| Order | (standard 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]
XMLA Properties
Find here the value of the XMLA properties when not specified otherwise.