How to get Unique /Distinct Records in Business Object Webi reports.
We are having so many options to get unique/distinct records in webi reports .I will try to accomplish all the options that are available in single document.
- Universe : In Select Clause if we use “distinct” then we will get unique records.
- Query : In Query properties we have option called “Retrieve duplicate rows” option. We need to disable for getting unique record’s.
Note: By default this option will be enabled.
- Report : There are 2 options at report level for getting unique records.
- Option A : Right click on created block we will get an option called format block there we will have an option called “Avoid duplicate
row aggregation”. we need to disable for getting unique records.
- Option B : By using “Previous Function” we can get distinct records.
- Scenario : Consider below table it contains EMP id, Emp name, Emp sal .Emp id contains duplicate values of Emp id’s
To avoid duplicate records I created one variable using previous function with name as v_unique like below:
Note: Before creating variable make sure that the object which you used for creating variable that needs to be sorted in ascending order.
I used created variable in report now it will show as “0” for duplicate records like below :
Apply filter on block with created variable value as equal to “1” then it will show only distinct records like below:
Hope , this document might be helpful for some of them.