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];

Wednesday, April 29, 2009

MDX for searching dimensions members

I wrote a simple MDX to list out the Employees whose name contains 'RA' and get the corresponding Parent Key as well.
*************************************************************************
Select
Generate({Filter({[Employee].[Employees].members},Instr([Employee].[Employees].currentmember.Name,"RA")>0)} ,Ascendants( [Employee].[Employees].currentmember))
DIMENSION PROPERTIES [Employee].[PARENT_UNIQUE_NAME] on rows,
{} on columns
From [Adventure Works]
*************************************************************************