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
Solutions
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 :
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 )
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 :
