Ordering Clarity OLAP Page Option Lists

I love Clarity 7 – as someone how has worked with the product since its inception I truly feel this is the version where Clarity matured.  The back-end re-architecture has truly provided amazing performance enhancements.  There are sadly some side-effects to these changes – one of them is the inability to sort Page Option Lists out-of-the-box.

In Clarity 6 we were able to use ORDER (for SSAS clients) or <SORTALTNAMES (for Essbase clients) in the page specification to sort the lists – no more in Clarity 7, where Studio intelligently builds Clarity abstract statements in the .template file based on your selections.

Thankfully Clarity has stuck true to their roots as a consulting company and provided multiple methods to go under-the-hood and by-pass Studio for the definition of specifications.

The solution for SSAS vs. Essbase clients is a little different – but in both cases we will using MDX and the ORDER statement.

Ordering SSAS Member Lists

For SSAS clients the solution is very straight-forward using the Snippet object in the OLAP member list.

Steps:

[raw][list style=”ordered” type=”type1″ ]
Add the Snippet Object to the member list
Add the following MDX to the Snippet:
[/list][/raw]

ORDER( {your Set specification}, {string expression to use for sort}, [{ ASC | DESC | BASC | BDESC } ] )

For the string sort expression you most likely will either be using the member name or alias:

• · Name: [{dimension}].CurrentMember.Name
  E.g. [Accounts].CurrentMember.Name
• · Alias: [{dimension}].CurrentMember.Properties(“{alias property name}”)
  E.g. [Accounts].CurrentMember.Properties(“Alias”)

Ordering Essbase Member Lists

Unfortunately for Essbase clients the solution is more complicated.  For performance reasons Clarity executes Essbase implementation page options not as MDX but using Calculation Script.  Essbase Calculation Script is great for calculations, but it does not have any functionality for sorting – so the order in which members are returned is hierarchy order.  In Clarity 6 this wasn’t a problem as Clarity used Report Script for member list queries.

Thankfully Essbase starting supporting MDX queries in version 7 – the trick now is to get Clarity to use MDX instead of Calculation Script for the page options.  To do this we need abandon Clarity Abstract completely and build our own MDX statement.

Steps:

Whether you have just created a new OLAP Member List or are enhancing an existing one, the first step to using MDX to define the drop-down list members is to remove the <Abstract> node from the XML.

Find the OlapMemberQuery node matching the list to be enhanced and remove all contents between <Abstract> and </Abstract> including the node headers themselves.  When you are done you should only have the <OlapMemberQuery> and the <Source> nodes remaining.

Before:

<OlapMemberQuery Name=”OlapMemberList1″>
<Source Name=”Sample” />
<Abstract>
<Dimensions>
<Dimension Name=”Year” />
</Dimensions>
</Abstract>
</OlapMemberQuery>

After

<OlapMemberQuery Name=”OlapMemberList1″>
<Source Name=”Sample” />
</OlapMemberQuery>

 

At this point you will notice that the Design window will remove the dimension information and grey out the New and Delete options.  This is a good sign as those options are only available when the list uses the Abstract node for the list specification.

After the <Source> node add the <Script Type = “Mdx”> node header into the XML.  Note that Mdx in the Script Type is case sensitive and must be capital M, small d small x.

Your XML should now look as follows:

<OlapMemberQuery Name=”OlapMemberList1″>
<Source Name=”Sample” />
                  <ScriptType=”Mdx”>
                  </Script>
</OlapMemberQuery>

 

After the <Script> node header add a <Text> node into the XML.

Your XML should now look as follows:

<OlapMemberQuery Name=”OlapMemberList1″>

<Source Name=”Projsum2″ />

<Script Type=”Mdx”>

<Text>

</Text>

</Script>

</OlapMemberQuery>

 

We finally now get to the good part – adding the MDX to the report.

Within the Text node add your MDX statement.  Although not always necessary, it is good practice to encapsulate your MDX within a <![CDATA[ Insert MDX here ]]> construct, just in case you use any restricted characters or words.  Please note the MDX rules below; otherwise your MDX might not work as expected or at all.

It is highly recommend that you build and test your MDX within your OLAP tool first as you will receive better feedback / errors and quicker iterations than using Clarity for debugging.

Important MDX Notes:

1. The MDX used in the Script node must be a full MDX statement including SELECT and FROM not just the partial segment that retrieves the members required.
2. The members for the drop-down must be specified for on the COLUMNS axis.  No ROWS axis is required
3. You must include DIMENSION PROPERTIES MEMBER_NAME, MEMBER_ALIAS, GEN_NUMBER on the COLUMNS specification.  This enables the full functionality of the options available in the Page Option dialog with respect to showing Members (Name) or Caption (Alias) as well as the ability to store the selected Name / Alias on the grid regardless of what is displayed in the dialog.

Your final XML should now be something like the following:

<OlapMemberQuery Name=”OlapMemberList1″>
<Source Name=”Projsum2″ />
<Script Type=”Mdx”>
<Text><![CDATA[SELECT ORDER (
DESCENDANTS( [Full Year], 99, LEAVES),
[Year].CurrentMember.[MEMBER_ALIAS],  BASC ) DIMENSION PROPERTIES MEMBER_NAME, MEMBER_ALIAS, GEN_NUMBER ON COLUMNS FROM Sample.Basic]]>
</Text>
</Script>
</OlapMemberQuery>

This MDX script displays the bottom level descendants of the Year dimension alphabetically based on Alias names.  You would use [Year].CurrentMember.[MEMBER_NAME] in the ORDER statement to sort by member name.