Sum

Syntax

Sum ( set [,numeric_value_expression ] )

Parameters

set: The set containing the tuples to aggregate. If empty, the return value is #MISSING.
numeric_value_expression: Optional. An expression that returns a value. Commonly used to restrict the aggregation to a slice from a Measures dimension (see MDX Grammar Rules). In the example below, [Measures].[Total Expenses] is the numeric value expression provided to the Sum function.

Notes

For optimized performance of this function on aggregate storage databases, include in your query the following kinds of sets:

Any of the following functions, used within the named set and/or as an argument to this function: Intersect, CurrentMember, Distinct, CrossJoin, PeriodsToDate.
The Filter function, with the search condition defined as: dimensionName.CurrentMember IS memberName.
The IIF function, with the true_part and false_part being sets that meet the above criteria.
The use of any other functions (such as Members) disables the optimization.
The second parameter, numeric_value_expression, must be included for optimal performance.

Optimal query performance may require a larger formula cache size. If you get an error message similar to the following, adjust the MAXFORMULACACHESIZE configuration setting accordingly:

Not enough memory for formula execution. Set MAXFORMULACACHESIZE configuration parameter to [1072]KB and try again.

For each tuple in set, the numeric value expression is evaluated in the context of that tuple and the resulting values are summed up.

The return value of Sum is #MISSING if either of the following is true:

The input set is empty.
All tuple evaluations result in #MISSING values.

Example

WITH MEMBER [Market].[Sum Expense for Main States]
AS
 'Sum
 ({[Market].[California], [Market].[Colorado],
   [Market].[Texas], [Market].[Illinois],
   [Market].[Ohio], [Market].[New York],
   [Market].[Massachusetts], [Market].[Florida]}, 
  [Measures].[Total Expenses]
  )' 
SELECT
 {[Measures].[Total Expenses]}
ON COLUMNS, 
  {UDA([Market], "Major Market"),
  [Market].[Sum Expense for Main States]}
ON ROWS
FROM 
 Sample.Basic
WHERE ([Scenario].[Actual])

returns the grid:

Table 4-129 Output Grid from MDX Example

(axis)	Total Expenses
New York	8914
Massachusetts	3412
Florida	5564
East	25310
California	11737
Texas	4041
Illinois	6900
Ohio	5175
Colorado	6131
Central	34864
Sum Expense for Main States	51874