Another common question that one would get while working with BI EE and Hyperion Essbase is “How to have the default sort order specified in an Essbase Cube to be available in BI EE reports?”. This is a very valid question since by default BI EE sorts the dimension attributes alphabetically. For example look at the screenshot below,
As you see above, BI EE by default has alphabetically sorted the channels report. But if we look at the outline of the channels dimension, the sorting order would be completely different. In most cases, we would like to have the same sort order as the cube (not an alphabetical sort).
In order to overcome this, there are 2 approaches. Both of them would involve the use of EVALUATE function and then passing an MDX function to the cube to fetch the rank of each dimensional attribute. So, lets look at the first approach. In this approach we shall be adding a new column and in the formula enter the below formula
EVALUATE(‘RANK(%1.dimension.currentmember,%2.members)’ AS INTEGER,CHANNELS.”Gen4,CHANNELS”,CHANNELS.”Gen4,CHANNELS”)
What this basically does is, it passes the member of the dimension to the MDX function RANK and retrieves the position of the member in the particular level.
Now hide this new column and apply sorting on it. This will give the default sort order as available in the Essbase Cube.
The problem with the above approach is that the sort order would have to be created for each and every report. In order to overcome that, create another logical column in the repository with the evaluate function and then apply sort order on the 4th level based on this column.
Also, ensure that you have assigned an hierarchy level to the new column.
Thanks to Alan Lee for sharing this. One can extend this to provide lot of different functionalites that BI EE does not offer out of the box.
Posts
