Ascendants Function in MDX

Ascendants Function
Ascendants Function


Lets discuss about Ascendants Function here. This function is very close to Ancestor and Ancestors functions but the major differences are both Ancestor and Ancestors returns the items at the specified level where as Ascendants returns items from all the levels including the member itself. PFB the syntax of the same –


You can see from the above syntax that there is no SECOND parameter like in Ancestor/Ancestors as it returns Ancestors of all the levels. Lets say we have a TIME Hierarchy as shown below.

Year –> Semester –> Quarter –> Month.

If you use Ascendants function to a member at Month level then the output will contain –

Month Member -> Month’s Parent(Quarter) -> Quarter’s Parent(Semester) -> Semester’s Parent(Year)

This is why we  doesn’t need SECOND parameter to specify LEVEL like in Ancestor/Ancestors function. Ascendants returns the set of the ascendants of a specified member, including the member itself. So,The Ascendants function returns all of the ancestors of a member from the member itself up to the top of the member’s hierarchy; more specifically, it performs a post-order traversal of the hierarchy for the specified member, and then returns all ascendant members related to the member, including itself, in a set. This is in contrast to the Ancestor function, which returns a specific ascendant member, or ancestor, at a specific level.

PFB sample query using Ascendants Function –

select [Measures].[Sales Amount] on Columns,
Ascendants([Dim Time].[Hierarchy].[Calendar Year].&[2002].&[3].&[September]) on Rows
From [Adventure Works]

In the above query I am asking for Ascendants of “[Dim Time].[Hierarchy].[Calendar Year].&[2002].&[3].&[September]” member which belongs to MONTH level of Hierarchy and the result set looks like in the below given screenshot –

Ascendants Function
Ascendants Function

Tip – So, When you want Ancestors at a Specified level then  go with Ancestor where as if your requirement is to pull complete Family of a Member then use Ascendants function.

That’s it .. Happy Coding !!


Roopesh Babu V


  1. Customs clearance usually takes up to 1 3 days, but can take up to a week depending on the quantity of international post entering the U real cialis online If your pharmacy is unable or unwilling to process your discount, please have the pharmacist call 1-877-422-7718

  2. Puoi comprare Cialis online sicuro in compresse da 2,5, 5, 10 e 20 mg, ma Viagra in 25, 50 e 100 mg generic priligy online If you have more questions about the medications used to treat erectile dysfunction or you need medical advice, speak with your healthcare provider

  3. Reactive oxygen species are known to inhibit the production of sulfhydryl antioxidants, inhibit enzyme reactions, damage nuclei, and initiate lipid peroxidation in cell membranes required to give the plasma membrane the fluidity essential for sperm motility Sanocka Kurpisz, 2004. clomid oral tablet 50 mg order online I still have a pretty good libido, ok energy, a little depressed, foggy, and soft erections.


Please enter your comment!
Please enter your name here

74 − = 65