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

Saw Object

The case

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

Obiee Goal Suppress Detail Row Of Group

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

Obiee New Dimension Formula

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 :

Obiee Result Combine Dimension

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 :

Obiee Conditional Formating Display None Detail

And you get :

Obiee Hide Detail Row In Pivot Table





Discover More
Saw Object
OBIEE 10G/11G - Pivot View

This view displays results in a pivot table, which provides a summary view of data in cross-tab format. Pivot tables provide the ability to rotate rows, columns, and section headings to obtain different...



Share this page:
Follow us:
Task Runner