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

• 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.

SAP SuccessFactors HXM Suite.

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.

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

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:
   
   
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
   
   
5. Click "+" button on top to duplicate the table(as shown below).
   
   
6. This will add new dependent table as below:
   
   
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)
   
   
10. Now if you drag any column from  Dependent Personal Info table, it will be linked to table Dependents(2).
    
    
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
    
    
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.
    
    
    
    
    
14. This will give one dependent information as shown below.
    
15. Similarly put filer on other 2 dependent tables so that the final result is like below.
    
16. Finally, you can rename the columns, remove unwanted columns using "Columns" tab
    
    
17. Final Result
    
    

3 rows present initially are converted to single row.

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