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

> Nico Web Log (Blog) > Blog - 2009

1 - The case

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

We have two solutions :

  • if you don’t need a total by “promo Category” (by post or by internet), you can create a new dimension with a formula
  • 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

2 - Solutions

2.1 - First: 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

2.2 - Second: 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 )

2.3 - 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

2.4 - 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 :

weblog/2009/0410-obiee-how-to-suppress-detail-rows-of-a-master-group-in-a-pivot-view.txt · Last modified: 2018/01/14 17:20 by gerardnico