HEAD function inside aggregation function SUM

135 Views Asked by At

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];
1

There are 1 best solutions below

2
On BEST ANSWER

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.