MDX Hierarchy Ordering

Problem
User wishes to fetch a measure for the state and zip codes belonging to the states. The state and zip code is part of a Geography Hierarchy that has hierarchy defined as Country -> State-Province -> City -> Postal Code. The  result gets order at the zip code level but does not get ordered at the City Level.

SELECT  {[Geography].[Geography].[City], [Geography].[Geography].[Postal Code]} ON ROWS,  {} ON COLUMNS FROM  [Adventure Works]

The query has 2 problems in the way the result set is displayed

• The City names comes first
• Zip codes comes next
• Order is not maintained

We would like to have the result come in proper order and in a proper Hierarchy.

Solution


One could now use the Hierarchize() function, but the problem is that it sorts members in a level in their natural order. 

SELECT  Hierarchize  (       {[Geography].[Geography].[City], [Geography].[Geography].[Postal Code]}   ) ON ROWS,  {} ON COLUMNS FROM  [Adventure Works]

But the sort order at the City level is not maintained. We would like to have the maintain the order even at the city level. To solve this issue one could use the ORDER() provided by MDX.

WITH       MEMBER [Measures].[OrderKey]       AS       ANCESTOR([Geography].[Geography].currentmember, [Geography].[Geography].[City]).member_name       + "#" +       ANCESTOR([Geography].[Geography].currentmember, [Geography].[Geography].[State-Province]).member_name SELECT       ORDER       (             {[Geography].[Geography].[City], [Geography].[Geography].[Postal Code]},             [Measures].[OrderKey],BASC       ) ON ROWS,       {[Measures].[OrderKey]} ON COLUMNS FROM  [Adventure Works]

As we don’t want the original order of the members created by the hierarchy, we need to use BASC. BASC means ascending order breaking the hierarchy. So instead of using HIERARCHIZE(), we will use ORDER using the OrderKey with BASC.