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

[...] View the original article here [...]

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

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