Summary
A SuccessFactors report designer/admin/author can easily notice the advantage of Story, especially when it comes to aggregating data. It is an enhanced function compared to Canvas or Table report. After a data model is built in Query designer, in Story, summarizing data is easier in Story, for example, headcounts, total compensation, or count of requisitions. Story provides multiple types of calculation and ‘Aggregation’ type conveniently calculates unique counts based on selected aggregation dimensions.
However, there are multiple situation where straight forward aggregation capabilities are not sufficient anymore but so-call "Exception Aggregation" is need. This blog post provides some further insights how to use this particular aggregation type.
Introduction
There’s an exception aggregation using ‘Aggregation’ calculation type to ensure the calculation with multiple rows of user ID data are accurately aggregated. The use case is when you want to accumulate any measure on a parent object which has a 1:N relationship to children objects. “Exception Aggregation” is so-to-speak the “Measure-counterpart” of the ‘operation’ and “Count Dimension” which is applied for a distinct count of dimensions.
Use case 1: Total FTE
The use case will become clear with the help of following example: Anchor of the query is job Info – which also has the FTE of the employee. What should be accumulated – and other objects with 1:N cardinality are joined (in the concrete case it’s the “Direct Report JobInfo”). See the example below, simply summing up FTE will not work (i.e. it would give “13” for the 13 records) since 2 of the 6 employees have direct reports (User “Awan” has 4 direct reports and user “Tchin” has 3 direct reports). To consider the FTE of users “Awan” and “Tchin” just once, a trick was applied (=”Exception Aggregation”) by creating 2 calculated story measures as indicated here:
Example how to create the two calculated measures to get the correct number of FTE
Please note that, the same result can be achieved by changing aggregation type of FTE in query. When the data semantic type is ‘Measure’, the default aggregation type is NONE. Change the type to Max/Min to only pick 1 value per user ID.
After the query is set, create a calculated column (Aggregation) in Story. Below is an aggregation calculation then the result will be same as the first approach.
Use case 2: Average days of requisition age
Recruiting data with application data result in multiple rows of data unavoidably due to the relationship between requisition and applications. A requisition has multiple applications (unless no candidate is interested) and this will cause multiple rows per same requisitions. Since the age definition is an age of requisition, the average should be an average per requisition.
To calculate average age per requisition, exception aggregation is required. Otherwise, it will end up summing all ages listed by applications, not by requisitions. This means, to average the data, we need to define the correct data sum and correct count to divide the sum. Since each requisitions have applications data, the sum of age will be an inflated number. Exception aggregation can overcome this.
Step 1: First step is in query designer, set ‘Age’ field to be measure and aggregation type ‘Max’ to ensure it is only picking up one value per requisition:
Step 2: Save the query and in Story, create a calculated column to sum ages of requisitions. This will only add one age value per requisition:
Step 3: Create another calculated column to average the requisition age as below. Notice the measure is not age, but the sum of requisition age ‘Requisition Age Sum’, which is calculated in Step 2.
For example, we have 3 requisitions, 2844, 2845, and 2861. Requisition 2844 has 1 application data, 2845 is null, and 2861 has 12 applications. Each requisition has age of 215, 215 and 32 days respectively. Requisition age sum is 462 days, not adding all values which is 814 (= 215 + 215 + 14 x 32) days due to the repetition from application for requisition 2861. And we’re seeing a correct average of requisition which is 462 divided by 3, that is 154 days.
- SAP Managed Tags:
2 Comments
| User | Count |
|---|---|
| 5 | |
| 3 | |
| 2 | |
| 2 | |
| 2 | |
| 2 | |
| 2 | |
| 2 | |
| 2 | |
| 1 |
