Symptom
While using timestamp fields Advanced Reporting (REALMS), the timestamp information is removed and only the date is displayed.
Image/data in this KBA is from SAP internal systems, sample data, or demo systems. Any resemblance to real data is purely coincidental.
Environment
SAP SuccessFactors HXM Suite
Reproducing the Issue
- Create a new query / report using Advanced Reporting as the data-source
- Add a time stamp field in the query
The following can be observed: - In Advanced Reporting, only date information is displayed
- Attempting to apply filter will also allow you to filter only based on dates and not on timestamps
- When exported from the preview screen / report distributor / report center, only date information is present
- When exported in Excel from the Query Designer, timestamp information is also included in the fields
Cause
This is expected behaviour.
Advanced Reporting does not support timestamp / Date-time datatype as default fields in the query.
It only supports date datatype.
Resolution
As a workaround, you can create a calculated column, use the desired date as the source and define a custom format.
While defining the custom format, ensure that the hh:mm:ss string is added.
Source and outputs compared:
Breakdown of the custom format 'yyyy-dd-MM hh:mm:ss':
- yyyy - Year in 4 digit format
y or yy will yield a one digit result and yyy or yyyy will yield a 4 digit result - dd - Date with a padding zero
Using just 'd' will still fetch the day but will not include an additional (leading) zero for dates from the 1st to the 9th - MM - Month with a padding zero
Using just 'M' will still fetch the month but will not include an additional (leading) zero for dates from the 1st to the 9th - hh:mm:ss - hour, minute and second values with padding zero.
- These tokens can be rearranged and the seperators ('-' and ':' ) can be changed.
- The column will fetch the datetimestamp as expected, as long as the tokens aren't mixed up by accident
example: using m instead of M will fetch the minut in places of the month.
Use the 'Get Preview Values' button for validation.
The date/time displayed in Advanced Reporting is always the server time. There is no option to switch between server time or user localized in Advanced Reporting.
The workaround is applicable only to date columns that inlude timestamp information when exported from the Query editor (preview / Results).
If a column does not include the timestamp info at all, the workaround is not applicable.
And the export from the query is the only indicator for this.
Date Time Stamp fields are available in Story reporting Type.
See Also
Keywords
date workaround, date-time, ORD, Online report designer, date format, server time localized report, report date , KBA , LOD-SF-ANA-ADV , Advanced Reporting (ODS) , LOD-SF-ANA-SAC , Stories in People Analytics , Problem