Monday, October 21, 2013

OBIEE - Sorting the Pivot by Totals

I was requested to sort  a pivot table by a total column.
For example:

In my I can easily sort on any of the year Revenue but no on the total.
My initial reaction was to create a calculation in criteria, something like sum("Base Facts"."Revenue" by "Products"."Product Type") and put it in the left axis. 
But I preferred a better solution.

I created a new calculated item:
Based on Time.Per name Year (the one I have in the top axis)

The calculation,  I named Total Revenue, is sum(*). With "custom formula" in function.

Now I can get rid of the original Total:
And have this:
I can sort by 2008, 2009, 2010, but unfortunately I still can't sort by Total Revenue!!??

To achieve that switch the measure location in the pivot.
Now I can sort the relevant Revenue:

And rearrange the top Axis if I desire that:

Rearrange using the Layout (see bellow) and not by direct dragging of the axis. For some reason, this option breaks the order by...


No comments:

Post a Comment