General OBIEE

Get Involved. Join the Conversation.

Topic

    Gal Shani
    Duplicate rows in OBIEE reportsAnswered
    Topic posted December 28, 2014 by Gal ShaniSilver Medal: 2,000+ Points, last edited December 28, 2014, tagged Compensation, OBIEE Answers, OTBI, Reports 
    823 Views, 5 Comments
    Title:
    Duplicate rows in OBIEE reports
    Content:

    Hi,

    Tried to eliminate the duplicate rows with the usual way (see attached screenshots), but it's seems this is not working on calculated field ('new salary', 'prior salary', etc'..).

    If someone can share his knowledge regarding this issue this will be very useful.

    Thanks,

    Gal.

    Image:
    Document:

    Best Comment

    Caroline Gladwin

    Others may have better solutions but here are some thoughts to get you started ....

    - the setting you are using only applies by default to attribute columns (these show in criteria tab with blue icon beside the column name rather than yellow ruler icon). The items you reference are measure columns and so do not pickup the duplicate rows settings as standard even if you set it at the column level. That is why you comment that it is "not working".

    You can do something similar to what you want by ...

    1. Select the report items and, for each of the measure columns you want to suppress rows for, choose edit formula. For each one, switch on the "treat as an attribute column" check box towards the bottom of the edit formula screen and also update the column properties to suppress rows

    2. Create your report as a pivot not a table

    3. Drag your measure columns that you want to suppress rows for into the rows section of the pivot with another attribute column to its right before you get to the measures section. The report will then understand what you are trying to do (using group by functions behind the scenes) and the columns will then suppress rows as you want.

    See the attached XML for an example report with this created based on the Salary History subject area. If you look in the edit formula for the adjustment amount you will see the checkbox set and if you look closely at the pivot table layout you will see how I am forcing it to know to suppress those row values. I am not sure how practical it will be for your layout though,

    One other comment - if you open up the help screens for the "edit formula screen" and scroll down a bit, you will find the section that explains this setting in much more detail.

     

    Comment

     

    • Julian Challenger

      Gal,

      Could you post your XML please?

      Thanks

      Julian

    • Caroline Gladwin

      Others may have better solutions but here are some thoughts to get you started ....

      - the setting you are using only applies by default to attribute columns (these show in criteria tab with blue icon beside the column name rather than yellow ruler icon). The items you reference are measure columns and so do not pickup the duplicate rows settings as standard even if you set it at the column level. That is why you comment that it is "not working".

      You can do something similar to what you want by ...

      1. Select the report items and, for each of the measure columns you want to suppress rows for, choose edit formula. For each one, switch on the "treat as an attribute column" check box towards the bottom of the edit formula screen and also update the column properties to suppress rows

      2. Create your report as a pivot not a table

      3. Drag your measure columns that you want to suppress rows for into the rows section of the pivot with another attribute column to its right before you get to the measures section. The report will then understand what you are trying to do (using group by functions behind the scenes) and the columns will then suppress rows as you want.

      See the attached XML for an example report with this created based on the Salary History subject area. If you look in the edit formula for the adjustment amount you will see the checkbox set and if you look closely at the pivot table layout you will see how I am forcing it to know to suppress those row values. I am not sure how practical it will be for your layout though,

      One other comment - if you open up the help screens for the "edit formula screen" and scroll down a bit, you will find the section that explains this setting in much more detail.

       

    • Caroline Gladwin

      Here is the report XML - cut and paste this into the Advanced tab of a new report and click Apply.