Aggregate (MDX)

Returns a number that is calculated by aggregating over the cells returned by the set expression. If a numeric expression is not provided, this function aggregates each measure within the current query context by using the default aggregation operator that is specified for each measure. If a numeric expression is provided, this function first evaluates, and then sums, the numeric expression for each cell in the specified set.

Syntax

Aggregate(Set_Expression [ ,Numeric_Expression ])

Arguments

• Set_Expression
  A valid Multidimensional Expressions (MDX) expression that returns a set.

• Numeric_Expression
  A valid numeric expression that is typically a Multidimensional Expressions (MDX) expression of cell coordinates that return a number.

Remarks

If a set of empty tuples or an empty set is specified, this function returns an empty value.

The following table describes how the Aggregate function behaves with different aggregation functions.

Aggregation Operator	Result
Sum	Returns the sum of values over the set.
Count	Returns the count of values over the set.
Max	Returns the maximum value over the set.
Min	Returns the minimum value over the set.
Semi-additive aggregation functions	Returns the calculation of semi-additive behavior over the set after projecting the shape to the time axis.
Distinct Count	Aggregates across the fact data contributing to the subcube when the slicer axis includes a set. Returns the distinct count for each member of the set. The result depends on the security on the cells being aggregated, and not on the security on the cells that are required for the computation. Cell security on the set generates an error; cell security below the granularity of the specified set is ignored. Calculations on the set generate an error. Calculations below granularity of the set are ignored. Distinct count over a set that includes a member and one or more of its children returns the distinct count across facts contributing to the child member.
Attributes that cannot be aggregated	Returns the sum of the values.
Mixed aggregation functions	Not supported, and raises an error.
Unary operators	Not respected; values are aggregated by summing.
Calculated measures	Solve order set to ensure calculated measure applies.
Calculated members	Normal rules apply, that is, the last solve order takes precedence.
Assignments	Assignments aggregate according to the measure aggregation function. If the measure aggregation function is distinct count, the assignment is summed.

Examples

The following example returns the sum of the Measures.[Order Quantity] member, aggregated over the first eight months of calendar year 2003 that are contained in the Date dimension, from the Adventure Works cube.

WITH MEMBER [Date].[Calendar].[First8Months2003] AS
    Aggregate(
        PeriodsToDate(
            [Date].[Calendar].[Calendar Year], 
            [Date].[Calendar].[Month].[August 2003]
        )
    )
SELECT 
    [Date].[Calendar].[First8Months2003] ON COLUMNS,
    [Product].[Category].Children ON ROWS
FROM
    [Adventure Works]
WHERE
    [Measures].[Order Quantity]

The following example aggregates over the first two months of the second semester of calendar year 2003.

WITH MEMBER [Date].[Calendar].[First2MonthsSecondSemester2003] AS
    Aggregate(
        PeriodsToDate(
            [Date].[Calendar].[Calendar Semester], 
            [Date].[Calendar].[Month].[August 2003]
        )
    )
SELECT 
    [Date].[Calendar].[First2MonthsSecondSemester2003] ON COLUMNS,
    [Product].[Category].Children ON ROWS
FROM
    [Adventure Works]
WHERE
    [Measures].[Order Quantity]

The following example returns the count of the resellers whose sales have declined over the previous time period, based on user-selected State-Province member values evaluated using the Aggregate function. The Hierarchize and DrillDownLevel functions are used to return values for declining sales for product categories in the Product dimension.

WITH MEMBER Measures.[Declining Reseller Sales] AS 
   Count(
      Filter(
         Existing(Reseller.Reseller.Reseller), 
            [Measures].[Reseller Sales Amount] < ([Measures].[Reseller Sales Amount],
            [Date].Calendar.PrevMember)
            )
         )
MEMBER [Geography].[State-Province].x AS 
   Aggregate ( 
      {[Geography].[State-Province].&[WA]&[US], 
      [Geography].[State-Province].&[OR]&[US] } 
         )
SELECT NON EMPTY Hierarchize (
   AddCalculatedMembers (
      {DrillDownLevel({[Product].[All Products]})}
         )
   )
        DIMENSION PROPERTIES PARENT_UNIQUE_NAME ON COLUMNS 
FROM [Adventure Works]
WHERE ([Geography].[State-Province].x, 
    [Date].[Calendar].[Calendar Quarter].&[2003]&[4],
    [Measures].[Declining Reseller Sales])

See Also

Reference

PeriodsToDate (MDX)
Children (MDX)
Hierarchize (MDX)
Count (Set) (MDX)
Filter (MDX)
AddCalculatedMembers (MDX)
DrilldownLevel (MDX)
Properties (MDX)
PrevMember (MDX)
MDX Function Reference (MDX)

Help and Information

Getting SQL Server 2005 Assistance

Change History

Release	History
17 July 2006	Changed content: Updated syntax and arguments to improve clarity. Added updated examples.