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)