SuccessFactors People Analytics (Stories): How to create change report (current vs previous values)

[StephanieBM01]

The other day, I was asked if it was possible to create a change report in Stories, similar to what we used to do in Canvas. Although I always assumed it was possible, I never had the time to explore it until now. To my surprise, I couldn't find the equivalent of the great Advance Reporting – Create Change Reports Manually guide for People Analytics (Stories). Hence, this blog post!

Step 1:
Start by creating the query with the "Today's value." The principle is quite similar to Advance Reporting. First, you need to select an "anchor" table to initiate the query. In this case, I selected the Personal Info table and added the related Job Information table. By default, when you create a new query the Time filter of the anchor table is set to Today’s and added related table set to Same Filter as First Table, please leave this it that!

Step 2:
Then, Duplicate the Job Information table. It's important to note that, similar to Canvas, you cannot copy the starting ("anchor") table. The option to duplicate is only available for an added related table.

Step 3:
Modify the time filter on the Copy table ONLY to include all records. To do this, click on the table, then select the Filter icon. From there, choose the Time Filter option and select Remove filter. It's important not to modify the time filter of the original Job Info table or Personal Info, which should remain set as "Today".

Step 4:
Now, it's time to select the columns we need for our report. Let’s say the objective of this report is to identify changes in the employee's Location (a Foundation Object) and on the employee's Work Arrangement Type (a picklist).

I have selected the fields needed and re-labeled everything to make it easier to follow along for the rest of this blog. Additionally, I have also included the Start Date, End Date, and Event Reason columns. I find it helpful to have these columns for testing the accuracy of our report at the end...

Step 5:
At this point, if you Preview the Query, it won’t look good at all 😊. Don’t worry, we just need a bit of filtering to clean it up! But let’s try to understand what we see. (PS: I’m not database specialist but let’s try to explain it!).

• For each employment record, the system will generate a line. This information is retrieved from the Copy table, which does not have a time filter, resulting in all employee records being included in the output. In our example above, the employee has 2 records in Job Info, hence 2 lines in our report (for now 😉).
• Each line will consist of Today’s values for all fields labeled as "Current" on the left side. These values are obtained from the Job Information original table, which only provides values as of Today's date (as per its Time Filter). Additionally, on the right side of the line, the query returns values from each saved record (including the Today’s values). It does look weirder if you have more saved records, but basically each line is a combination of today’s record and another record of the employee.

Step 6:
Our next steps are focused on refining the query to ensure that it returns only one line for each employee. This line should include the current record for today and the corresponding previous record, which ended the day before the start date of the current record. To accomplish this, we will create calculated columns that compare the Start Date of the current record with the End Date of the previous record. If there is a one-day gap between these dates, it indicates the line we want to keep in our results. This comparison will help us filter and identify the correct records to include in our final query result. Here is the formula:

Step 7:
Now, we need to apply a filter to our query based on the calculated column we created. This filter will ensure that we keep only the line that contains the data for the current and previous records. Additionally, we will take this opportunity to include only active employees in our results.

Step 8:
We're almost there! The last query step is to create calculated columns for the fields where we want to see the changed data. These calculated columns will help us identify and display the specific changes that have occurred. By creating these columns, we can easily compare the values between the current and previous records and highlight any differences. Here is our formulas for the Location change and the Work Arrangement Change columns:

Step 9:
Now, let’s wrap things up and preview the result in a Story. Finish the query and create a beautiful (or useful) table. In this table, we will be able to see the Current and Previous Values for the selected fields, as well as verify that our Changed column is functioning correctly.

Conclusion:
Therefore, this is how we can create change columns in Stories.

I hope you found this post helpful and informative! If you enjoyed reading it and found value in the information shared, I would greatly appreciate some Kudoes in return. Your support and encouragement through virtual high-fives is part of my motivation to continue sharing cool things to do in SuccessFactors.

Cheers,
Stéphanie