2545465 - How to convert multiple row data in single row on Advanced Reporting

SAP Knowledge Base Article - Public

2545465 - How to convert multiple row data in single row on Advanced Reporting

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

Environment

SAP Successfactors

Advanced Reporting

Reproducing the Issue

Query is giving multiple rows per user because of different values in a particular column

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

Cause

This is an expected behavior as ther data is being fectched from database where is available in different columns for a user.

 However, you can still remove this partial duplicacy using the "duplicate table" functionality available in Advanced Reporting.

Resolution

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

1. Intially 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 on Edit table link of original dependent table

3.JPG

5. Click on "+" 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 Perfornal 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 on eye button( as shown below)

9.JPG

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

12.JPG

11. Now you have to put filter on individual tables to fecth one type of Dependent.

How to put filter on individual table:

12. Select the table (Dependent) and Edit( step 4)

15.JPG

13. Put filter on Relationship field to include on one type of dependent( Spouse, Child1, Child2)

5.JPG

14. This will give one dependent information as shown below.

6.JPG

15. Similarly put filer on other 2 dependent tables so that the final result is like below.

11.JPG

16 Finally, you can rename the columns, remove unwanted columns using "Columns" tab

14.JPG

17. Final Result

13.JPG

3 rows present initially are converted to single row.

Keywords

Multiple rows in Advanced Reporting One row per user Remove duplicate rows , KBA , LOD-SF-ANA-ODS , Advanced Reporting , How To

Product

SAP SuccessFactors Workforce Analytics all versions