AGGREGATE

Description:

This function is used to return 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 ])

Return Type:
SCALAR

Parameters:

Parameter	Description

Parameter	Description
Set_Expression	It is a valid MDX expression that returns a set.
Numeric_Expression	A valid numeric expression is typically an MDX expression of cell coordinates that returns a number.

Examples:

"WITH MEMBER [Measures].[Total Sales] AS
    SUM([Product].[Product].Members, [Measures].[Internet Sales Amount])
SELECT 
    [Measures].[Total Sales] ON COLUMNS
FROM [AdventureWorksMF_ValueBased]"

"WITH MEMBER [Measures].[Avg Sales] AS
    AVG([Product].[Product].Members, [Measures].[Internet Sales Amount])
SELECT 
    [Measures].[Avg Sales] ON COLUMNS
FROM [AdventureWorksMF_ValueBased]"

"WITH MEMBER [Measures].[Max Sales] AS
    MAX([Product].[Product].Members, [Measures].[Internet Sales Amount])
SELECT 
    [Measures].[Max Sales] ON COLUMNS
FROM [AdventureWorksMF_ValueBased]"

"WITH MEMBER [Measures].[Min Sales] AS
    MIN([Product].[Product].Members, [Measures].[Internet Sales Amount])
SELECT 
    [Measures].[Min Sales] ON COLUMNS
FROM [AdventureWorksMF_ValueBased]"