Order function (MDX)

The Order function sorts the members of a set in ascending or descending order. You can specify whether to preserve or to break the hierarchy of the set.

Returns: A set.

Syntax


>>-Order--(--SetExpression----,--+-NumericExpression-+----+--------------+--)-><
                                 '-StringExpression--'    '-,--+-ASC---+-'      
                                                               +-DESC--+        
                                                               +-BASC--+        
                                                               '-BDESC-'

Parameters

SetExpression

Specifies the set to be sorted.

NumericExpression

Specifies a numeric expression that, when applied to each tuple in the set, gives the value by which the set is to be sorted. This expression is typically a measure. If you omit both this parameter and StringExpression, the function sorts the set based on the current value for each tuple.

StringExpression

Specifies an expression that resolves to a string representation of a number. When applied to each tuple in the set, the expression gives the value by which the set is to be sorted. This expression is typically a measure. If you omit both this parameter and NumericExpression, the function sorts the set based on the current value for each tuple.

Optional flag

Specifies whether the set is sorted in ascending or descending order, and whether the hierarchy is preserved or broken. If you omit the flag parameter, ASC is used by default.

Flag	Order	Hierarchical behavior
ASC	Ascending	Preserves the hierarchy
DESC	Descending	Preserves the hierarchy
BASC	Ascending	Breaks the hierarchy
BDESC	Descending	Breaks the hierarchy

Description

If you specify that the hierarchy is to be preserved, members are sorted with their siblings, but keep their positions relative to their cousins; if instead you specify that the hierarchy is to be broken, members are sorted without regard to their hierarchical positions. For example, suppose you have a set of months, January, February, March, and April; January and March are children of the 2007 member, and February and April are children of the 2008 member. The hierarchy-preserving order of the months is January, March, February, April, whereas the hierarchy-breaking order is January, February, March, April.

Example 1

The following example uses the function to retrieve the sales amount for ValueTrend Store 554 and all ascendants of this store in descending hierarchical order of sales amount.

Query

SELECT {[Price Analysis].[Measures].[Sales Amount]} ON AXIS(0)
, {Order(Ascendants([Price Analysis].[Store].[All Stores].[Subdivision 1A]
.[Region 44].[District 38].[ValueTrend Store 554]), 
[Price Analysis].[Measures].[Sales Amount], DESC)} ON AXIS(1)
FROM [Price Analysis]

Result

Store	Sales Amount
All Stores	2719121.06
Subdivision 1A	1423778.49
Region 44	872423.17
District 38	413491.11
ValueTrend Store 554	184520.49

Example 2

The following example uses the function to retrieve the sales amount for stores in descending hierarchical order.

Query

SELECT {[Price Analysis].[Measures].[Sales Amount]} ON AXIS(0)
, {Order(Descendants([Price Analysis].[Store].[All Stores], -1, LEAVES), 
[Price Analysis].[Measures].[Sales Amount], DESC)} ON AXIS(1)
FROM [Price Analysis]

Result

Store	Sales Amount
ValueTrend Store 1414	267831.59
ValueTrend Store 835	191100.47
ValueTrend Store 782	228970.62
ValueTrend Store 554	184520.49
ValueTrend Store 1199	281038.93
ValueTrend Store 1095	270316.39
ValueTrend Store 681	260621.19
ValueTrend Store 375	257877.78
ValueTrend Store 875	256457.18
ValueTrend Store 116	271528.06
ValueTrend Store 278	248858.36

Example 3

The following example uses the function to retrieve the sales amount for stores in descending nonhierarchical order.

Query

SELECT {[Price Analysis].[Measures].[Sales Amount]} ON AXIS(0)
, {Order(Descendants([Price Analysis].[Store].[All Stores], -1, LEAVES), 
[Price Analysis].[Measures].[Sales Amount], BDESC)} ON AXIS(1)
FROM [Price Analysis]

Result

Store	Sales Amount
ValueTrend Store 1199	281038.93
ValueTrend Store 116	271528.06
ValueTrend Store 1095	270316.39
ValueTrend Store 1414	267831.59
ValueTrend Store 681	260621.19
ValueTrend Store 375	257877.78
ValueTrend Store 875	256457.18
ValueTrend Store 278	248858.36
ValueTrend Store 782	228970.62
ValueTrend Store 835	191100.47
ValueTrend Store 554	184520.49

Feedback | Information roadmap