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.