ORDER
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