Order (MDX)
Orders members in a set optionally keeping their natural order (i.e., hierarchical order).
Parameters
Name | Type | Cardinality | Default value |
set | set-expression | one | |
number | numeric-expression | zero-one | |
option | ASC|DESC|BASC|BDESC | zero-one | ASC |
Return
set
Description
The function performs a rearrangement of the specified set. It is possible to break the hierarchy ordering during members arrangement using additional flag option.
1) If BASC or BDESC is specified: members are ordered regardless of their natural position. Parent/child relations are broken.
2) If ASC or DESC flag is specified: only sibling members (children of the same parent) are ordered. So, parent/child relations are kept in the resulting set.
The ASC flag is the default option.
Examples
Returns the members in ascending order using the numeric expression.
Note how member original order is kept for members with the same value
Query
WITH MEMBER [Geography].[Geo].[First] AS 1.0 MEMBER [Geography].[Geo].[Second] AS 2.0 MEMBER [Geography].[Geo].[Third] AS 2.0 SELECT Order({[Geography].[Geo].[Second], [Geography].[Geo].[First], [Geography].[Geo].[Third]}, [Measures].[Amount], BASC) on 0 FROM [Sales]
Result
First | Second | Third |
1.0 | 2.0 | 2.0 |
Assertion : MDX Equals
WITH MEMBER [Geography].[Geo].[First] AS 1.0 MEMBER [Geography].[Geo].[Second] AS 2.0 MEMBER [Geography].[Geo].[Third] AS 2.0 SELECT {[Geography].[Geo].[First], [Geography].[Geo].[Second],[Geography].[Geo].[Third]} on 0 FROM [Sales]
Returns the members in descending order using the numeric expression.
Note how member original order is kept for members with the same value
Query
WITH MEMBER [Geography].[Geo].[First] AS 1.0 MEMBER [Geography].[Geo].[Second] AS 2.0 MEMBER [Geography].[Geo].[Third] AS 2.0 SELECT Order({[Geography].[Geo].[Second],[Geography].[Geo].[First], [Geography].[Geo].[Third]}, [Measures].[Amount], BDESC) on 0 FROM [Sales]
Result
Second | Third | First |
2.0 | 2.0 | 1.0 |
Assertion : MDX Equals
WITH MEMBER [Geography].[Geo].[First] AS 1.0 MEMBER [Geography].[Geo].[Second] AS 2.0 MEMBER [Geography].[Geo].[Third] AS 2.0 SELECT {[Geography].[Geo].[Second], [Geography].[Geo].[Third],[Geography].[Geo].[First]} on 0 FROM [Sales]
Set may be ordered according to string expressions instead of numerical ones.
Query
WITH MEMBER [Geography].[Geo].[First] AS 4.0 MEMBER [Geography].[Geo].[Second] AS 1.0 MEMBER [Geography].[Geo].[Third] AS 3.0 SELECT Order({[Geography].[Geo].[Second],[Geography].[Geo].[First], [Geography].[Geo].[Third]}, [Geography].[Geo].currentMember.name, BASC) on 0 FROM [Sales]
Result
First | Second | Third |
4.0 | 1.0 | 3.0 |
Assertion : MDX Equals
WITH MEMBER [Geography].[Geo].[First] AS 4.0 MEMBER [Geography].[Geo].[Second] AS 1.0 MEMBER [Geography].[Geo].[Third] AS 3.0 SELECT {[Geography].[Geo].[First], [Geography].[Geo].[Second],[Geography].[Geo].[Third]} on 0 FROM [Sales]
Returns the members using their natural order (hierarchize) where siblings are sorted using the ASC/DESC flag.
In our example, we're sorting by name. First we sort the cities by country (we would be sorting by continent if they had different ones); afterwards we sort the cities belonging to the same parent by name. That is the reason we got first all cities of France.
Check how the same behaviour may be produced using the Generate() function in the test.
Query
WITH SET [Europe-Cities] AS Descendants( [Geography].[Geo].[Europe], [Geography].[Geo].[City] ) SELECT Order( [Europe-Cities] , [Geography].[Geo].currentMember.name, ASC) on 0 FROM [Sales]
Result
Paris | Barcelona | Madrid | Valencia | Geneva | Lausanne | Zurich |
4 | 2 | 1 | 128 | 56 | 64 |
Assertion : MDX Equals
WITH SET [Ordered European cities] AS Order( [Geography].[Geo].[Europe].children, [Geography].[Geo].currentMember.name, ASC) SELECT Generate( [Ordered European cities], Order( [Geography].[Geo].currentMember.children, [Geography].[Geo].currentMember.name, ASC ) ) on 0 FROM [Sales]
Order using ASC on a set without siblings gives the same result as calling the Hierarchize() function.
Query
WITH SET [Broken Hierarchy] AS {[Geography].[Geo].[Lausanne],[Geography].[Geo].[Mexico],[Geography].[Geo].[Continent].[America]} SELECT --ORDER( [Broken Hierarchy], [Broken Hierarchy].currentMember.NAME, ASC ) on 0 ORDER( [Broken Hierarchy], [Geography].[Geo].currentMember.NAME, ASC ) on 0 FROM [sales]
Result
America | Mexico | Lausanne |
768 | 56 |
Assertion : MDX Equals
WITH SET [Broken Hierarchy] AS {[Geography].[Geo].[Lausanne],[Geography].[Geo].[Mexico],[Geography].[Geo].[Continent].[America]} SELECT Hierarchize( [Broken Hierarchy] ) on 0 FROM [sales]
Returns the set hierarchized with the siblings ordered using the numeric expression.
In our example, sorting the countries is the same as sorting the whole hierarchy and filtering the countries from the result.
Query
SELECT Order( [Geography].[Geo].[Country].members, [Measures].[Amount], DESC) on 0 FROM [Sales]
Result
United States | Canada | Mexico | Venezuela | Switzerland | France | Spain |
768 | 248 | 4 | 3 |
Assertion : MDX Equals
WITH SET [AllMembers] AS Order( [Geography].[Geo].members, [Measures].[Amount], DESC) SELECT Filter( [AllMembers] as s1, s1.currentMember.level IS [Geography].[Geo].[Country]) on 0 FROM [Sales]
Tuples set are ordered in a different way. Only the last hierarchy of the tuples is ordered using the flag. The members of the other hierarchies are ordered using their rank (natural order).
In our example, [Licence] members are first ordered using their rank, afterwards [Geo] members are ordered using the flag. Check the test query to see a working example of the decomposition of this function.
Query
WITH SET [Set1] as Order( [Product].[Prod].[Licence].members, [Product].[Prod].currentMember.name, ASC) SELECT [Measures].Amount on 0, Order( [Set1] * [Geography].[Geo].[City].members, [Measures].[Amount], DESC) on 1 FROM [Sales]
Result
Amount | ||
Corporate | New York | 768 |
Corporate | Los Angeles | |
Corporate | San Francisco | |
Corporate | Quebec | |
Corporate | Toronto | |
Corporate | Mexico | |
Corporate | Caracas | |
Corporate | Geneva | 128 |
Corporate | Lausanne | 16 |
Corporate | Zurich | |
Corporate | Paris | |
Corporate | Barcelona | |
Corporate | Madrid | |
Corporate | Valencia | |
Partnership | Zurich | 64 |
Partnership | Lausanne | 32 |
Partnership | Geneva | |
Partnership | Paris | |
Partnership | Barcelona | |
Partnership | Madrid | |
Partnership | Valencia | |
Partnership | Quebec | |
Partnership | Toronto | |
Partnership | Mexico | |
Partnership | Los Angeles | |
Partnership | New York | |
Partnership | San Francisco | |
Partnership | Caracas | |
Personal | Lausanne | 8 |
Personal | Geneva | |
Personal | Zurich | |
Personal | Paris | 4 |
Personal | Barcelona | 2 |
Personal | Madrid | 1 |
Personal | Valencia | |
Personal | Quebec | |
Personal | Toronto | |
Personal | Mexico | |
Personal | Los Angeles | |
Personal | New York | |
Personal | San Francisco | |
Personal | Caracas | |
Startup | Quebec | |
Startup | Toronto | |
Startup | Mexico | |
Startup | Los Angeles | |
Startup | New York | |
Startup | San Francisco | |
Startup | Caracas | |
Startup | Paris | |
Startup | Barcelona | |
Startup | Madrid | |
Startup | Valencia | |
Startup | Geneva | |
Startup | Lausanne | |
Startup | Zurich |
Assertion : MDX Equals
WITH SET [AllMembers] AS Hierarchize([Product].[Prod].[Licence].members) SELECT [Measures].Amount on 0, Generate( [AllMembers] as s1, Order( s1.currentMember * [Geography].[Geo].[City].members, [Measures].[Amount], DESC) ) on 1 FROM [Sales]
Query
WITH SET [AllMembers] AS Order({ [Product].[Prod].[Licence].&[Corporate], [Product].[Prod].[Licence].&[Partnership], [Product].[Prod].[Licence].&[Startup], [Product].[Prod].[Licence].&[Startup], [Product].[Prod].[Licence].&[Personal] } , [Measures].[Amount], BDESC ) SELECT [Measures].Amount on 0, [AllMembers] on 1 FROM [Sales] WHERE [Geography].[Geo].[Continent].[America]
Result
Amount | |
Corporate | 768 |
Partnership | |
Startup | |
Startup | |
Personal |
Assertion : MDX Equals
WITH SET [AllMembers] AS { [Product].[Prod].[Licence].&[Corporate], [Product].[Prod].[Licence].&[Partnership], [Product].[Prod].[Licence].&[Startup], [Product].[Prod].[Licence].&[Startup], [Product].[Prod].[Licence].&[Personal] } SELECT [Measures].Amount on 0, [AllMembers] on 1 FROM [Sales] WHERE [Geography].[Geo].[Continent].[America]
Query
WITH SET [AllMembers] AS Order({ [Product].[Prod].[Licence].&[Corporate], [Product].[Prod].[Licence].&[Partnership], [Product].[Prod].[Licence].&[Startup], [Product].[Prod].[Licence].&[Startup], [Product].[Prod].[Licence].&[Personal] } , [Measures].[Amount], DESC ) SELECT [Measures].Amount on 0, [AllMembers] on 1 FROM [Sales] WHERE [Geography].[Geo].[Continent].[America]
Result
Amount | |
Corporate | 768 |
Partnership | |
Personal | |
Startup | |
Startup |
Assertion : MDX Equals
WITH SET [AllMembers] AS { [Product].[Prod].[Licence].&[Corporate], [Product].[Prod].[Licence].&[Partnership], [Product].[Prod].[Licence].&[Personal], [Product].[Prod].[Licence].&[Startup], [Product].[Prod].[Licence].&[Startup] } SELECT [Measures].Amount on 0, [AllMembers] on 1 FROM [Sales] WHERE [Geography].[Geo].[Continent].[America]
Returns the set passed as parameter as this function currently does nothing.
Query
UnOrder( [Geography].[Geo].[Continent].Members )
Result
{ [Geography].[Geo].[Continent].[America], [Geography].[Geo].[Continent].[Europe] }
Assertion : MDX Equals
[Geography].[Geo].[Continent].Members
XMLA Properties
Find here the value of the XMLA properties when not specified otherwise.