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 :

{{:dat:obiee:presentation_service:obiee_goal_suppress_detail_row_of_group.jpg|}}

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

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

{{:dat:obiee:presentation_service:obiee_new_dimension_formula.jpg|}}

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 :

{{:dat:obiee:presentation_service:obiee_result_combine_dimension.jpg|}}

===== Second Solution : Conditional Formatting =====

Unfortunately, the cross conditional formatting is not yet supported by OBIEE in a pivot view (
[[dat:obiee:presentation_service: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 : [[dat:obiee:presentation_service: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 :

{{:dat:obiee:presentation_service:obiee_conditional_formating_display_none_detail.jpg|}}

And you get :

{{:dat:obiee:presentation_service:obiee_hide_detail_row_in_pivot_table.jpg|}}

Tags: , ,

3 Responses to “Obiee – How to suppress detail rows of a master group in a pivot view ?”

  1. Saranya says:

    Hi,

    Need ur valuable help here, I tried to design a report in Answers level where in Pivot view I could acheive certain calculations with 1 pivot column.The rest of the columns are as usual like in table view, but im unable to perform Aggregation logics on other columns apart from pivot columns…Is there a way to acheive this? Pls help…

    Thanks in advance !!

  2. Venkat says:

    Hi Gerardnico,

    I have a simillar requirement, but for working though. When I am making the conditional formatting Then I am getting weired results.

    Thanks
    S

Leave a Reply