Thursday, January 22, 2009

OBIEE rowbased calculations in a pivot table

One of the little known possibilities are the row based calculations in a pivot table. Let's start with a simple report based on the sales sample RPD:

image

image

 

From this base report we make a simple pivot table:

image

image

In the columns section click on the column properties and select "New Calculated Item"

image

Now can create a rowbased calculation:

image

You can also play with the other functions:

image

Till Next Time

8 comments:

matt said...

Do you know if it is possible to use the evaluate function in the formula field of a pivot table calculated item?

John Minkjan said...

Hi Matt,

It can't be done in the pivot table since the pivoting is done in the BI server, not in the database. Maybe you can use the EVALUATE or EVALUATE_AGGR instead in your table view and use that column in your pivot view with the aggregation set to FIRST

matt said...

good point, thank you.

Venky Iyer said...

would it be possible to have a case statement in the calculated item for a pivot

Unknown said...

Hi John,

I have got one problem in the Pivot table view. This pivot table contains 2 rows Category and Sun Category ,One Column Date,this date having two values currenct date and previous date; and four measures sub1,sub2,sub3 and total.
but i need to find out the varience i.e Total@Current date-Total@Previous date.
but when ever i created the calculated item on date column as Currentdate-Previousdate, it returns differemce between all 4 columns as varience.but i need to perform calculation only one Total measure..can suggest me how to get it.


Regards

Rahul

Unknown said...

Hi John,

I have a requirement to diaply Totals in OBIEE Charts.

Could you pleas ele tme know how to achieve this.

I created calcuated Item for the Totals in pivot view and created Chart for it. Now Totals are dispalying chart. But, Totlas always comes last in the chart.

How can I bring it to the first.

Thanks,
Johnny

Prabhu said...

John,

I am not able to generate my report output in PDF if I am using a calculated member in the PIVOT table.
Any idea how can i resolve this..

Prabhu.

ArunK said...
This comment has been removed by the author.