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];
Thursday, April 30, 2009
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]
*************************************************************************
*************************************************************************
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]
*************************************************************************
Subscribe to:
Posts (Atom)