MDX Functions: Hierarchy Functions
In This Article
Overview
Hierarchy functions in Multidimensional Expressions (MDX) are used to navigate and manipulate the hierarchical structures within your Analysis Services cubes. These functions are essential for tasks such as retrieving parent or child members, identifying levels within a hierarchy, and working with different perspectives of hierarchical data.
Understanding hierarchies is fundamental to effectively querying multidimensional data. They represent the natural relationships between data elements, such as Year > Quarter > Month, or Country > State > City.
Hierarchy Functions
The following are some of the most commonly used hierarchy functions in MDX:
Parent(Member_Expression)
Returns the parent member of the specified member. If the specified member is at the top level of the hierarchy, this function returns an empty set.
Syntax
Parent(Member_Expression)Arguments
- Member_Expression: A valid Multidimensional Expressions (MDX) expression that returns a member.
Children(Member_Expression)
Returns a set containing the direct children of the specified member. If the specified member has no children, an empty set is returned.
Syntax
Children(Member_Expression)Arguments
- Member_Expression: A valid Multidimensional Expressions (MDX) expression that returns a member.
Level(Member_Expression)
Returns the level of the specified member. A level represents a specific depth within a hierarchy.
Syntax
Level(Member_Expression)Arguments
- Member_Expression: A valid Multidimensional Expressions (MDX) expression that returns a member.
Ancestors(Member_Expression, Level_Expression)
Returns a set of all ancestors of a given member up to and including the specified level. This includes the member itself if the level specified is the member's own level.
Syntax
Ancestors(Member_Expression, Level_Expression)Arguments
- Member_Expression: A valid MDX expression that returns a member.
- Level_Expression: A valid MDX expression that returns a level.
Descendants(Member_Expression, Level_Expression [, Member_Set_Relationship])
Returns a set of all descendants of a given member up to and including the specified level. The optional Member_Set_Relationship argument specifies how to relate the descendants to the original member.
Syntax
Descendants(Member_Expression, Level_Expression [, Member_Set_Relationship])Arguments
- Member_Expression: A valid MDX expression that returns a member.
- Level_Expression: A valid MDX expression that returns a level.
- Member_Set_Relationship: An optional parameter that can be SELF, AFTER, BEFORE, TUPLE, or ALL. Defaults to CHILDREN.
Siblings(Member_Expression)
Returns a set containing all members at the same level as the specified member, excluding the member itself. If the member is at the top level, it returns an empty set.
Syntax
Siblings(Member_Expression)Arguments
- Member_Expression: A valid MDX expression that returns a member.
Examples
Let's assume we have a 'Date' dimension with a hierarchy 'Calendar' that includes levels like 'Year', 'Quarter', 'Month', and 'Day'.
Get direct children of a Year
To get all quarters within the year 2023:
SELECT
Children([Date].[Calendar].[Year].&[2023]) ON COLUMNS
FROM [YourCube]Get the parent of a Month
To find the quarter to which 'January 2023' belongs:
SELECT
Parent([Date].[Calendar].[Month].&[202301]) ON COLUMNS
FROM [YourCube]List all months in a specific year
Using Descendants to get all months under the year 2023:
SELECT
Descendants([Date].[Calendar].[Year].&[2023], [Date].[Calendar].[Month]) ON COLUMNS
FROM [YourCube]Get all members up to the 'Quarter' level for a specific month
Using Ancestors to find the path from a month to the top level:
SELECT
Ancestors([Date].[Calendar].[Month].&[202301], [Date].[Calendar].[Year]) ON COLUMNS
FROM [YourCube]Get members at the same level (siblings)
To get other months within the same quarter as 'January 2023' (excluding January itself):
SELECT
Siblings([Date].[Calendar].[Month].&[202301]) ON COLUMNS
FROM [YourCube]