Getting the level x value of the currentmember in a parent-child hierarchy in MDX

Question

I have an employee parent-child hierarchy in a dimension called Employees which shows the managerial structure of an organisation. This hierarchy is [Employees].[Managerial]. There is another hierarchy that lists all the employees for an organisation. This is a single level hierarchy and it is [Employess].[All Employees].

I have a query that looks something like this:

With 
Member measures.[FullTimeSalary] as measures.[Salary] * measures.[FullTimeFactor] 
Select {measures.[FullTimeSalary]} on 0,
Non empty
{
[Employess].[All Employees].[All].Children 
}
On 1
From MyCube
Where ([Time].[Month].&[201501])

Now if I expand the parent-child hierarchy (the [Employees].[Managerial] hierarchy) I can see each of the different levels of this structure( [Level 02], [Level 03], [Level 04], ect) and what I need to do now is create a new calculated measure called measures.[SupervisingManager] that brings back the currentmembers value at [Level 03] of the hierarchy.

I've tried

member measures.[SupervisingManager] as [Employees].[Managerial].[Level 03].currentmember.member_name

but that just returns "#Error" and using

member measures.[SupervisingManager] as [Employees].[Managerial].currentmember.member_name

returns that currentmember. I also experimented with

measures.[SupervisingManager] as [Employees].[Managerial].currentmember.parent.member_name

but the issue with this is that the currentmember can be located at any within the hierarchy. The only way I can think of doing this is to do a massive case statement, get the ordinal value of the current member and use the appropriate .parent.parent logic. Is there a neater way to do this?

whytheq whytheq · Accepted Answer · 2015-05-20T10:35:06

Maybe something along these lines will help:

WITH 
  MEMBER measures.[FullTimeSalary] AS 
    measures.[Salary] * measures.[FullTimeFactor] 
  MEMBER measures.[SupervisingManager] AS 
    IIF
    (
      [Employees].CurrentMember.Parent.Level.Name = 'Level 03'
     ,[Employees].CurrentMember.Parent.Member_Caption
     ,'n/a'
    ) 
SELECT 
  {
    measures.[FullTimeSalary]
   ,measures.[SupervisingManager]
  } ON 0
 ,NON EMPTY 
    {[Employess].[All Employees].[All].Children} ON 1
FROM MyCube
WHERE 
  [Time].[Month].&[201501];

Getting the level x value of the currentmember in a parent-child hierarchy in MDX

1 Answers