3505244 - How to fetch latest record in Story report?

How to fetch latest record in the Story report when user has multiple records for the object?

Image/data in this KBA is from SAP internal systems, sample data, or demo systems. Any resemblance to real data is purely coincidental.

SAP SuccessFactors HCM Suite

Example domain for reproducing the issue: Formal Education

1. Login to the BizX tenant
2. Navigate to Reporting
3. Create a Story report with 'Formal Education' domain.
4. Add columns as user ID, Degree, Start Date, End Date etc.
5. Save the query
6. Create a table widget.
7. Run the report and notice in the results:
   
   

Users that have multiple education records will show in multiple rows. Example: user has one record for bachelor's degree and another for master with two different end dates.

Requirement is to fetch only the latest education record for all users.

1. Create a Calculated Measure of type 'Date Difference' between 'End Date' and "Current Date" with Aggregation Operation as MAX to return the date difference.

2. Create a Measure-Based Dimension with Dimension Context as "User ID".

3. Create a Measure-Based Dimension with Dimension Context as "User ID" and "End Date".

• The dimension 'User ID - Dimension' will return the MAX value for User ID.
• The dimension 'User ID & End date - Dimension' will return the MAX value for End Date for same person (which is the unique days difference for individual rows).

6. Create Dimension Calculated column (MaxOnly) to check if dimension 'User ID - Dimension' and dimension 'User ID & End date - Dimension' are same.

• This will flag the record where it shows the latest date.

7. Create a filter on MaxOnlyFlag field (on 0) to fetch latest End Date Education record.

8. The final report data:

Important notes:

1. Date‑Based Selection Behavior
When multiple records occur on the same calendar day, the calculation retrieves all records for that date, regardless of differences in timestamp. The selection logic evaluates only the date stored in the database and does not consider the time component.
For example, if a user has two approved sessions on 12 January 2025, one as Jan 12, 2025 4:19:25 PM and another at Jan 12, 2025 1:29:30 PM, both entries will be returned. Even though 4:19:25 PM is chronologically later, both records appear because they fall on the same day. This is a known system limitation.

2. Impact of Story Report Time Zone Conversion
Story reports apply time zone conversion based on the viewer’s machine time zone. As a result, the same record may appear on different dates for different users, potentially causing confusion when determining which entry is the “latest.”

Example:
An user in Brazil (BRT, UTC‑3) sees timestamps such as:

• 12 Jan 2025 4:19:25 PM
• 12 Jan 2025 1:29:30 PM

An user in India (IST, UTC+5:30) sees the same records converted to:

• 13 Jan 2025 12:49:25 AM IST
• 12 Jan 2025 9:59:30 PM IST

Because of this conversion, the IST user visually sees the events occurring on different dates, which may lead to the incorrect expectation that only the record on the later date should be selected, considering the previous provided calculations logic. 
However, the calculation always uses the date stored in the database, not the date after time zone conversion in Story. In this case, both records still belong to 12 January 2025 (database date) and are therefore included in the output, as per current system limitation.

3. Recommendation
To confirm the actual date stored in the database and avoid misunderstandings caused by time zone conversion, you can create a calculated column that bypasses Story’s time zone conversion behavior. This approach is demonstrated in KBA 3679706 – How to avoid Time Zone conversion in Story reports.
Using such calculated column allows you to validate whether records truly belong to the same date in the underlying data.

KBA 3679706 – How to avoid Time Zone conversion in Story reports

story, latest, result, record, calculation, calculate, measure based dimension, date difference, column, multiple, report, duplicate, single, many, avoid, prevent , KBA , LOD-SF-ANA-SAC , Stories in People Analytics , LOD-SF-ANA-SAC-QDS , Query Designer - Filter, Calculated Column, Params , LOD-SF-ANA-SAC-SDS , Story Designer - Story & Formatting , How To