Submit a request

MDX Training - Basics II

Avatar
Kepion Support
  • Updated

In this tutorial (part II of our MDX training series), we will cover set functions, which return member sets. Set functions are useful to know, as you can use set functions to define your axes. Remember that sets are always defined by either a set of curly braces { }, or a function which returns a set.

 

 

Before you start

Please complete part I of our MDX training series before continuing.

 

 

DESCENDANTS and ASCENDANTS

The DESCENDANTS function grabs every member below the specified member. In this example, it's finding the descendants of the All member on the lowest level. 10,000 is a limit on how far it goes down the tree in its search.

 

DESCENDANTS([Account].[Account].[All], 10000, LEAVES)

 

This is useful when we want to dynamically grab a set of members without specifying each one: 

 

SELECT 
{
 DESCENDANTS([Account].[Account].[Salaries & Benefits], 10000, LEAVES)
} 
ON ROWS,
{
 DESCENDANTS([Department].[Department].[All], 10000, LEAVES)
}
ON COLUMNS
FROM [Workforce-Reporting] 
WHERE
{[Scenario].[Scenario].[Actual]}

 

mceclip0.png

 

There's also a lesser-used ASCENDANTS function, which returns members above the specified Member in the Hierarchy.

 

SELECT
{
ASCENDANTS([Account].[Account].[Salaries & Benefits])
}
ON ROWS,
{
DESCENDANTS([Department].[Department].[All], 10000, LEAVES)
}
ON COLUMNS
FROM [Workforce-Reporting]
WHERE
{[Scenario].[Scenario].[Actual]}

 

mceclip3.png

 

 

HIERARCHIZE

The HIERARCHIZE set function takes in a set and returns the members in the order defined by the hierarchy.

 

SELECT 
{
 DESCENDANTS([Account].[Account].[Salaries & Benefits], 10000, LEAVES)
} 
ON ROWS,
{
 HIERARCHIZE({[Department].[Department].[Sales]
 ,[Department].[Department].[All]
 ,[Department].[Department].[Finance]})
}
ON COLUMNS
FROM [Workforce-Reporting] 
WHERE
{[Scenario].[Scenario].[Actual]}

 

mceclip2.png

 

 

Types of sets

1. An array of members (within the same hierarchy)

{[Department].[Department].[Sales]
,[Department].[Department].[Marketing] 
,[Department].[Department].[Finance]}

 

2. A CROSS JOIN (between one or more sets)

{[Department].[Department].[Sales]}
*{[Account].[Account].[Headcount]}

 

3. An array of sets (where each set has the same set of hierarchies)

{ 
{[Department].[Department].[Sales]}*{[Account].[Account].[Headcount]}
,{[Department].[Department].[Finance]}*{[Account].[Account].[Salaries]}
}

 

Nested sets of curly braces can usually be removed for improved readability, as they denote sets of sets. In this example, these two sets are functionally the same:

 

{{[Department].[Department].[Sales]}}
{[Department].[Department].[Sales]}

 

As are these sets:

 

{DESCENDANTS([Account].[Account].[Salaries & Benefits], 10000, LEAVES)}
DESCENDANTS([Account].[Account].[Salaries & Benefits], 10000, LEAVES)

 

Tip: When working in MDX, you should always simplify any extraneous sets to better understand the structure of the query.

 

 

Next Steps

Complete our MDX training by following MDX Training - Basics III.

Related articles