MDX Order Function

Order MDX function is used to show the result set in specified order. Generally we set OrderBy property of key attribute as Key. Sometime we want to define the customized order wherein we can create another attribute on different column which store Order like 1,2,3.....and then we can set OrderBy property of KeyAttribute as "AttributeKey" and specify the attribute name (which stores customized ordering) under "OrderByAttribute" property.

Consider an example wherein you have set the OrderBy property of KeyAttribute as "Key" and while displaying result set, you want to show the members in different orders....in such cases you can use Order MDX function. Consider following example build using AdventureWorks sample;

Open AdventureWorks sample and execute following MDX query

SELECT 
{[Measures].[Internet Sales Amount]} 
ON COLUMNS,
DESCENDANTS([Product].[Product Categories],[Product].[Product Categories].[Category],SELF) 
ON ROWS
FROM [Adventure Works]

After execution, you will get following result set;

Above result set is showing Ordering by Name because OrderBy property of Category attribute is set as Name.If you want to show ordering Descending by Name while showing MDX results then you can use Order function in the following way;

SELECT 
{[Measures].[Internet Sales Amount]} 
ON COLUMNS,
Order
  (
DESCENDANTS([Product].[Product Categories],[Product].[Product Categories].[Category],SELF) ,
[Product].[Product Categories].CURRENTMEMBER.MEMBER_NAME,
DESC
  )
ON ROWS
FROM [Adventure Works];

After executing above MDX, you will get following result set Ordered by Name DESC

Now consider you want to show the results as per Key Order, in such case you can modify your MDX in following way;

SELECT 
{[Measures].[Internet Sales Amount]} 
ON COLUMNS,
Order
   (
DESCENDANTS([Product].[Product Categories],[Product].[Product Categories].[Category],SELF) ,
[Product].[Product Categories].CURRENTMEMBER.MEMBER_KEY,
ASC
)
ON ROWS
FROM [Adventure Works];

After executing above MDX, you will get following result set Ordered by KEY ASC

If you specify DESC in above MDX, you will get result set Ordered by KEY DESC