Jaa

Sum (MDX)

  • Artikkeli

Returns the sum of a numeric expression evaluated over a specified set.

Syntax

Sum( Set_Expression [ , Numeric_Expression ] )

Arguments

  • Set_Expression
    A valid Multidimensional Expressions (MDX) set expression.
  • Numeric_Expression
    A valid numeric expression that is typically a Multidimensional Expressions (MDX) expression of cell coordinates that return a number.

Remarks

If a numeric expression is specified, the specified numeric expression is evaluated across the set and then summed. If a numeric expression is not specified, the specified set is evaluated in the current context of the members of the set and then summed. If the SUM function is applied to a non-numeric expression, the results are undefined.

Note

Analysis Services ignores nulls when calculating the sum of a set of numbers.

Examples

The following example returns the sum of Reseller Sales Amounts for all members of the Product.Category attribute hierarchy for calendar years 2001 and 2002.

WITH MEMBER Measures.x AS SUM
   ( { [Date].[Calendar Year].&[2001]
         , [Date].[Calendar Year].&[2002] }
      , [Measures].[Reseller Sales Amount]
    )
SELECT Measures.x ON 0
,[Product].[Category].Members ON 1
FROM [Adventure Works]

The following example returns the sum of the month-to-date freight costs for Internet sales for the month of July, 2002 through the 20th day of July.

WITH MEMBER Measures.x AS SUM 
   (
      MTD([Date].[Calendar].[Date].[July 20, 2002])
     , [Measures].[Internet Freight Cost]
     )
SELECT Measures.x ON 0
FROM [Adventure Works]

The following example uses the WITH MEMBER keyword and the SUM function to define a calculated member in the Measures dimension that contains the sum of the Reseller Sales Amount measure for the Canada and United States members of the Country attribute hierarchy in the Geography dimension.

WITH MEMBER Measures.NorthAmerica AS SUM 
      (
         {[Geography].[Country].&[Canada]
            , [Geography].[Country].&[United States]}
       ,[Measures].[Reseller Sales Amount]
      )
SELECT {[Measures].[NorthAmerica]} ON 0,
[Product].[Category].members ON 1
FROM [Adventure Works]

See Also

Reference

MDX Function Reference (MDX)

Help and Information

Getting SQL Server 2005 Assistance

Change History

Release History

12 December 2006
Changed content:
  • Clarified impact of nulls.
  • Fixed broken example

17 July 2006
Changed content:
  • Updated syntax and arguments to improve clarity.
  • Added updated examples.