Search code examples
ssasmdxcube

Mdx request get the first element of a tuple with datetime

My MDX request gives to me this result:

enter image description here

For my entities, I've multiple years for one value, I need just get the first year for the entity (and others dimensions). I've tried to use the function .FirstChild in the year dimension this return only value with the year '2014' (first year in my dimension). The function .Item() returns only empty values:

SELECT 
  NON EMPTY 
    {[Measures].[Value]} ON COLUMNS
 ,NON EMPTY 
    {
        [EntiteFederal].[EntiteCode].[EntiteCode].ALLMEMBERS*
        [T].[Year].[Year].ALLMEMBERS*
        [T].[YearDate].[YearDate].ALLMEMBERS
    }
  DIMENSION PROPERTIES 
    MEMBER_CAPTION
   ,MEMBER_UNIQUE_NAME
   ON ROWS
FROM [Mycube];
Solution

  • Hopefully this is working - tricky for me to test:

    WITH 
  SET [EntYr] AS 
    Generate
    (
      [EntiteFederal].[EntiteCode].[EntiteCode].MEMBERS AS X
     ,
        X.CurrentMember
      * 
        Head
        (
          NonEmpty
          (
            [T].[Year].[Year].ALLMEMBERS * [T].[YearDate].[YearDate].ALLMEMBERS
           ,X.CurrentMember
          )
        )
    ) 
SELECT 
  NON EMPTY 
    [Measures].[Value] ON COLUMNS
 ,NON EMPTY 
    [EntYr] ON ROWS
FROM [Mycube];