Human Capital Management Blogs by SAP
Get insider info on SAP SuccessFactors HCM suite for core HR and payroll, time and attendance, talent management, employee experience management, and more in this SAP blog.
Showing results for 
Search instead for 
Did you mean: 


Headcount Trend Report provides an overview of the number of employees in an organization / department/ division over time periods. The periods can be Year / Quarter / Month. Such a report enables organizations to effectively monitor the employee-related information and headcount over a period in a single view.

How to create HeadCount Trend Over Years?

SAP Successfactors Workforce Analytics (WFA) provides time-trended analysis. All metrics in WFA can be easily sliced by the available dimensions - including the time hierarchy (year/quarter/month). Therefore, the general advice is to consider WFA when you have a need for analyzing metrics across time.

However, for simple reports where you just need headcount trend over a few (specific) time periods (Months/Quarter/Years), there is a way to create such report within report stories. It can be achieved via calculated columns.

Let us consider a use case of creating headcount trend over different years.

Different steps involved in creating a headcount trend report over years are detailed below.

  • Begin with selecting Job Information table in Query Designer. Should mandatorly include columns

    • Employment#Job Information#Effective Start Date

    • Employment#Job Information#Effective End Date

  • Once the columns are selected, create calculated columns (measure) to indicate if an employee belong to on organization for a particular year. In the sample code snippet below, employees available to an organization last year is marked True.

IF(YEAR([Employment#Job Information#Effective Start Date]) =YEAR(CURRENTDATE())-1  and (YEAR([Employment#Job Information#Effective End Date])>YEAR(CURRENTDATE())-1 or [Employment#Job Information#Effective End Date]=[Last Day Current Year-1]), 1, 0 )

* Change the numeric value (1) in all places for previous years. For last year it is -1, one year before last year -2 etc.

  1. Last Day Current Year is another calculated column with the formula

TODATE(CONCAT(TOTEXT(YEAR(CURRENTDATE())-1), "/12/31"),"yyyy/mm/dd" )

* Change the number accordingly for previous years

  • Similarly based on requirement calculated column to represent required years for reporting.

    • Note: Change the logic in calculated columns accordingly for other years

  • Next step would be to get the sum of employees which are marked as True in the calculated column to get the headcount for that specific year.

    • For that, select Aggregation type as either none or sum for all the calculated columns representing years in query the designer

  • Must ensure that either no time filter is applied or a data range filter which covers the required period for reporting is applied

  • Navigate to Story Designer and insert any aggregated widget

    • Select calculated columns in the widget

HeadCount Trend

Note: For headcount trend over month / quarter, modify the calculated columns logic accordingly.


In this blog post we saw how to create a headcount trend over time periods. The steps involve creating requisite calculated columns for the time, applying aggregation and visualizing in story designer.