Thursday, April 30, 2009

MDX to get the Lineage and All ancestors

Following is the MDX written to get the Lineage (Parents path in terms of key separated by slash) and parent path with name of all members in the dimension.

WITH
MEMBER [Measures].[AncestorNames] AS
Generate
(
Ascendants([Employees].CurrentMember)
,[Employees].CurrentMember.Name
,"->"
)
MEMBER [Measures].[Lineage] AS
Generate
(
Ascendants([Employees].CurrentMember)
,[Employees].CurrentMember.Properties("key")
,"/"
)
SELECT
{
[Measures].[AncestorNames]
,[Measures].[Lineage]
} ON COLUMNS
,{[Employee].[Employees].MEMBERS} ON ROWS
FROM [Adventure Works];

No comments: