Monday, January 31, 2011

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”
ColumnFormula
Direct SalesFILTER(“Sales Facts”.”Amount Sold” USING (Channels.”Channel Desc” = 'Direct Sales'))
InternetFILTER(“Sales Facts”.”Amount Sold” USING (Channels.”Channel Desc” = 'Internet'))
PartnersFILTER(“Sales Facts”.”Amount Sold” USING (Channels.”Channel Desc” = 'Partners'))
Tele SalesFILTER(“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 :

No comments:

Post a Comment