SAP Knowledge Base Article - Public

2545465 - How to convert multiple row data in single row in Advanced Reporting (transpose)

Symptom

  • How to convert multiple row data in single row on Advanced Reporting.
  • How to remove duplicates in Advanced Reporting to get single row per user.
  • Convert Rows to Columns

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 HCM Suite

Reproducing the Issue

A Query / Report fetches multiple rows per user because of different values in one or more columns

Ex: Fetching the dependent details give as many rows as there are dependents.
In below example, there are 3 rows coming for a user.
1_new.png

Cause

The behavior is expected, since this is how the data is stored in the database.

Resolution

Although a true transpose function is unavailable in Advanced Reporting, we can achieve it to an extent with the help of the Duplicate table and edit restrictions functionalities in Advanced Reporting.

This approach can be applied to any scenario (example Pay Components) as long as the following two conditions are satisfied:

  1. We have a definite number of rows that have to be converted to columns.
    For a Dependents use case, this would be number of dependents
    For a Pay Components use case, this would be the number of Pay Components

  2. It is possible to filter the desired entries using one ore more parameters.
    For a Dependents use case, this would be the Relationship ID
    For a Pay Components use case, this would be the internal ID of the Pay Component


In this example, we will consider the above scenario of dependent information.

  1. Initially the report is giving 3 record for a user as per above screenshot.
  2. Table join will be like below:

    2.JPG
  3. To achieve a single row per user, you have to duplicate the dependent tables as many time as the number of dependents (3 times in this example).
    Once the tables are duplicated, put filter on individual table to fetch one particular dependent type (spouse, child1, child2).

    How to Duplicate the table:
  4. Click Edit table link of original dependent table

    3.JPG
  5. Click "+" button on top to duplicate the table(as shown below).

    4.JPG
  6. This will add new dependent table as below:

    8.JPG
  7. To join further tables from duplicated table. Ex: Add Dependent Personal Info(2) linked to Dependents(2), you have to change the path of the query.

    How to Change the query path:
  8. Edit the table(step 4) to which you want to join new tables.
  9. Click eye button(as shown below)

    9.JPG
  10. Now if you drag any column from  Dependent Personal Info table, it will be linked to table Dependents(2).

    12.JPG
  11. Now you have to put filter on individual tables to fetch one type of Dependent.

    How to put filter on individual table:
  12. Select the table (Dependent) , Edit(step 4) and Edit Restrictions.
    NOTE: It is important to use the Edit restrictions option and not the query / report's primary Filters

    15.JPG
  13. Apply a filter on the Relationship field to include on one type of dependent (Spouse, Child1, Child2)
    This can be identified by hovering on the Report values section while applying the restriction.

    tool_tip.JPG


    5.JPG
  14. This will give one dependent information as shown below.
    6_new.png
  15. Similarly put filer on other 2 dependent tables so that the final result is like below.
    11_new.png
  16. Finally, you can rename the columns, remove unwanted columns using "Columns" tab

    14.JPG
  17. Final Result

    13_new.png

3 rows present initially are converted to single row.

Keywords

transpose, multiple, row, to, column, duplicate, convert, single, Pay, Component, Dependents, Advanced, Reporting, Online, Report, Canvas, Center, ORD, Designer, Analytics , KBA , LOD-SF-ANA-ODS , Advanced Reporting , How To

Product

SAP SuccessFactors HCM Suite all versions