Search code examples
mdxparenthierarchy

mdx hierarchy return parent and child in query

I have the following query

WITH 
  MEMBER [Measures].[Period Key] AS 
    Axis(1).Item(1).Item(1).Hierarchy.CurrentMember.UniqueName 
  MEMBER [Measures].[Year Key] AS 
    [Dim Date].[Year].CurrentMember.UniqueName 
SELECT 
  {
    [Measures].[Valuation]
   ,[Measures].[Period Key]
   ,[Measures].[Year Key]
  } ON 0
 ,(
    [Dim Date].[Hierarchy].[Quarter].ALLMEMBERS
   ,[Dim Platform].[Platform Key].ALLMEMBERS
  ) ON 1
FROM [Cube];

I want the Year Key to return the uniquename in the hierarchy instead, as I want to use it in a parameter. In this form it will cause errors as the field definition is expecting the result in the hierarchy form. Is this possible?

Solution

  • Assuming your hierarchy is Year-Semester-Quarter-Month-Date, how about using

    [Dim Date].[Hierarchy].CurrentMember.Parent.Parent.UniqueName

    If you have a Year-Quarter-Month-Date in place, 

    [Dim Date].[Hierarchy].CurrentMember.Parent.UniqueName

    What this is doing is that it's looking at the current Quarter and returning it's corresponding year by going up in the hierarchy tree using the .PARENT function.