Here are two tables of HRIS – Human Resource Information System – concepts and queries. The first explains what they are, the second gives Excel, DAX and Oracle SQL Queries for them. In the second table, the Excel column has an ‘ before each formula so that it displays correctly.
| Category | Subcategory | Concept | Description | Shortcode equation | Use Case | Keywords |
|---|---|---|---|---|---|---|
| Foundation | System Definition | HRIS | Human Resource Information System – integrated software solution for managing HR data, processes, and compliance across the employee lifecycle | Centralized platform for tracking employee records, payroll, benefits, time & attendance, and reporting | HR technology, data management, employee database, system integration | |
| Core HR | Employee Data | Employee Master Data | Complete employee record including personal information, job details, compensation, and organizational relationships | Foundation for all HR analytics and reporting; ensures data accuracy across systems | demographics, personal data, org structure, employee ID | |
| Core HR | Employee Data | Job Data | Information about positions including title, department, cost center, reports-to relationships, and job codes | Organizational planning, headcount analysis, workforce structure reporting | job title, department, position, org hierarchy | |
| Core HR | Employee Data | Employment Status | Classification of employee work arrangement (full-time, part-time, contractor, temporary) | Workforce composition analysis, compliance reporting, benefits eligibility | FTE, employment type, worker classification | |
| Workforce Metrics | Headcount | Active Headcount | Total number of active employees at a specific point in time | [COUNT of Active Employees] | Workforce size tracking, budget planning, trend analysis | employee count, workforce size, census |
| Workforce Metrics | Headcount | FTE (Full-Time Equivalent) | Standardized measure converting part-time and full-time employees to equivalent full-time positions | [Sum of (Hours Worked / Standard Full-Time Hours)] | Workforce capacity planning, labor cost analysis, productivity metrics | full-time equivalent, staffing levels, capacity |
| Workforce Metrics | Turnover | Turnover Rate | Percentage of employees who leave the organization during a period | [(Separations / Average Employees) * 100] | Retention analysis, talent management, workforce stability assessment | attrition, separations, retention, churn |
| Workforce Metrics | Turnover | Voluntary Turnover | Percentage of employees who choose to leave the organization | [(Voluntary Separations / Average Employees) * 100] | Identify retention issues, competitive positioning, employee satisfaction | resignations, quits, employee-initiated |
| Workforce Metrics | Turnover | Involuntary Turnover | Percentage of employees terminated by the organization | [(Involuntary Separations / Average Employees) * 100] | Performance management effectiveness, termination trends | terminations, layoffs, employer-initiated |
| Workforce Metrics | Hiring | Time to Fill | Average days between job requisition opening and offer acceptance | [AVG(Offer Accept Date – Requisition Open Date)] | Recruiting efficiency, hiring process optimization, talent acquisition performance | recruitment speed, hiring cycle, vacancy duration |
| Workforce Metrics | Hiring | Cost Per Hire | Average cost to fill a position including advertising, recruiter time, and onboarding | [Total Recruiting Costs / Number of Hires] | Recruiting budget management, sourcing strategy evaluation | recruitment costs, hiring expenses, talent acquisition ROI |
| Workforce Metrics | Diversity | Diversity Metrics | Representation percentages across protected categories (gender, race, ethnicity, age, disability) | [(Group Count / Total Count) * 100] | DEI initiatives, compliance reporting (EEO-1), pay equity analysis | demographics, representation, inclusion, EEO |
| Time & Attendance | Absence | Absenteeism Rate | Percentage of scheduled work time lost due to absences | [(Absent Days / Scheduled Work Days) * 100] | Workforce productivity, attendance policy effectiveness, staffing planning | absence, attendance, lost time |
| Time & Attendance | Hours | Overtime Hours | Hours worked beyond standard work schedule requiring premium pay | Labor cost management, scheduling optimization, compliance monitoring | OT, extra hours, premium pay | |
| Time & Attendance | Leave | Leave Balances | Accrued but unused paid time off (vacation, sick, personal) | [Opening Balance + Accruals – Usage] | Benefits liability tracking, staffing coverage planning, policy administration | PTO, vacation accrual, sick leave |
| Compensation | Pay | Base Salary | Fixed annual compensation for exempt employees | Compensation planning, pay equity analysis, budget forecasting | annual salary, base pay, fixed compensation | |
| Compensation | Pay | Hourly Rate | Pay per hour for non-exempt employees | Labor cost calculation, overtime computation, payroll processing | wage, hourly wage, non-exempt pay | |
| Compensation | Analysis | Compa-Ratio | Employee’s salary relative to midpoint of pay range | [(Actual Salary / Range Midpoint) * 100] | Pay positioning analysis, compensation equity, merit increase planning | compensation ratio, pay level, range penetration |
| Compensation | Analysis | Pay Equity | Analysis ensuring equal pay for equal work across protected groups | [Compare avg pay by role/level across demographics] | Legal compliance, fairness assessment, DEI initiatives | equal pay, wage gap, compensation fairness |
| Benefits | Enrollment | Benefits Participation Rate | Percentage of eligible employees enrolled in benefit plans | [(Enrolled Employees / Eligible Employees) * 100] | Benefits program effectiveness, cost forecasting, vendor negotiations | enrollment rate, plan participation, benefits uptake |
| Benefits | Cost | Benefits Cost Per Employee | Average annual cost of benefits per employee | [Total Benefits Costs / Total Employees] | Benefits budgeting, total rewards strategy, cost management | per capita benefits, employee benefits expense |
| Performance | Reviews | Performance Rating Distribution | Distribution of employees across performance rating categories | [(Count per Rating / Total Rated) * 100] | Forced ranking compliance, rating calibration, talent identification | performance distribution, rating curve, evaluation scores |
| Performance | Management | High Performer Retention | Retention rate specifically for top-rated employees | [(Retained High Performers / Total High Performers) * 100] | Talent retention strategies, succession risk, engagement programs | top talent retention, key employee turnover |
| Talent Management | Succession | Succession Coverage | Percentage of critical roles with identified successors | [(Roles with Successors / Critical Roles) * 100] | Succession planning, talent development, organizational risk | bench strength, pipeline, readiness |
| Talent Management | Development | Internal Fill Rate | Percentage of open positions filled by internal candidates | [(Internal Hires / Total Hires) * 100] | Career development effectiveness, talent mobility, promotion trends | internal mobility, promotions, career progression |
| Compliance | Reporting | EEO-1 Reporting | Required annual demographic workforce data submission to EEOC | Federal compliance, diversity tracking, legal requirements | equal employment, EEOC, federal reporting | |
| Compliance | Tracking | I-9 Compliance | Verification of employment eligibility for all US employees | Immigration compliance, audit readiness, legal requirement | work authorization, employment verification | |
| Compliance | Management | FLSA Classification | Proper classification of employees as exempt or non-exempt | Overtime compliance, wage/hour law adherence, risk mitigation | exempt status, non-exempt, wage hour | |
| Analytics | Reporting | HR Dashboard | Visual display of key HR metrics and KPIs | Executive reporting, trend monitoring, data-driven decision making | KPI tracking, metrics visualization, scorecard | |
| Analytics | Forecasting | Workforce Planning | Projection of future staffing needs based on business plans and trends | Strategic planning, budget development, talent acquisition strategy | headcount planning, staffing forecast, demand planning | |
| Analytics | Analysis | Cohort Analysis | Tracking groups of employees hired or promoted in same period over time | Retention patterns, onboarding effectiveness, hiring quality assessment | group tracking, tenure analysis, hire class |
| Category | Subcategory | HRIS Query | Description | Use Case | Excel Formula | DAX Query | Oracle SQL Query | Keywords |
|---|---|---|---|---|---|---|---|---|
| Headcount | Active Count | Current Active Headcount | Count of all active employees as of current date | Workforce planning, budget forecasting, executive dashboards | =COUNTIF(Status,”Active”) | Active HC = CALCULATE(COUNTROWS(Employees), Employees[Status]=”Active”) | SELECT COUNT(*) FROM employees WHERE status = ‘Active’ | active employees, workforce size, current headcount, employee census |
| Headcount | FTE Calculation | Calculate Total FTE | Sum of FTE values for all active employees | Labor capacity planning, productivity analysis, budgeting | =SUMIF(Status,”Active”,FTE_Column) | Total FTE = CALCULATE(SUM(Employees[FTE]), Employees[Status]=”Active”) | SELECT SUM(fte) FROM employees WHERE status = ‘Active’ | full-time equivalent, capacity, FTE total, workforce capacity |
| Headcount | Trend Analysis | Headcount by Month | Monthly snapshot of active employee count over time | Identify growth or reduction trends, seasonality, business cycle impact | =COUNTIFS(Status,”Active”,Hire_Date,”<="&@End_of_Month,Term_Date,">“&@End_of_Month) | HC by Month = CALCULATE(COUNTROWS(Employees), Employees[Status]=”Active”, DATESBETWEEN(Calendar[Date], MIN(Calendar[Date]), MAX(Calendar[Date]))) | SELECT TRUNC(hire_date, ‘MM’) AS month, COUNT(*) FROM employees WHERE status = ‘Active’ GROUP BY TRUNC(hire_date, ‘MM’) ORDER BY month | historical headcount, trend analysis, monthly census, workforce trends |
| Headcount | Department Analysis | Headcount by Department | Active headcount grouped by department or division | Department comparisons, resource allocation, organizational analysis | =COUNTIFS(Status,”Active”,Department,@Dept_Name) | HC by Dept = CALCULATE(COUNTROWS(Employees), Employees[Status]=”Active”, ALLEXCEPT(Employees, Employees[Department])) | SELECT department, COUNT(*) AS headcount FROM employees WHERE status = ‘Active’ GROUP BY department ORDER BY headcount DESC | departmental headcount, organizational breakdown, division size |
| Headcount | Status Breakdown | Employee Status Distribution | Count of employees by employment status (FT, PT, Contractor) | Workforce composition reporting, contractor tracking, benefits eligibility | =COUNTIF(Employment_Status,@Status_Type) | Status Count = CALCULATE(COUNTROWS(Employees), ALLEXCEPT(Employees, Employees[Employment_Status])) | SELECT employment_status, COUNT(*) AS count FROM employees GROUP BY employment_status | employment type, worker classification, full-time, part-time, contractor |
| Turnover | Rate Calculation | Annual Turnover Rate | Percentage of workforce that separated in past 12 months | Benchmark retention, identify talent management issues, board reporting | =(COUNTIFS(Term_Date,”>=”&@Start_Date,Term_Date,”<="&@End_Date)/AVERAGE(Headcount))*100 | Turnover % = DIVIDE(CALCULATE(COUNTROWS(Terminations), DATESINPERIOD(Calendar[Date], MAX(Calendar[Date]), -12, MONTH)), CALCULATE(AVERAGE(Headcount[HC]), DATESINPERIOD(Calendar[Date], MAX(Calendar[Date]), -12, MONTH)))*100 | SELECT (COUNT(*)::FLOAT / (SELECT AVG(hc) FROM monthly_headcount WHERE month >= ADD_MONTHS(SYSDATE, -12))) * 100 AS turnover_rate FROM terminations WHERE term_date >= ADD_MONTHS(SYSDATE, -12) | attrition rate, separation rate, retention, turnover percentage |
| Turnover | Voluntary Analysis | Voluntary vs Involuntary Separations | Breakdown of separations by voluntary and involuntary categories | Understand turnover drivers, retention strategy development | =COUNTIFS(Term_Date,”>=”&@Start,Term_Date,”<="&@End,Term_Type,"Voluntary") | Vol Terms = CALCULATE(COUNTROWS(Terminations), Terminations[Type]=”Voluntary”) | SELECT termination_type, COUNT(*) AS count FROM terminations WHERE term_date BETWEEN :start_date AND :end_date GROUP BY termination_type | voluntary turnover, involuntary turnover, resignation, termination breakdown |
| Turnover | New Hire Retention | New Hire 90-Day Retention | Percentage of new hires still employed after 90 days | Evaluate hiring quality, onboarding effectiveness, recruiter performance | =(COUNTIFS(Hire_Date,”>=”&@Date-90,Status,”Active”)/COUNTIF(Hire_Date,”>=”&@Date-90))*100 | 90Day Retention = DIVIDE(CALCULATE(COUNTROWS(Employees), Employees[Status]=”Active”, Employees[Hire_Date] >= TODAY()-90), CALCULATE(COUNTROWS(Employees), Employees[Hire_Date] >= TODAY()-90))*100 | SELECT (COUNT(CASE WHEN status = ‘Active’ THEN 1 END)::FLOAT / COUNT(*)) * 100 AS retention_90day FROM employees WHERE hire_date >= SYSDATE – 90 | new hire retention, onboarding success, 90-day retention, hiring quality |
| Turnover | Termination Trends | Terminations by Reason | Count and percentage of terminations grouped by reason code | Exit interview analysis, identify systemic issues, HR intervention planning | =COUNTIFS(Term_Date,”>=”&@Start_Date,Term_Date,”<="&@End_Date,Term_Reason,@Reason_Code) | Terms by Reason = CALCULATE(COUNTROWS(Terminations), ALLEXCEPT(Terminations, Terminations[Reason])) | SELECT termination_reason, COUNT(*) AS count, ROUND((COUNT(*)::FLOAT / (SELECT COUNT(*) FROM terminations WHERE term_date BETWEEN :start AND :end)) * 100, 2) AS pct FROM terminations WHERE term_date BETWEEN :start AND :end GROUP BY termination_reason ORDER BY count DESC | termination reasons, exit analysis, separation drivers, turnover root cause |
| Recruiting | Pipeline Metrics | Active Requisitions by Status | Count of open job requisitions by current workflow status | Recruiting workload management, hiring pipeline visibility | =COUNTIFS(Req_Status,@Status_Value) | Req Count = CALCULATE(COUNTROWS(Requisitions), ALLEXCEPT(Requisitions, Requisitions[Status])) | SELECT req_status, COUNT(*) AS count FROM requisitions WHERE req_status IN (‘Open’, ‘Screening’, ‘Interview’) GROUP BY req_status | requisition pipeline, open roles, hiring status, recruitment workload |
| Recruiting | Time to Fill | Average Time to Fill | Mean number of days from requisition open to offer accepted | Recruiting efficiency assessment, process improvement, SLA tracking | =AVERAGE(@Offer_Accept_Date-@Req_Open_Date) | Avg Time to Fill = AVERAGE(Requisitions[Days_to_Fill]) | SELECT AVG(offer_accept_date – req_open_date) AS avg_days_to_fill FROM requisitions WHERE offer_accept_date IS NOT NULL | recruiting efficiency, hiring speed, days to fill, recruitment cycle time |
| Recruiting | Source Effectiveness | Hires by Recruitment Source | Count of hires attributed to each recruitment source | Recruiting ROI analysis, optimize sourcing spend, vendor evaluation | =COUNTIFS(Hire_Date,”>=”&@Start_Date,Hire_Date,”<="&@End_Date,Source,@Source_Name) | Hires by Source = CALCULATE(COUNTROWS(Hires), ALLEXCEPT(Hires, Hires[Source])) | SELECT recruitment_source, COUNT(*) AS hires FROM employees WHERE hire_date BETWEEN :start_date AND :end_date GROUP BY recruitment_source ORDER BY hires DESC | source of hire, recruiting channels, sourcing effectiveness, recruitment ROI |
| Recruiting | Hiring Volume | Monthly Hiring Trends | Number of new hires by month for trend analysis | Capacity planning, seasonal hiring patterns, budget vs actual analysis | =COUNTIFS(Hire_Date,”>=”&EOMONTH(Start,-1)+1,Hire_Date,”<="&EOMONTH(Start,0)) | Monthly Hires = CALCULATE(COUNTROWS(Hires), DATESMTD(Calendar[Date])) | SELECT TRUNC(hire_date, ‘MM’) AS month, COUNT(*) AS new_hires FROM employees GROUP BY TRUNC(hire_date, ‘MM’) ORDER BY month DESC | hiring trends, new hire volume, recruitment activity, hiring patterns |
| Compensation | Pay Analysis | Average Salary by Job Title | Mean base salary calculated for each job title | Compensation benchmarking, pay equity analysis, market competitiveness | =AVERAGEIF(Job_Title,@Title,Base_Salary) | Avg Salary = CALCULATE(AVERAGE(Employees[Base_Salary]), ALLEXCEPT(Employees, Employees[Job_Title])) | SELECT job_title, ROUND(AVG(base_salary), 2) AS avg_salary FROM employees WHERE status = ‘Active’ GROUP BY job_title ORDER BY avg_salary DESC | compensation analysis, pay by role, salary benchmarking, title-based pay |
| Compensation | Equity Analysis | Compa-Ratio by Employee | Individual employee salary divided by job range midpoint | Individual pay positioning, merit increase planning, equity reviews | =(@Base_Salary/@Range_Midpoint)*100 | Compa Ratio = DIVIDE(Employees[Base_Salary], RELATED(Jobs[Range_Midpoint]))*100 | SELECT employee_id, employee_name, base_salary, range_midpoint, ROUND((base_salary / range_midpoint) * 100, 2) AS compa_ratio FROM employees e JOIN job_ranges j ON e.job_code = j.job_code WHERE e.status = ‘Active’ | pay positioning, salary ratio, compensation equity, range penetration |
| Compensation | Range Positioning | Employees Below Range Minimum | List of employees whose salary is below the minimum of their range | Compensation compliance, pay adjustment planning, equity corrections | =@IF(@Base_Salary<@Range_Min,Employee_Name,"") | Below Min = CALCULATE(COUNTROWS(Employees), Employees[Base_Salary] < RELATED(Jobs[Range_Min])) | SELECT employee_id, employee_name, base_salary, range_min FROM employees e JOIN job_ranges j ON e.job_code = j.job_code WHERE e.status = ‘Active’ AND base_salary < range_min | below minimum, underpaid employees, pay compliance, compensation adjustments |
| Compensation | Merit Planning | Merit Increase Eligibility | Employees eligible for merit increase based on hire date and last increase | Merit budget allocation, identify eligible employees, cycle planning | =IF(AND(@DATEDIFF(TODAY(),Hire_Date)>=365,@DATEDIFF(TODAY(),Last_Increase)>=365),”Eligible”,””) | Merit Eligible = CALCULATE(COUNTROWS(Employees), DATEDIFF(Employees[Hire_Date], TODAY(), DAY) >= 365 && DATEDIFF(Employees[Last_Increase], TODAY(), DAY) >= 365) | SELECT employee_id, employee_name, hire_date, last_increase_date FROM employees WHERE status = ‘Active’ AND MONTHS_BETWEEN(SYSDATE, hire_date) >= 12 AND (last_increase_date IS NULL OR MONTHS_BETWEEN(SYSDATE, last_increase_date) >= 12) | merit eligibility, raise eligibility, increase cycle, annual review |
| Compensation | Increase Analysis | Year-over-Year Salary Growth | Percentage change in average salary compared to prior year | Compensation trends, inflation adjustment, market movement tracking | =((@AVG(Current_Year_Salary)-@AVG(Prior_Year_Salary))/@AVG(Prior_Year_Salary))*100 | Salary Growth % = DIVIDE([Avg Salary Current] – [Avg Salary Prior], [Avg Salary Prior])*100 | WITH current_avg AS (SELECT AVG(base_salary) AS curr FROM employees WHERE status = ‘Active’), prior_avg AS (SELECT AVG(base_salary) AS prior FROM employees_history WHERE snapshot_date = ADD_MONTHS(TRUNC(SYSDATE, ‘YY’), -12)) SELECT ROUND(((curr – prior) / prior) * 100, 2) AS yoy_growth FROM current_avg, prior_avg | salary trends, compensation growth, inflation adjustment, pay progression |
| Time & Attendance | Overtime Tracking | Overtime Hours by Department | Sum of overtime hours worked by department in a period | Labor cost management, scheduling optimization, budget variance | =SUMIFS(OT_Hours,Department,@Dept_Name,Date,”>=”&@Start_Date,Date,”<="&@End_Date) | OT Hours = CALCULATE(SUM(TimeData[OT_Hours]), ALLEXCEPT(TimeData, Employees[Department])) | SELECT department, SUM(overtime_hours) AS total_ot FROM time_data WHERE time_date BETWEEN :start_date AND :end_date GROUP BY department ORDER BY total_ot DESC | OT analysis, overtime costs, extra hours, premium pay tracking |
| Time & Attendance | Absence Reporting | Absence Rate by Employee | Percentage of scheduled time absent for each employee | Attendance policy enforcement, FMLA tracking, productivity impact | =(SUM(Absent_Days)/SUM(Scheduled_Days))*100 | Absence Rate = DIVIDE(SUM(Attendance[Absent_Days]), SUM(Attendance[Scheduled_Days]))*100 | SELECT employee_id, employee_name, ROUND((SUM(absent_days)::FLOAT / SUM(scheduled_days)) * 100, 2) AS absence_rate FROM attendance WHERE attendance_date BETWEEN :start_date AND :end_date GROUP BY employee_id, employee_name HAVING SUM(scheduled_days) > 0 ORDER BY absence_rate DESC | absenteeism, attendance tracking, absence frequency, lost time |
| Time & Attendance | PTO Analysis | PTO Balance Report | Current balance of PTO hours/days by employee | Benefits liability tracking, vacation planning, policy administration | =SUMIFS(Accrued,Employee_ID,@ID)-SUMIFS(Used,Employee_ID,@ID) | PTO Balance = CALCULATE(SUM(Leave[Accrued]) – SUM(Leave[Used]), ALLEXCEPT(Leave, Employees[Employee_ID])) | SELECT employee_id, employee_name, leave_type, accrued_balance – used_balance AS current_balance FROM leave_balances WHERE as_of_date = TRUNC(SYSDATE) | vacation balance, PTO accrual, time off liability, leave tracking |
| Benefits | Enrollment Rates | Benefits Election Rate | Percentage of eligible employees enrolled in each benefit plan | Benefits program effectiveness, open enrollment planning, cost forecasting | =(COUNTIFS(Plan_Name,@Plan,Enrolled,”Y”)/COUNTIFS(Plan_Name,@Plan,Eligible,”Y”))*100 | Enrollment % = DIVIDE(CALCULATE(COUNTROWS(Benefits), Benefits[Enrolled]=”Y”), CALCULATE(COUNTROWS(Benefits), Benefits[Eligible]=”Y”))*100 | SELECT plan_name, ROUND((COUNT(CASE WHEN enrolled = ‘Y’ THEN 1 END)::FLOAT / COUNT(CASE WHEN eligible = ‘Y’ THEN 1 END)) * 100, 2) AS enrollment_rate FROM benefits_enrollment GROUP BY plan_name | benefit participation, enrollment effectiveness, plan adoption, benefits uptake |
| Benefits | Cost Analysis | Total Benefits Cost per Employee | Total cost of benefits divided by number of employees | Total rewards analysis, benefits budget planning, per capita costing | =SUMIF(Employee_ID,@ID,Benefits_Cost)/COUNT(Employee_ID) | Benefits per EE = DIVIDE(SUM(Benefits[Cost]), COUNTROWS(Employees)) | SELECT ROUND(SUM(annual_benefit_cost) / COUNT(DISTINCT employee_id), 2) AS cost_per_employee FROM benefits_costs WHERE year = EXTRACT(YEAR FROM SYSDATE) | benefits expense, per capita cost, benefit spend, total rewards cost |
| Performance | Rating Distribution | Performance Rating Distribution | Count and percentage of employees in each performance rating category | Calibration sessions, forced ranking compliance, talent identification | =(COUNTIF(Rating,@Rating_Value)/COUNTA(Rating))*100 | Rating Distribution = DIVIDE(COUNTROWS(Performance), CALCULATE(COUNTROWS(Performance), ALLEXCEPT(Performance, Performance[Rating])))*100 | SELECT performance_rating, COUNT(*) AS count, ROUND((COUNT(*)::FLOAT / (SELECT COUNT(*) FROM performance WHERE review_period = :period)) * 100, 2) AS pct FROM performance WHERE review_period = :period GROUP BY performance_rating ORDER BY performance_rating DESC | rating curve, performance distribution, evaluation breakdown, rating spread |
| Performance | High Performer ID | Top Performers List | Employees with highest performance ratings (e.g., 4 or 5 rating) | Succession planning, retention risk, development program targeting | =@IF(@Rating>=4,Employee_Name,””) | Top Performers = CALCULATE(COUNTROWS(Performance), Performance[Rating] >= 4) | SELECT employee_id, employee_name, performance_rating FROM performance WHERE review_period = :period AND performance_rating >= 4 ORDER BY performance_rating DESC | high performers, top talent, succession candidates, key employees |
| Performance | Review Completion | Performance Review Completion Rate | Percentage of employees with completed review vs total eligible | Performance cycle management, manager accountability, HR compliance | =(COUNTIF(Review_Status,”Complete”)/COUNTIF(Review_Status,”<>“))*100 | Review Complete % = DIVIDE(CALCULATE(COUNTROWS(Performance), Performance[Status]=”Complete”), COUNTROWS(Performance))*100 | SELECT ROUND((COUNT(CASE WHEN review_status = ‘Complete’ THEN 1 END)::FLOAT / COUNT(*)) * 100, 2) AS completion_rate FROM performance WHERE review_period = :period | review compliance, evaluation completion, performance cycle tracking |
| Diversity | Representation | Gender Representation by Level | Count and percentage of male/female employees at each job level | DEI reporting, pay equity analysis, representation goals tracking | =COUNTIFS(Gender,@Gender_Value,Job_Level,@Level) | Gender by Level = CALCULATE(COUNTROWS(Employees), ALLEXCEPT(Employees, Employees[Gender], Employees[Job_Level])) | SELECT job_level, gender, COUNT(*) AS count FROM employees WHERE status = ‘Active’ GROUP BY job_level, gender ORDER BY job_level, gender | diversity metrics, gender representation, workforce demographics, EEO reporting |
| Diversity | Pay Gap Analysis | Pay Equity Analysis by Gender | Comparison of average salary for male vs female in same roles | Legal compliance, pay equity remediation, compensation fairness | =AVERAGEIFS(Salary,Job_Title,@Title,Gender,”F”)-AVERAGEIFS(Salary,Job_Title,@Title,Gender,”M”) | Pay Gap = CALCULATE(AVERAGE(Employees[Salary]), Employees[Gender]=”F”) – CALCULATE(AVERAGE(Employees[Salary]), Employees[Gender]=”M”) | SELECT job_title, ROUND(AVG(CASE WHEN gender = ‘F’ THEN base_salary END), 2) AS female_avg, ROUND(AVG(CASE WHEN gender = ‘M’ THEN base_salary END), 2) AS male_avg, ROUND(AVG(CASE WHEN gender = ‘F’ THEN base_salary END) – AVG(CASE WHEN gender = ‘M’ THEN base_salary END), 2) AS pay_gap FROM employees WHERE status = ‘Active’ GROUP BY job_title HAVING COUNT(CASE WHEN gender = ‘F’ THEN 1 END) > 0 AND COUNT(CASE WHEN gender = ‘M’ THEN 1 END) > 0 | compensation equity, gender pay gap, equal pay analysis, fairness assessment |
| Diversity | Hiring Diversity | Diversity of Recent Hires | Demographic breakdown of employees hired in recent period | DEI hiring goals, recruitment effectiveness, affirmative action | =COUNTIFS(Hire_Date,”>=”&@Start,Hire_Date,”<="&@End,Demographic,@Demo_Value) | Diverse Hires = CALCULATE(COUNTROWS(Hires), Hires[Hire_Date] >= [Period_Start], ALLEXCEPT(Hires, Hires[Demographic])) | SELECT ethnicity, COUNT(*) AS hires, ROUND((COUNT(*)::FLOAT / (SELECT COUNT(*) FROM employees WHERE hire_date BETWEEN :start AND :end)) * 100, 2) AS pct FROM employees WHERE hire_date BETWEEN :start AND :end GROUP BY ethnicity | hiring diversity, recruitment demographics, diverse candidates, affirmative action |
| Tenure | Service Analysis | Average Tenure by Department | Mean length of service calculated by department | Retention analysis, knowledge retention risk, succession planning | =AVERAGE(TODAY()-@Hire_Date)/365.25 | Avg Tenure = AVERAGE(DATEDIFF(Employees[Hire_Date], TODAY(), DAY))/365.25 | SELECT department, ROUND(AVG(MONTHS_BETWEEN(SYSDATE, hire_date)) / 12, 2) AS avg_tenure_years FROM employees WHERE status = ‘Active’ GROUP BY department ORDER BY avg_tenure_years DESC | length of service, employee tenure, average service years, experience level |
| Tenure | Cohort Tracking | Tenure Bands Distribution | Count of employees grouped by tenure ranges (0-1, 1-3, 3-5, 5-10, 10+ years) | Workforce demographics, retention patterns, career development | =COUNTIFS(Tenure_Years,”>=”&@Lower_Bound,Tenure_Years,”<"&@Upper_Bound) | Tenure Bands = CALCULATE(COUNTROWS(Employees), ALLEXCEPT(Employees, Employees[Tenure_Band])) | SELECT CASE WHEN tenure_years < 1 THEN '0-1 years' WHEN tenure_years < 3 THEN '1-3 years' WHEN tenure_years < 5 THEN '3-5 years' WHEN tenure_years < 10 THEN '5-10 years' ELSE '10+ years' END AS tenure_band, COUNT(*) AS count FROM (SELECT FLOOR(MONTHS_BETWEEN(SYSDATE, hire_date) / 12) AS tenure_years FROM employees WHERE status = 'Active') GROUP BY CASE WHEN tenure_years < 1 THEN '0-1 years' WHEN tenure_years < 3 THEN '1-3 years' WHEN tenure_years < 5 THEN '3-5 years' WHEN tenure_years < 10 THEN '5-10 years' ELSE '10+ years' END ORDER BY MIN(tenure_years) | tenure distribution, service bands, experience levels, workforce maturity |
| Tenure | Retention by Tenure | Retention Rate by Years of Service | Percentage of employees retained across different tenure cohorts | Flight risk analysis, engagement correlation, targeted retention | =(COUNTIFS(Tenure_Range,@Range,Status,”Active”)/COUNTIFS(Hire_Date,”>=”&@Cohort_Start))*100 | Retention by Tenure = DIVIDE(CALCULATE(COUNTROWS(Employees), Employees[Status]=”Active”), CALCULATE(COUNTROWS(Employees), ALL(Employees[Status])))*100 | WITH tenure_cohorts AS (SELECT CASE WHEN MONTHS_BETWEEN(SYSDATE, hire_date)/12 < 1 THEN '<1yr' WHEN MONTHS_BETWEEN(SYSDATE, hire_date)/12 < 3 THEN '1-3yr' ELSE '3+yr' END AS cohort, status FROM employees) SELECT cohort, ROUND((COUNT(CASE WHEN status = 'Active' THEN 1 END)::FLOAT / COUNT(*)) * 100, 2) AS retention_rate FROM tenure_cohorts GROUP BY cohort | tenure-based retention, service-based attrition, longevity analysis |
| Organizational | Span of Control | Manager Span of Control | Number of direct reports per manager | Organizational design, manager workload, promotion readiness | =COUNTIF(Manager_ID,@Manager) | Span of Control = CALCULATE(COUNTROWS(Employees), ALLEXCEPT(Employees, Employees[Manager_ID])) | SELECT manager_id, manager_name, COUNT(*) AS direct_reports FROM employees WHERE status = ‘Active’ AND manager_id IS NOT NULL GROUP BY manager_id, manager_name ORDER BY direct_reports DESC | manager workload, direct reports, supervisory ratio, organizational design |
| Organizational | Reporting Structure | Organizational Hierarchy Report | Tree structure showing reporting relationships from CEO down | Organizational planning, restructuring analysis, role clarity | Org Hierarchy = PATH(Employees[Employee_ID], Employees[Manager_ID]) | SELECT LEVEL, SYS_CONNECT_BY_PATH(employee_name, ‘ > ‘) AS hierarchy FROM employees START WITH manager_id IS NULL CONNECT BY PRIOR employee_id = manager_id ORDER BY LEVEL, employee_name | reporting structure, organizational tree, management hierarchy, org chart | |
| Organizational | Open Position Tracking | Open Positions by Department | Count of unfilled positions by department with days open | Hiring priority setting, time-to-fill tracking, capacity planning | =COUNTIFS(Position_Status,”Open”,Department,@Dept_Name) | Open Positions = CALCULATE(COUNTROWS(Positions), Positions[Status]=”Open”, ALLEXCEPT(Positions, Positions[Department])) | SELECT department, COUNT(*) AS open_positions, ROUND(AVG(SYSDATE – req_open_date), 0) AS avg_days_open FROM requisitions WHERE req_status = ‘Open’ GROUP BY department ORDER BY open_positions DESC | vacancy tracking, unfilled positions, open reqs, hiring needs |