OBIEE 10G - How to suppress detail rows of a master group in a pivot view ?

> Reporting and Data Access Tools > Oracle Business Intelligence (OBIEE/Siebel Analytics) > OBIEE - BI Presentation Service (SAW/OBIPS)

1 - The case

You have detail row of a group in a pivot view that you want suppress as this :

We have two solutions :

  1. if you don't need a total by “promo Category” (by post or by internet), you can create a new dimension with a formula
  2. if you want a total by “promo Category”, we must perform a cross conditional formatting. One each column, we must add this conditional statement.
if "promo category" = internet then suppress the value

3 - First Solution : New Combine Dimension

Click on the formula icon and enter this formula to create a new combine dimension :

CASE WHEN  Promotions."Promo Category" = 'post' THEN Products."Prod Category" ELSE 'Post' END

It remains to remove the column Promotions.“Promo Category” from the pivot view with a drag and drop of it in the exlcuded area and you get :

Advertising

4 - Second Solution : Conditional Formatting

Unfortunately, the cross conditional formatting is not yet supported by OBIEE in a pivot view ( OBIEE 10G - Cross conditional formatting on a pivot)

We must go therefore back to a table view, pivot the table and apply a css property to suppress the detail rows. ( To know more about what is and how to pivot a table in a table view : OBIEE - How to perform a pivot in a table view )

4.1 - Pivoting the table

We must pivot the table on the column “Channel Desc”. This column as 5 distinct values :

Direct Sales
Internet
Partners
Tele Sales

We create therefore 6 columns :

  • one for the “Promo Category”
  • one for the “Prod Category”
  • 4 formula columns for each distinct value of the “Channel Desc”
    • one column for Direct Sales
    • one column for Internet
    • one column for Partners
    • one column for Tele Sales

In each column of “Channel Desc”, we insert a filter formula.

Example for the column “Direct Sales”

Column Formula
Direct Sales FILTER(“Sales Facts”.“Amount Sold” USING (Channels.“Channel Desc” = 'Direct Sales'))
Internet FILTER(“Sales Facts”.“Amount Sold” USING (Channels.“Channel Desc” = 'Internet'))
Partners FILTER(“Sales Facts”.“Amount Sold” USING (Channels.“Channel Desc” = 'Partners'))
Tele Sales FILTER(“Sales Facts”.“Amount Sold” USING (Channels.“Channel Desc” = 'Telesale'))

The table is nu in a pivot form and we can apply the conditional formatting to suppress the detail record.

Advertising

4.2 - Suppression of the detail rows and the result

In each column of the report, you must add this conditional formatting in the column properties :

Promo Category is equal to / is in internet

and add this css property to hide the values :

display:none

Example :

And you get :