Oracle Analytics Cloud and Server

Hiding duplicate rows

Received Response
362
Views
6
Comments

Hi all,

I have a question. How to make Oracle BI Answers not to hide duplicate rows?

I disabled DISTINCT_SUPPORTED in database features in BI Administration Tool (as it was described here - Shiv Bharti's Blog: Ability to Enable/Disable DISTINCT and ORDER BY Clause in OBIEE at Report Level ), so there are no distincts in report's query when I check query log, but rows in the report are still hiding.

For example, here I have table:

PERIODA1DR
NOV-16Sample100
NOV-16Sample100
NOV-16Sample300

I want to see all the data in my report, all rows, but in the report data display as:

pastedImage_4.png

I added rownumber column to check - and it shows that the hided row is counted but not displayed. Any ideas how to avoid this? I have Oracle version 11.1.1.7.150120 if it is important.

Thanks in advance.

Answers

  • Hi,

    A quick one: did you unchecked "suppress" and select "repeat" in "Column properties" > "Column format" > "Value suppression".

    As you say in the physical query there is no distinct it means the physical query return the duplicate? Then it's only the analysis hiding it (that's why I point first to the "suppress"...

  • Odarin
    Odarin ✭✭✭

    Yes, I unchecked "suppress" on all columns, but it is not works.

    Yes, the physical query returns duplicates, so I believe that it is indeed the analysis problem.

  • asim cholas
    asim cholas ✭✭✭✭✭

    Better make it a dimension instead of fact. Ie create an alias and self join with the table and use it. It will work. As you dont need aggregation dimension will work.

    OR

    Make your amount column as a key in RPD. Still it wont aggregate.

    These are not direct solution as it is the default behavior of OBIEE. But if you really need some work around you can try those.

    Thanks

    Aj

  • in rpd, if  you go the logical table source in the table -> content tab-> check on  select distinct  values option -> save it and try to see the results now

  • Odarin
    Odarin ✭✭✭

    nay, unfortunately it didn't help

  • Thomas Dodds
    Thomas Dodds ✭✭✭✭✭

    Add the unique row key from the database to your analysis just to make sure you have it right ... the display is showing correctly given the QUERY GRAIN ... you need to pin that grain at the same level as the TABLE GRAIN to get what you want.