Friends,
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 –
Ascendants(Member_Expression)
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 –
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 !!
Regards,
Roopesh Babu V
