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

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

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 :

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 )

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.

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 :


Powered by ComboStrap