Obiee – 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 table 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_conditionnal_formating_on_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_transformation_table_to_pivot )

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 :