OBIEE - How to perform a pivot in a table view

About

This article explains how to obtain a pivot view in a table view through the creation of pivot measures.

Articles Related

What is a pivot table ?

A pivot table is just the same table where :

• the pivot column has been suppressed
• for each distinct value of the pivot column, a column with a conditional sum is created.

Example : If I want to pivot this table from the SH schema :

If the pivot column is the column “Channel Desc”, we have 5 distinct values :

Direct Sales
Internet
Partners
Tele Sales

And the pivot table will result of this sql statement :

select 
   "Promo Category", 
   sum(case "Channel Desc" = 'Direct Sales' then Amount Sold else 0 end) "Direct Sales",
   sum(case "Channel Desc" = 'Internet' then Amount Sold else 0 end) "Internet",
   sum(case "Channel Desc" = 'Partners' then Amount Sold else 0 end) "Partners",
   sum(case "Channel Desc" = 'Tele Sales' then Amount Sold else 0 end) "Tele Sales"
from
   FactTable,
   DimensionTable,
   ...
group by
   "Promo Category";

Transformation of a table in a pivot table

For the query above, we need 6 column :

• one for the “Promo Category”
• 4 formula columns for each distinct value of the “Channel Desc”

In each formula column and for each distinct value of “Channel Desc”, enter a filter formula.

Below an example for the value 'Direct Sales' :

FILTER("Sales Facts"."Amount Sold" USING (Channels."Channel Desc" = 'Direct Sales'))

And here for the value “Internet” :

The Result

This method has a weak point is that you must know the number of distinct value in advance. For instance, if a new distinct value appear in the column “Channel Desc”, you must change your report.

Below the pivot view :