- 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.
- Duplicate rows are appearing on Advanced Reporting.
- SAP Successfactors Workforce Analytics
- Report Canvas - Advanced Reporting query
- 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.
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.
In this, we will take example of above scenario of dependent information.
- Intially the report is giving 3 record for a user as per above screenshot.
- Table join will be like below:
- 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:
- Click on Edit table link of original dependent table
- Click on "+" button on top to duplicate the table( as shown below).
- This will add new dependent table as below:
- 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:
- Edit the table(step 4) to which you want to join new tables.
- Click on eye button( as shown below)
- Now if you drag any column from Dependent Perfornal Info table, it will be linked to table Dependents(2).
- Now you have to put filter on individual tables to fecth one type of Dependent.
How to put filter on individual table:
- Select the table (Dependent) and Edit( step 4)
- Put filter on Relationship field to include on one type of dependent( Spouse, Child1, Child2)
- This will give one dependent information as shown below.
- Similarly put filer on other 2 dependent tables so that the final result is like below.
- Finally, you can rename the columns, remove unwanted columns using "Columns" tab
- Final Result
3 rows present initially are converted to single row.
Multiple rows in Advanced Reporting One row per user Remove duplicate rows report Canvas , KBA , LOD-SF-ANA-ODS , Advanced Reporting , How To