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.

                     n5.PNG

        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.

  Thanks,

  Seshu

To report this post you need to login first.

4 Comments

You must be Logged on to comment or reply to a post.

  1. Scott Gilsdorf

    Seshu, I just wanted to thank you for taking the time to put this together but also to tell you that I believe your approach was very concise and clear.

    (1) 

Leave a Reply