Practical mDx for Essbase Developers - Developing Essbase Applications - page 211

SELECT {} ON AXIS(0),
{Ancestors([Jan], 1)} ON AXIS(1)
FROM [Sample.Basic];

Axis-1
+-------------------
(Qtr1)
(Jan)

This query will return ancestors that are 0 levels up, essentially returning the query
member. note that while this is not usually practical, it is valid.

SELECT {} ON AXIS(0),
{Ancestors([Jan], 0)} ON AXIS(1)
FROM [Sample.Basic];

6.10.1.5 Querying Level-0 Members The query below returns all level-0 members of the
dimension. note the use of the order() function in this example. typically, order()
leverages a numeric value expression for sorting. Think of a case where you want to sort
members in descending order by sales. In this case, we are looking to sort the member
set alphabetically based on the member name overriding the default hierarchy order.

SELECT {} ON AXIS(0),
ORDER({[Year].levels(0).members}, [Year].CurrentMember.MEMBER_NAME)
ON AXIS(1)
FROM [Sample.Basic];

Axis-1
+-----------------------------
(Apr)
(Aug)
(Dec)
(Feb)
(Jan)
(Jul)
(Jun)
(Mar)
(May)
(Nov)
(Oct)
(Sep)

As mentioned above, sorting by [Sales] would be accomplished by:

SELECT {[Sales]} ON AXIS(0),
ORDER({[Year].levels(0).members}, [Measures].[Sales], BDESC) ON
AXIS(1)
FROM [Sample.Basic];

Axis-1 (Sales)
+-------------------+----------
(Jul) 36134.0000

(Aug) 36008.0000

(Jun) 35088.0000