Headcount Trend Report Times Out for Executives
An analyst maintains a matrix report, Headcount by Supervisory Organization and Month-End, used by the executive team. It uses three calculated fields: one extracts the worker's current supervisory organization from the primary job, one extracts the primary job's location, and a true/false calculated field checks whether the worker's hire date falls on or before the last day of the reporting month and the termination date is null or later than that day - comparing both against a hard-coded reference date. Run for a single month and a single region it returns in about 40 seconds. Run for 36 months across all regions it exceeds the 30-minute limit and fails. The analyst proposes converting the true/false field into a text calculated field and adding four more sub-filters on location and job family.
1. What is driving the runtime, and what would you change first?
Read the worked answer
Two things are being multiplied: instances and months.
- Instance fan-out. Extracting the supervisory organization and location from the primary job means the report evaluates per job instance per worker, not per worker. Workers with multiple jobs or historical job rows multiply the work, and the aggregation at the end has to de-duplicate.
- Wrong evaluation point. Both extraction fields pull current values. On a 36-month trend, 'current' is evaluated per row anyway, so the report pays the cost of an instance walk for values that are then thrown away for all but the last month.
- Nothing is being pushed down. The true/false field is referenced in the filter, so it is computed for every worker before anything is excluded. The report never gets smaller as it runs.
Change first, in this order:
- Move the filter to the data source, on indexed business-object fields (region, supervisory org), so the worker population is cut before any calculated field is evaluated. This is usually the single largest win.
- Reconsider the data source. A trend of headcount by month is a delivered pattern; check whether an existing trend-capable source gives you the as-of-date behaviour without custom extraction fields.
- Narrow the run with a prompt on region and a bounded date range, so the executive view is not silently requesting every region for three years.
- Schedule it to a file if the audience only needs it refreshed, not interactive.
2. Assess the analyst's proposal to convert the true/false field to text and add four more sub-filters.
Read the worked answer
Both changes make it worse, not better.
- True/false to text raises the number of distinct values per worker and prevents the field behaving as a cheap binary filter. A boolean field is about as compressible as a field gets; a text field that encodes the same logic costs more to evaluate and more to group. If the motivation is 'we want it to display better,' that is a formatting concern, not a data-type concern.
- Four more sub-filters applied per instance add evaluation work to every row that survives. Sub-filters are evaluated against the instance being examined, so they do not reduce the instance count - they filter after the walk. Only filters on indexed fields at the data source reduce the work.
The instinct behind both changes is 'narrow the result,' which is right, but the levers chosen are at the wrong layer. Narrow at the data source and at the prompt, not in the calculated fields.
What to tell the analyst: the report is slow because it is doing per-instance work over a large population for a long date range, and no amount of reformatting the output changes that.
3. Setting performance aside, what is wrong with the numbers this report produces across 36 months?
Read the worked answer
The hard-coded reference date makes every historical month wrong.
The true/false field compares hire and termination dates against a fixed date. When the report is run for January, February, and March of prior years, the field returns the same answer for all of them - essentially 'is this person employed as of the hard-coded date.' The trend line the executives are reading is flat by construction.
Fix: tie the evaluation to the report's own as-of date rather than to a literal. Set the calculated field to evaluate as of the report's effective date, and confirm the report is actually running with the effective date you think it is - a trend report that evaluates 'as of today' produces the same flat-line error with more steps.
Second correctness problem: double counting. If the report extracts from job instances, a worker with two jobs appears twice and the headcount total inflates. The aggregation must be an unduplicated count on worker, not a row count. Verify by running the report for a single month against a known headcount and reconciling the difference.
Third: terminated workers with future-dated terminations. Confirm the field's logic matches the business definition of headcount at month end, and get that definition in writing from the consumer before you rebuild it.