ANCESTOR MDX Function

I recently started looking up on MDX Functions and one of the first MDX Function I came across was ANCESTOR. Although, MSDN does provide some very good resource on MDX functions, but it was not so simple for me. Either I am totally new to MDX or I am too lame.

MSDN tries to explain the ANCESTOR MDX function using the Product Hierarchy on the Adventure Works Database but I think Date Hierarchy can be more cool.

So, here we go...

As the name suggests, the Ancestor MDX expression helps you find the Ancestor of any MDX Expression. You can specify the either the no. of levels you want to go back or also an expression specifying a level.

The syntax for Ancestor is as follows:
Ancestor(Member_Expression, Level)
or
Ancestor(Member_Expression, Distance)

Thus, for example, if the Date Hierarchy is as follows:
Year -> Semester -> Quarter -> Month -> Date
The MDX expression results to 04th October 2003
and you want to know the total Internet Sales Amount for the Semester this date belongs to... you will use the following syntax

SELECT {
ANCESTOR(
[Date].[Calendar].[Date].[October 4, 2003], --Member Expression
[Date].[Calendar].[Calendar Semester]) --Level Expression
} ON COLUMNS,
[Measures].[Internet Sales Amount] ON ROWS
FROM [Adventure Works]

Please note as the Member Expression can return sets, this is used with { }.

If you want to know the Internet Sales Amount for the Month, Quarter and Year based on the given date you will use the following syntax

SELECT {
ANCESTOR([Date].[Calendar].[Date].[October 4, 2003], [Date].[Calendar].[Month]),
ANCESTOR([Date].[Calendar].[Date].[October 4, 2003], [Date].[Calendar].[Calendar Quarter]),
ANCESTOR([Date].[Calendar].[Date].[October 4, 2003], [Date].[Calendar].[Calendar Year])
} ON COLUMNS,
[Measures].[Internet Sales Amount] ON ROWS
FROM [Adventure Works]

The syntax produces the following output






You can use the following syntax to use the Distance (Numeric Expression) to get the same output.

SELECT {
ANCESTOR([Date].[Calendar].[Date].[October 4, 2003], 1),
ANCESTOR([Date].[Calendar].[Date].[October 4, 2003], 2),
ANCESTOR([Date].[Calendar].[Date].[October 4, 2003], 4)
} ON COLUMNS,
[Measures].[Internet Sales Amount] ON ROWS
FROM [Adventure Works]

Remeber from Date, Month is 1 Level up, Quarter is 2 Levels up and Year is 4 Levels up..

Please note the Ancestor Function always returns a single value..
Interestingly, there is also an MDX Function ANCESTORS but I could not find the difference between two. Even the Ancestors return a single value..

The MSDN article on ANCESTOR can be access here...