How to Effective Date a Headcount Report?

Question

I have a headcount report in OTBI HCM, that is running from the Worker Assignment Real Time subject area.  When it runs, its always latest and greatest.  How do I effective date this report to show me the staff I had, with their details, on 1/1/2022 for example (2 years back).

Thanks,

Luke

Martin Lowe · Answer

Hi Patrick - the link to the "Ye olde historical reporting explained" PDF doesn't work. Do you have another source so I can see how to set up the Presentation Variable?

Patrick_Midd · Answer

For accomplishing this from Worker Assignment Real Time, you'll want to check out ye olde Historical Reporting Explained documentation found here.

The gist is that Worker Assignment Real Time doesn't have the Time Dimension and as such defaults to reporting as of current date. Page 4-5 of the linked documentation provides a work around for this, overriding the default reporting date (current date) by supplying an effective date parameter in the LSQL prefix. In a nutshell:

* Edit your analysis and go to the Advanced tab.
* Scroll to Advanced SQL Clauses section and paste the following in the Prefix box:* SET VARIABLE PARAM_EFFECTIVE_DATE='2022-06-30';

* Click Apply SQL.
* Save the analysis and navigate back to Results tab to review results.

This should override the default effective date back to June 30, 2022.  You can swap the date out with a different value as needed.

If you want this to be a little more dynamic and user friendly, you can instead set the Prefix up to receive a Presentation Variable like so:

SET VARIABLE PARAM_EFFECTIVE_DATE ='@{AsOfDate}[YYYY-MM-DD]{@{system.currentTime}[YYYY-MM-DD]}';

Pages 8-12 of the linked documentation cover how to setup the prompt for the Presentation Variable and leverage this for a basic effective-dated headcount report by department.

The syntax I shared differs a little from what is in the documentation in that it also sets the current date as a default value for AsOfDate if none is provided in the Prompt, cribbed from a comment made last week by someone who knows a lot more about this than I do. Having a fallback value here is useful for development as it stops the analysis from throwing an error in edit mode.

All of that being said: after you get this tied into your current analysis, I highly recommend checking out subject area Workforce Management - Workforce Trend Real Time. This does include the Time Dimension, and has a number of useful measure columns (eg. "Period Start Active Headcount" and "Period End Active Headcount") that can be tied back to reporting headcount and FTE overtime by a wide array of date intervals.