OBIEE - How to perform a pivot in a table view

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

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

Discover More
Blog - Obiee 10G – How to suppress detail rows of a master group in a pivot view ?

You have detail row of a group in a pivot table that you want suppress as this : We have two solutions : if you don’t need a total by “promo Category” (by post or by internet), you can create...
OBIEE - How to sort on a measure column of a pivot view ?

In a pivot view, you don't have any options to choose the sort order on any measure columns (column grand total include). If you search to sort on the column grand total, see this article : . The...
OBIEE - How to sort on the column grand total value of a pivot view ?

In a pivot view, you don't have any options to choose the sort order on the column grand total (Amount sold in our case). To sort on a measure column (Direct Sales for instance), see this article : ...
OBIEE - Pivot Measures (The Filter Function)

Indicator, description of Measures are generally the sign that you need to pivot your measure (i.e. to create a Pivot Measure) The revenue is booked. Revenue is the measure and booked is a description...
OBIEE - Table view

You can set them on 11g with the help of FMW Control. Business Intelligence > CoreApplication > Performance tab Maximum Number of Rows to Download to Excel option Maximum Number of Rows Per Page...
OBIEE 10G - Cross conditional formatting on a pivot

OBIEE does not support cross column conditional formatting in Pivot view. For instance, it's not possible to set a red background of a column based on the value of an other column. Below is a workaround...
OBIEE 10G - How to suppress detail rows of a master group in a pivot view ?

You have detail row of a group in a pivot view that you want suppress as this : We have two solutions : if you don't need a total by “promo Category” (by post or by internet), you can create...
OBIEE 10G/11G - Writeback Capabilities (Table of Pivot)

Write Back is the ability to enter values directly into a report and have those values used in calculations and charts in the report. For example, a report can have Sales Quota Amount defined as a write...