Sum

Returns the sum of values of tuples in a set.

Syntax

Sum ( set [,numeric_value_expression ] )
ParameterDescription

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 essbase.cfg 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:

(axis)Total Expenses
New York8914
Massachusetts3412
Florida5564
East25310
California11737
Texas4041
Illinois6900
Ohio5175
Colorado6131
Central34864
Sum Expense for Main States51874

See Also

  • Aggregate