SQL Server Analysis Services

MDX Functions: Aggregate

Aggregate Function

The Aggregate function is an aggregation function that returns the sum of an expression evaluated over a set. It is particularly useful for aggregating values that might not be directly summable in a standard way, or when you need to apply a specific aggregation logic to a subset of data.

Syntax

Aggregate(Set_Expression [,numeric_expression ] )

Arguments

  • Set_Expression: A valid Multidimensional Expressions (MDX) expression that returns a set of tuples. This set defines the scope over which the numeric expression will be evaluated.
  • numeric_expression: (Optional) A numeric expression that is evaluated for each tuple in the set. If this argument is omitted, the expression is evaluated over the current context of each tuple in the set.

Description

The Aggregate function iterates through each tuple in the specified Set_Expression. For each tuple, it evaluates the numeric_expression (or the default measure if no expression is provided). The results of these evaluations are then summed to produce the final result of the Aggregate function.

This function is a powerful tool for custom aggregation scenarios. For example, you can use it to calculate the sum of profits for specific product categories within a fiscal year, or to sum the sales of products that meet certain criteria.

Examples

Example 1: Simple Aggregation

Sum of Sales for all members of the 'Product' dimension under the 'Bikes' category.

SELECT
  { [Measures].[Internet Sales Amount] } ON COLUMNS,
  { Aggregate(
      Descendants([Product].[Category].&[Bikes], [Product].[Category].[Category]),
      [Measures].[Internet Sales Amount]
    )
  } ON ROWS
FROM [Adventure Works DW]

Example 2: Aggregating a Specific Measure

Calculate the total quantity sold for products manufactured in the USA.

SELECT
  { [Measures].[Reseller Sales Amount] } ON COLUMNS,
  { Aggregate(
      { [Geography].[Country].&[United States] },
      [Measures].[Sales Quantity]
    )
  } ON ROWS
FROM [Adventure Works DW]

Example 3: Using a Calculated Member

Calculate the total value of orders that have a discount greater than 10%.

WITH MEMBER [Measures].[High Discount Value] AS
  Aggregate(
    Filter(
      [Sales Order].[Sales Order Number].Members,
      [Measures].[Discount Amount] / [Measures].[Sales Amount] > 0.10
    ),
    [Measures].[Sales Amount]
  )
SELECT
  { [Measures].[High Discount Value] } ON COLUMNS
FROM [Adventure Works DW]

Considerations

  • The Aggregate function can be computationally intensive, especially with large sets. Optimize your set expressions where possible.
  • Ensure the numeric_expression is valid and returns a numeric value.
  • The order of evaluation within the set might matter if the numeric_expression depends on the context of individual tuples.

See Also