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.
.gif) 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]