Aggregate

Returns a number that is calculated by aggregating over the members returned by the set expression. If a numeric expression is provided, this function first evaluates and then sums the numeric expression for each cell in the specified set.

If a numeric expression is not provided, this function aggregates each member within the current query context by using the default aggregation operator that is specified for each measure.

Syntax

CODE

Aggregate(Set_Expression [ ,Numeric_Expression ])

Arguments

Set_Expression	MDX expression that returns set.
Numeric_Expression	MDX expression that returns number.

Examples

By default members are ordered by name. Aggregated set of members can be used to rearrange order in which members are displayed. For example to reorder Account dimension and have "Expense" as first member and see "Asset" after it add Account calculated member as follows:

CODE

Aggregate({
[Account].[Expense],
[Account].[Asset]
})

Select this member and drill into to see individual aggregated members.

Another option for changing order is that you can Search and Bookmark member names of dimension that you wish to include in your report. Afterwards you can select the bookmarked columns from dimension "Select members" bookmarked members part in the order you wish to display them in chart.

Following example returns invoice amount from 13 months ago to current data.

CODE

aggregate({ [Measures].[Invoice amount],
[Time].[Month].DateMember('13 month ago'):
[Time].[Month].CurrentDateMember}
)