Search code examples
ssasmdxolapmdxstudio

HEAD function inside aggregation function SUM


How do I change this script so that HEAD is not inside the aggregation function SUM?

The reason I'd like to do this is that I'm being warned that:

Function 'Head' was used inside aggregation function - this disables block computation mode

WITH 
  SET [LatestDate] AS 
    [Date].[Calendar].[Date].&[20060611] 
  MEMBER [Measures].[Sales] AS 
    [Measures].[Internet Sales Amount] 
  MEMBER [Measures].[Sales_EquivMTD] AS 
    Sum
    (
      Head
      (
        [Date].[Calendar].CurrentMember.Children
       ,11
      )
     ,[Measures].[Sales]
    ) 
SELECT 
  {
    [Measures].[Sales]
   ,[Measures].[Sales_EquivMTD]
  } ON COLUMNS
 ,NON EMPTY 
    [Date].[Calendar].[Month].MEMBERS ON ROWS
FROM [Adventure Works];

Solution

  • You could replace

    Head
          (
            [Date].[Calendar].CurrentMember.Children
           ,11
          )
    

    with a range expression:

    [Date].[Calendar].CurrentMember.Children.Item(0) : [Date].[Calendar].CurrentMember.Children.Item(10)
    

    But I am not sure if that helps performance wise. And it could cause an error on members having less than 11 children.