2347520 - ORD Advanced Reporting - How to display Previous and Current values in the same report

SAP Knowledge Base Article - Public

2347520 - ORD Advanced Reporting - How to display Previous and Current values in the same report

Symptom

How to display Previous and Current values in the same report in Advanced Reporting (Legacy ODS & Realms)

Guide to the manual method for creating Change Reports to show previous and current values in Advanced Reporting.

The manual change reporting method works from the premise of "Base" or anchor table and then joining two instances of the table containing the change reporting columns onto this base table.

Two separate instances of a table allows the query to apply a different date filter to each instance, which in turn retrieves the current and previous values. Other tables unrelated to the change column can be pulled into the query for additional report data if required.

The following steps will show to to display the "Current" the "previous" Values. however the same method can apply for historical (rather than the current) values simply by adjusting the date filters on the relevant tables.

Note that this method shows changes that occurred within a day timeframes. Should an employee have more than one change in a single day, only the last record in that day will be captured in the results.

 A. Duplicating the Change Reporting table in the "new" Advanced Reporting UI (REALMS)

Example: EC Job Information Changes: For this example we will look at the employee Marcus Hoff. He is currently in the Enterprises Department and the Industries Cost Center, having moved from Industries and Industries Executive Office respectively.

See the attachment document File -Page 1-23 (From page 5 to 11 )

Begin the query to start:(1)

  • we will pull in data from the table which will be the anchor table: Typically this will be the Person > Personal Information table for queries that are Change Reporting on employee Job data, or reporting on employee Compensation data.
  • Select the Person category from the drop down and open the Personal Information table. Pull in the columns required from this table.

Pull the first change table: (2)

  • Navigate to the Global Job Information table from the Personal Information table and pull in the columns required for change reporting.
  • In this example we are looking at changes on the Department and Cost Center columns. The Effective Start/End Dates have also been selected so that we can see the "timeline" in the finished report 

Example1.png

Duplicate the first change table:

  • We now need to create a second instance of the table containing the change "columns" (1)
  • Open the column menu for the Global Job Information table and click Duplicate Table. (2)

Steps to duplicate first change table.png

Second instanceof the change table is now created:

The Global Job Information table has been duplicated, with the identical column settings as the original.

 

  • Global Job Information will be the table retrieving the "current" Job Information and,
  • Global Job Information (2) will be the table retrieving the previous Job Information.

We can now easily identfy which Department + Cost Center is the "current" and which Department + Cost Center is the "previous". We will rename the columns in each table appropriately.

 

two job info tables.png

We now have a report output that is duplicating the current Department and current Division for each employee. This occurs because both instances of the Global Job Information table defaults to the "Current Date"

As we can see in the screenshot below, the group (1) have identical results as the group (2).

New UI example.png

Please jump to the (C) Finalize the manual Change Reporting query by adding the required filters section where we will apply date filters to retrieve the proper current & previous results for Department and Cost Center.

 

B. Duplicating the Change Reporting table in the "Classic" Advanced Reporting UI (ODS)

Example: EC Job Information Changes: For this example we will look at the employee Marcus Hoff. He is currently in the Enterprises Department and the Industries Cost Center, having moved from Industries and Industries Executive Office respectively.

To start: Pull In the first "hange" Table:

we will pull in data from the table which will be the anchortable. Typically this will be the Employment Details table for queries that are Change Reporting on employee Job data, or reporting on employee Compensation data.

  • Open the Employment Details table under Detailed Reporting
  • Employee Central and drag on User ID (and any other required columns from this table).   (1)
  • Navigate to the Job Information table (also under Detailed Reporting > Employee Central).  (2)

In this example we are looking at changes on the Department and Cost Center columns. Ensure the Advanced view is enabled and pull the Department (Label) column onto the canvas to add the Job Information table into the query.

Legacy 1 and 2 table.png

Configure the join for the first "Change" Table:

As we are in Advanced view, the Join Selector dialog will open ensure the User ID > User ID join is selected and click OK.

join selector.png

 

Configure the columns for the first "Change" Table:

Enable the remaining required columns for the Job Information table:

  • Effective Start Date
  • Effective End Date
  • Cost Center (Label)

Duplicate the first "Change" Table:

Now we will duplicate the first change table in order to create a second table:

  • We now repeat the process of pulling in the Job Information table to create a second instance of the table containing the "change" columns.
  • Return to the column menu and drag the Department (Label) column again onto the canvas.

Duplicate firsttable.png

 

 Make a New instance (duplicate) of the"change" table:

When columns from a table that already exists in the query are pulled into the query, you will be offered the choice to add those columns to the existing table or to create a new instance of the table.

For Change Reporting purposes we need to create a new instance of the Job Information table, so in the Join Selector dialog click Add to new table instance.

second join selector.png

Configure joinfor the duplicate "Change" table:

Ensure the same join is configured as for the first change table. Here we will again ensure the User ID > User ID join is selected and click OK.

Note the table layout graphic is slightly different here, showing the first Job Information table that we have already pulled into the query.

 Last selector join.png

Second instance of the change is now created

Another instance of the Job Information table is now available in the columns to switch on:

  • Department             (2)
  • Effective Start Date  (2)
  • Effective End Date   (2)
  • Cost Center (Label)  (2)

We now have a report output that is duplicating the current Department and current Division for each employee. This occurs because both instances of the Job Information table default to "Current Date" .

As shown in the below screenshot, the group1 and gropup 2 have identical columns and same date. ( we need to go the last steps (C) to change the date for the second change table to show previous and current.)

 

Legacy UI.png

See the attachment document File - Page 1-23 ( from page 12 to 23 ).

 

C. Finalize the manual Change Reporting query by adding the required filters to display "Previous" values

Apply the Date Filters for Previous Values:

1. Open the Date Options function and click on the Overrides (Advanced) tab. (screenshot (1) )

Click Edit on the Global Job Information table and note that this table is currently set to Current Date. In this example we would like to report on the current Department and current Division from
this table so we will not make any changes here.

Note that should you wish to report on historical values, here is where you will apply the date or date range relevant to the history you wish to capture.

2. Remain in the Date Options function on the Overrides (Advanced) tab. (screenshot (2) )

Click Edit on the Global Job Information (2) table and note that this table is currently set to Current Date. Change this setting to Show All and click Set (note the Set button is obscured in
this screen shot by the Date Type drop down).

3. When the Show All override for the Global Job Information (2) table has been Set, the Overrides (Advanced) tab will show this table in red, with a Clear option next to Edit. (screenshot (3) )

  • Click Save to exit the function. 

Apply filters.png

4. We now have a set of results that shows the current record (1) against all the previous records (2):

final result.png

For additional details see the attachment document File - Page 23-37 ( from page 23 to 34 ).

The Attached document for more details, please downloads the files :  File - Page1-23 and File - Page 23-37

Environment

  • SuccessFactors HCM Core   
  • ORD - Online Report Designer
  • Advanced Reporting

Keywords

  • Display current and preview record
  • Preview data
  • Current Data
  • Display historical records
, KBA , LOD-SF-ANA-ODS , Advanced Reporting , How To

Product

SAP Jam 1605

Attachments

Advanced Reporting - Change Reporting manual method P1-23.pdf
Advanced Reporting - Change Reporting manual method P23-P37.pdf