Aggregate

Syntax

Aggregate ( set [, accounts_member] )

Parameters

set: A set containing tuples to be aggregated. If empty, #Missing is returned.
accounts_member: A member from an Accounts dimension. If omitted, the current member from Accounts is used. If there is no Accounts dimension, this function behaves the same as Sum.

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 value of accounts_member is evaluated.

If accounts_member has no time balance tag, or if set is one-dimensional, this function behaves the same as Sum().

If accounts_member has a time balance tag, this function behaves as follows:

For TB First, returns the value of accounts_member for the first tuple in set.
For TB First with SKIP, scans tuples in set from first to last and returns first tuple with non-empty value for accounts_member.
For TB Last, returns the value of accounts_member for the last tuple in set.
For TB Last with SKIP, scans tuples in set from last to first and returns first tuple with non-empty value for accounts_member.
For TB Average, returns the average of values of accounts_member at each tuple in set.
For TB Average with SKIP, returns the average of value of accounts_member at each tuple in set without factoring empty values.

Example

WITH 
 SET [T1] AS '{[Time].[1st Half]}'
 SET [GM] AS '{Children ( [Geography].[South] )}'
 MEMBER [Measures].[m1] as 'Aggregate(CrossJoin([T1],{[Geography].CurrentMember}),[Measures].[Price Paid])'
SELECT 
 {[Measures].[m1]} 
ON COLUMNS,
 NON EMPTY {CrossJoin([T1] ,[GM])}
ON ROWS
FROM ASOSamp.Basic

returns the grid:

Table 4-36 Output Grid from MDX Example

(axis)	m1
(1st Half, DISTRICT OF COLUMBIA)	961107.26
(1st Half, DELAWARE)	245394.68
(1st Half, FLORIDA)	1446868.96
(1st Half, GEORGIA)	4766285.74
(1st Half, MARYLAND)	2496467.86
(1st Half, NORTH CAROLINA)	4660670.94
(1st Half, SOUTH CAROLINA)	2524777.6
(1st Half, VIRGINIA)	6253779.5
(1st Half, WEST VIRGINIA)	5009523.72