OBIEE - How to perform a pivot in a table view

1 - About

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

3 - 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 :

Promo Category Channel Desc Amount Sold
internet Direct Sales 2,658,953
internet Internet 642,251
internet Partners 1,097,558
NO PROMOTION Direct Sales 115,549,450
NO PROMOTION Internet 26,154,910
NO PROMOTION Partners 51,682,188
post Tele Sales 554,853
TV Direct Sales 696,703
TV Internet 4,166

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

Direct Sales
Tele Sales

And the pivot table will result of this sql statement :

   "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"
group by
   "Promo Category";

4 - 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” :

5 - 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 :

Data Science
Data Analysis
Data Science
Linear Algebra Mathematics

Powered by ComboStrap