MDX Ascendants Function

In Multidimensional Expression, Ascendant function will return the associate Parent member of the specified member at all levels until it reaches Level 0 OR We can simply say, Ascendant Function will return all the ascendants of a specified Member. For instance, you Know the customer name and if you want to find the Post Code, City, State and Country of that particular customer then you can use this Ascendant function.
MDX Ascendants Function Syntax

The basic syntax of the MDX Ascendants Function is:

ASCENDANTS (Member_Expression)

Member_Expression: Any Multidimensional Expression that returns valid Member.


In this article we will show you, How to write Ascendants Function in MDX Query with examples. For this we are going to use below shown hierarchy



MDX Ascendants Function Example


In this example we are going to find the City, State and Country of the Post Code 2055


CODE


SELECT
[Measures].[Reseller Sales Amount] ON COLUMNS,
ASCENDANTS ([Geography].[Geography].[Postal Code].[2055]) ON ROWS
FROM [Adventure Works];


OUTPUT

Using Order Function along with MDX Ascendants Function

In this example we are going to use the MDX Order Function to reorder the members written by the Ascendants function in the Descending Order.

CODE

SELECT [Measures].[Reseller Sales Amount] ON COLUMNS,
ORDER (
ASCENDANTS ([Geography].[Geography].[Postal Code].[2055])
,DESC) ON ROWS
FROM [Adventure Works];


OUTPUT

Using Hierarchize along with MDX Ascendants Function

In this example we are going to use the MDX Hierarchize function to reorder the members written by the Ascendants function from Top to bottom.


CODE


SELECT [Measures].[Reseller Sales Amount] ON COLUMNS,
HIERARCHIZE
(
ASCENDANTS ([Geography].[Geography].[Postal Code].[2055])
) ON ROWS
FROM [Adventure Works];


OUTPUT




NOTE: MDX Hierarchize Function is very useful function to order the members written by the Ascendants rather than MDX Order Function