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 (ASO) cubes, 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

The following MDX query:

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 following output:

See Also

Aggregate