Creating Month Start and End Dates for Headcount Follow-Up in Story Reports

[bellakong]

Reporting headcount at each month end or tracking employees who are active during a specific month has consistently ranked among the most demanding requests for HR professionals utilizing SuccessFactors Story Reports. Addressing these challenges often involves navigating the inherent limitations within the Story functionality. This blog aims to be a guiding light, offering solutions to overcome obstacles and empower HR fellows in their reporting endeavors within SuccessFactors.

 

In the evolving landscape of HR analytics, this post serves as the inaugural step, focusing on the creation of month start and end dates—a pivotal aspect for generating insightful headcount follow-up reports. As we tackle this fundamental requirement, we pave the way for future discussions on period reports, including topics such as tracking new hires, leavers, and measuring intricate balances. Let's embark on this journey together, conquering the reporting obstacles that come with SuccessFactors Story Reports.

Before we dive into the details, ensure the following prerequisites are in place:

Access to SAP SuccessFactors with the necessary permissions for Story Reporting.

Familiarity with the data model relevant to headcount reporting. For example Employee Central and how employment details (different dates) have been configured in your system, and which are used for the count.

Definitions of who should be included during what period.

In this blog, I will use 2 examples. One is for a monthly following up headcount rolling back to 13 month, and the other one is for count new hires or active employees by month within a full year.

Screen shots were taken using Classic Design Experience in story report.

First case: Rolling back based on current date

Creating Month Start

SuccessFactors has no calendar date which can be used for reporting employee job information (excluding time sheet). Therefor, before reporting any headcount based on period, you have to create a month start date and a month end date (24 calculated columns in query) in order to built a timeline.

Open the Story Reporting page and select the relevant data source and chose needed columns for the report. In the example, it is Job information, time filter is As of Today because this report is reflecting up to date job information for all the employees.

Create calculated columns in the Column Overviews

 

Define current month first day (current month first day -1 will be the end date for previous month)

TODATE(CONCAT( CONCAT( CONCAT( TOTEXT( 

MONTH(CURRENTDATE())),"/"),  TOTEXT ( YEAR(CURRENTDATE()))),

"/01"), "mm/yyyy/dd")

You are able to set the calculated columns as hidden (Off) or visible in story (On), including in Preview) once it has been created:

Create another calculated column as previous month 1st date. All the first date of each month should be created first.

ADDMONTHTODATE( TODATE(CONCAT( CONCAT( CONCAT(

TOTEXT( MONTH(CURRENTDATE())),"/"),

TOTEXT ( YEAR(CURRENTDATE()))), "/01"), "mm/yyyy/dd"), -1)

In the same logic, create another 11 calculated columns until previous 12 month 1 start date.

ADDMONTHTODATE( TODATE(CONCAT( 

CONCAT( CONCAT( TOTEXT( MONTH(CURRENTDATE())),"/"),  

TOTEXT ( YEAR(CURRENTDATE()))), "/01"), "mm/yyyy/dd"), -12)

At this point, it is highly recommended to save query and save the story report with the data source you have created.

 

Creating Month End

Based on the month start date you have created, create a new calculated column as previous month end date

ADDDAYTODATE([CURRENTMONTH1DAY],-1)

 

Hit Preview in the query and test if they are performing as expected (Current date in screen shot = 12/03/2023😞

 

Same logic for the rest of the month end date until previous 13 month end date

ADDDAYTODATE([LAST12MONTH1DAY],-1)

Now the timeline has been created rolling back 13 month in your query. After saving the query, you can either pull out a table list or create a chart to validate if all the start date and end dates have been calculated correctly.

Example Usage in Story Reports:

To visualize and validate the calculated columns created earlier, follow these steps:

1. Insert a table list (List, non-aggregated) in the Grid page.


2. Open the dropdown list under 'Column', starting with Employee ID for consistency in checking established dates for further calculations.

The table list will pull the date based on the selection:

 

 

Second example: reporting new hire or active employees based on 12 months in 2023

Same step to create calculated columns in the query, however it is much simpler:

Jan 1st 2023

TODATE("01/01/2023", "mm/dd/yyyy")

Jan 31st 2023

TODATE("01/31/2023", "mm/dd/yyyy")

Same for the rest month start date and month end date.

Because these dates are fixed as in the formula, you may need to create a new story report for another year's reporting demand.

Best Practices:

Regularly save your work to prevent data loss.
Validate the accuracy of calculated columns by previewing the data source.

Be aware of some of important limitations in query for story report: 30 tables and 120 columns.

Next Steps:

Hope this blog will help you in the journey mastering the creation of month start and end dates for headcount follow-up reports. As you navigate the SuccessFactors reporting landscape, keep these best practices in mind to ensure the integrity and precision of your analyses.

Stay tuned for upcoming blogs in this series, where we'll delve into more specific period reports. The next installment will guide you through reporting headcount, new hires, and leavers based on the time structures you've built using the concepts discussed above. These insights will further empower you to harness the full potential of SuccessFactors Story Reports for nuanced and comprehensive workforce analytics.

Feel free to share your experiences and questions in the comments below. Your feedback is invaluable as we explore the intricacies of SuccessFactors reporting together.

[LewisCook]

Good article and useful, but would have liked to have seen a completed chart example as a target.

[bellakong]

Hi Lewis,

 

Your comments are appreciated. The preview of the calculated columns has been inserted in the blog, so it brings pieces of look and feel for added calculated columns.

[00022111734]

Hi Bella

 

Thanks for insightful blog very well explain

[vnotaro]

Hi Bella

Thanks you for sharing the above insides.

I have being able to replicate all the steps you mentioned and I have now all my calculated columns ready (Thank you)

However I could not find a way to display it properly using any chart or table, I have got confused where fields should be displayed.

It would be very nice if you could add some screenshots showing how you displayed your data.

[LewisCook]

Outstanding. Thank  you!

[bellakong]

Hello Vitor,

 

Glad to hear you are able to create these calculated columns following the steps.

As long as the columns have Display in the report turned on, you should be able to find them all in Dimension (these dates are dimensions but not measures) .

Example has been added in the blog so you can have a visual how they would be used in story. If you have noticed anything bizar, please share with me more details, what do you mean they cannot be properly displayed in table list.

At this point, the date dimensions cannot be used in charts, because every employee has the same dates for all these columns. Therefore the chart using these columns is showing as one bar, which is not helping any analysis.

I will working on the following blogs which contain how to count headcounts based on these dates, if that is what you are aiming at.

Hope it helps.

 

Bella

[vnotaro]

Thanks very much Bella.

I am still not clear on the table as I can not see the count column.

How does it work the count by each month ?

Can you please clarify ?

[SaeedAlQarni]

Hi Bella

Thanks for sharing this blog.

I'm looking for something similar to have the same tracking of HC based on position through EC Report Center or Integration Center.

[GCR]

Hi Bella

This is very useful, thank you.

Can you provide further details as to how I can create a chart showing the headcount each month (end of each month) for the last 12 months including headcount by business unit for each month?

Kind regards

Gemma

[bellakong]

Hello Gemma,

 

Great to hear from you. The design of the charts and analysis will be included in other blog which is coming. The work is still ongoing. Stay tuned.

Bella

[bellakong]

Hello,

 

You can have a look at one of my another blog. Hope it bring inspirations. https://blogs.sap.com/2023/12/18/a-deep-dive-into-tacking-new-hires-and-leavers-trends-how-to-build-...

If you are using position management in EC, the way you calculate count on position will be adjusted to include Position Status. It also depends on what date you are having on position. If you plan to use effective start date on position, just add a layer of IF() in the calculation:

IF([Position#positionstatus]="Inactive"

,0 ,

IF([Position#positionstartdate] >= [LASTMONTH1STDAY] AND

[Position#positionstartdate] <=[LASTMONTHENDDAY] , 1, 0 ))

[emilymartins]

Bella, 

I am so appreciative of you for this article and the one linked in your last comment. I've been trying to create time reporting in Report Stories and have been significantly limited in meeting my client's requirements by the instability and gaps of the product. I've asked SAP for help with this to no avail. This artcile and the one that followed helped me create a chart to indicate number of new positions by month dynamically.

THANK YOU!

[LincolnAF]

Hi Bella,

Thank you for this.  My challenge is that we have a lot of rehires, and contractors that terminate and convert to employee.  So, I don't believe that a current date report will work for me to correctly capture monthly headcount in the past because hire date will be the most recent and termination date will be null if the employee is currently active.  I think I need to build this report as a date range report, but I'm not sure what logic I can use to capture headcount going back 13 months.  

Am I correct in my assumption or can I somehow use a current date report as you described?

Thanks

Andy