Friday, March 1, 2013

OBIEE 11g - Sorting on the Total in a Pivot


Pivots in OBIEE are a great option of displaying data, especially monthly data.  However, I often run
into a situation where the customer wants me to show trailing 12 months of data, sorted descending
by the total for those 12 months.  Unfortunately, OBIEE doesn't have a sort arrow on the Total.  And, 
creating a column with a total doesn't get me what I need, since it ends up being another metric in the pivot. 
 For example, a customer might want to see this:

Unfortunately, with the limitation of not having a sort button on the total, this is not as simple as one would
 like.  However, there is a way to do this.  What I did in the above example, was create a sorting column. 
 I started out by trying to add a calculation to give myself a SUM of the metric/fact by the Dimension
 ('Utility').  However, if I do this, it shows up as a fact/metric, and it won't let me sort on it.  So, I then tried
 to check the option 'Treat as an attribute column'.  However, this caused performance issues that were 
unacceptable.  So, I finally came to a solution where I created a Rank column, by ranking the sum of the 
metric by the 'Utility' field, and then converted it to an integer (if you convert to a character, it will sort a 
10 before a 2).  I set the option to treat the field as an attribute, and now it works.  Below is the example 
of what I did.

This formula gives me the ability to have a Rank column that I can now put in my pivot and sort on it.
If I want to,
 I can also hide this field, but the downfall of this is that once hidden, none of the other sorts will work, 
since the first sort is on a hidden field.  Below is the formula and a picture of the final result:

CAST(RANK(SUM("Fact Table"."Field" BY "Dimension Table"."Utility")) AS INTEGER)