SAP Knowledge Base Article - Public

2587890 - Advanced Reporting displays only the date for timestamp fields (Canvas / ORD Report)

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

  1. Create a new query / report using Advanced Reporting as the data-source
  2. 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

      Timestamp_Excel.png

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.

customDateFormat.png

Source and outputs compared:
compared.png


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

2726015 - Changing Date Display Format in Online Report Designer or Report - Canvas

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

Product

SAP SuccessFactors HCM Suite all versions