ORDER (MDX)
Returned Type
This function returns a set.
Syntax and Details
ORDER(set_expression, ordering_expression, optional_keyword)
Where:
-
set_expression is an expression that evaluates to a set, typically a set of members.
-
ordering_expression is a numeric expression or a string expression that determines the order of the set elements.
For numeric values, typically ordering_expression is [MEASURES].[measure_name]
The function evaluates this expression for each element of the set and sorts the elements of the set according to this value.
-
optional_keyword controls how MDX handles any hierarchies in the set. Use one of the following keywords:
-
ASC — Use this to sort in ascending order (using the value returned by ordering_expression), while preserving the hierarchy. For information on hierarchical order, see HIERARCHIZE.
If you omit the keyword, the function sorts in this way.
-
DESC — Use this to sort in descending order (using the value returned by ordering_expression), while preserving the hierarchy.
-
BASC — Use this to break the hierarchy and sort all members in ascending order (using the value returned by ordering_expression).
-
BDESC — Use this to break the hierarchy and sort all members in descending order (using the value returned by ordering_expression).
-
Example
For example, the following query sorts cities in descending order by average test score, respecting the ZIP codes to which the cities belong:
SELECT MEASURES.[avg test score] ON 0,
ORDER(homed.city.MEMBERS, MEASURES.[avg test score], DESC) ON 1 FROM patients
Avg Test Score
1 Pine 75.67
2 Magnolia 74.65
3 Cypress 74.61
4 Centerville 74.85
5 Cedar Falls 74.62
6 Elm Heights 74.36
7 Juniper 74.52
8 Spruce 74.14
9 Redwood 74.16
To see a picture of the hierarchy used in this example, see the FIRSTCHILD function.
In contrast, the following example uses the BDESC keyword and disregards the hierarchy:
SELECT MEASURES.[avg test score] ON 0,
ORDER(homed.city.MEMBERS, MEASURES.[avg test score], BDESC) ON 1 FROM patients
Avg Test Score
1 Pine 75.67
2 Centerville 74.85
3 Magnolia 74.65
4 Cedar Falls 74.62
5 Cypress 74.61
6 Juniper 74.52
7 Elm Heights 74.36
8 Redwood 74.16
9 Spruce 74.14
For another example, the following query defines a string measure (as a calculated member) and then uses it with the ORDER function:
WITH MEMBER measures.stringtest AS 'IIF(MEASURES.[avg test score]<75, "low","high")'
SELECT {MEASURES.[avg test score],MEASURES.stringtest} on 0,
ORDER(homed.city.MEMBERS,measures.stringtest,BASC) ON 1 FROM patients
Avg Test Score stringtest
1 Pine 75.67 high
2 Cedar Falls 74.62 low
3 Centerville 74.85 low
4 Cypress 74.61 low
5 Elm Heights 74.36 low
6 Juniper 74.52 low
7 Magnolia 74.65 low
8 Redwood 74.16 low
9 Spruce 74.14 low
