The following are various examples/techniques of MDX queries. The list is geared toward MDX queries that return member data (such as for use in a Dodeca MDX selector). Unless otherwise noted, the classic Sample/Basic database is used. You may need to append FROM [Sample].[Basic] or similar in your MDX query editor to make these work (although when a connection is set, this is unnecessary in Dodeca).

If you have an example you’d like to share (especially if it works with Sample/Basic), please send it my way and I’ll be happy to include it on the list.

Return Level 0 Members From Dimension

This is one of the simplest examples where we just want the level 0 (leaf) members (nodes) from a given dimension.

SELECT
{} ON 0,
{[Product].Levels(0).Members} ON 1

Sample Output

Cola
Diet Cola
Caffeine Free Cola
Old Fashioned
Diet Root Beer
Sasparilla
Birch Beer
Dark Cream
Vanilla Cream
Diet Cream
Grape
Orange
Strawberry
Diet Cola
Diet Root Beer
Diet Cream

Return Level 1 Members From Dimension

We might instead want members from level 1, which is just a simple tweak to specify that level instead.

SELECT
{} ON 0,
{[Product].Levels(1).Members} ON 1

Sample Output

Colas
Root Beer
Cream Soda
Fruit Soda
Diet Drinks

Return Members From Dimension with UDA

We can return the members with a given UDA (text property).

SELECT
{} ON 0,
{Uda([Market], "Major Market")} ON 1

Sample Output

East
New York
Massachusetts
Florida
California
Texas
Central
Illinois
Ohio
Colorado

Return Members at Level 0 with UDA

We can use the Intersect function to get members that are both at a certain level and have a UDA.

SELECT
{} ON 0,
{Intersect(Uda([Market], "Major Market"), [Market].Levels(0).Members)} ON 1

Sample Output

New York
Massachusetts
Florida
California
Texas
Illinois
Ohio
Colorado

Return Members at Level 1 with UDA

Similarly to the above level 1 example, we may want the members at a different level that also have a given UDA (UDAs can be used on members at any level).

SELECT
{} ON 0,
{Intersect(Uda([Market], "Major Market"), [Market].Levels(1).Members)} ON 1

Sample Output

East
Central

Return Members at Level 0 with UDA, Sorted on Member Name

We can sort the members that come back in the query. Our front-end tool might do this for us, but there’s no real harm in doing it here, plus we actually have some additional flexibility.

SELECT
{} ON 0,
{Order(
Intersect(Uda([Market], "Major Market"), [Market].Levels(0).Members),
[Market].CurrentMember.MEMBER_NAME
)} ON 1

Sample Output

California
Colorado
Florida
Illinois
Massachusetts
New York
Ohio
Texas

Return Members at Level 0 with UDA, Reverse Sorted

The implied sorting order if none is specified is alphabetical, but we might also want the reverse of that (note use of BDESC).

SELECT
{} ON 0,
{Order(
Intersect(Uda([Market], "Major Market"), [Market].Levels(0).Members),
[Market].CurrentMember.MEMBER_NAME, BDESC
)} ON 1

Sample Output

Texas
Ohio
New York
Massachusetts
Illinois
Florida
Colorado
California

Return Members at Level 0, Sorted on Member Name (Contains Duplicates)

Due to the way that shared members are processed, we might get back duplicate members that we didn’t want. The DISTINCT function doesn’t work quite as usefully as we would want it to (ostensibly due to the way that Essbase classifies shared/duplicate members).

SELECT
{} ON 0,
{Order(
[Product].Levels(0).Members,
[Product].CurrentMember.MEMBER_NAME
)} ON 1

Sample Output

Cola
Diet Cola
Diet Cola
Caffeine Free Cola
Old Fashioned
Diet Root Beer
Diet Root Beer
Sasparilla
Birch Beer
Dark Cream
Vanilla Cream
Diet Cream
Diet Cream
Grape
Orange
Strawberry

Return Members at Level 0, Sorted on Member Alias

We can sort on member aliases if we want to. In the previous example with markets (state names), the member name and alias are effectively the same, so it didn’t matter. However, it may be the case that we have, say, numeric or otherwise terse member names but want to sort on the alias (or the “nice name” if you will). We can specify the alias as the sort key.

SELECT
{} ON 0,
{Order(
[Product].Levels(0).Members,
[Product].CurrentMember.MEMBER_ALIAS
)} ON 1

Sample Output

Birch Beer
Caffeine Free Cola
Cola
Dark Cream
Diet Cola
Diet Cola
Diet Cream
Diet Cream
Diet Root Beer
Diet Root Beer
Grape
Old Fashioned
Orange
Sasparilla
Strawberry
Vanilla Cream

Return Members at Level 0, Sorted on Member Alias, Suppressing Duplicates

As mentioned above, the distinct keyword doesn’t quite seem to filter duplicates the way we might expect or need. As a bit of a workaround, we can filter based on the member unique name property to remove entries that start with a bracket. Adapted from Joe Watkins’ MDX example on the Network54 Essbase Forum. Note also that on the same post as Joe’s link there is another example that works in a completely different way, using a dynamic member. It’s a bit more code and complicated, but provides an alternate approach (courtesy of Gary Crisci).

SELECT
{} ON 0,
{Filter(
Order(
[Product].Levels(0).Members,
[Product].CurrentMember.MEMBER_ALIAS
),
LEFT([Product].CurrentMember.MEMBER_UNIQUE_NAME,1) <> "["
)} ON 1

Sample Output

Birch Beer
Caffeine Free Cola
Cola
Dark Cream
Diet Cola
Diet Cream
Diet Root Beer
Grape
Old Fashioned
Orange
Sasparilla
Strawberry
Vanilla Cream