MDX Employee Ancestors

MDX to get the employee and managers above him. User can restrict to what level above him the manager details need to be fetched. Here we are getting immediate manager and his manager.

WITH       MEMBER [EmployeeLevel] AS [Employee].[Employees].CURRENTMEMBER.LEVEL_NUMBER      SELECT       FILTER(             ORDER(                   NONEMPTY(                         ASCENDANTS([Employee].[Employees].&[291])                         ,[Measures].[Reseller Order Count]                   ),                   EmployeeLevel,BDESC             ),             EmployeeLevel<>0 and EmployeeLevel>=2       )       ON 0,       {             [EmployeeLevel],             [Measures].[Reseller Order Count]                  } ON 1 FROM [Adventure Works]

Using the descendant function

WITH MEMBER Manager as [Employee].[Employees].currentmember.parent.name MEMBER Director as [Employee].[Employees].currentmember.parent.parent.name SELECT NON EMPTY {       [Measures].[Reseller Order Count],       measures.manager,       measures.director } ON COLUMNS, NON EMPTY filter (       {             (    DESCENDANTS([Employee].[Employees].[Employee Level 04].ALLMEMBERS,,leaves)             )       },        [Measures].[Reseller Order Count] >0 ) ON ROWS FROM ( SELECT [Employee].[Employees].&[291] ON COLUMNS FROM [Adventure Works])