Returns a numeric value which is the aggregation of the 'member-set' values.
On the example we aggregate the default [Measures] for all the calendar months.
Query
WITH
MEMBER [Time].[Calendar].[All sales] AS Aggregate([Time].[Calendar].[Month].members)
SELECT
[Time].[Calendar].[All sales] ON COLUMNS,
[Geography].[Geo].[Country].members ON ROWS
FROM
[Sales]
Result
|
All sales |
| Canada |
|
| Mexico |
|
| United States |
768 |
| Venezuela |
|
| France |
4 |
| Spain |
3 |
| Switzerland |
248 |
Assertion : MDX Equals
WITH
MEMBER [Time].[Calendar].[All sales] AS Sum([Time].[Calendar].[Month].members,[Measures].[Amount])
SELECT
[Time].[Calendar].[All sales] ON COLUMNS,
[Geography].[Geo].[Country].members ON ROWS
FROM
[Sales]
Assertion : MDX Equals
/*specify measure for the aggregate*/
WITH
MEMBER [Time].[Calendar].[All sales] AS Aggregate( [Time].[Calendar].[Month].members, [Measures].[Amount] )
SELECT
[Time].[Calendar].[All sales] ON COLUMNS,
[Geography].[Geo].[Country].members ON ROWS
FROM
[Sales]
Returns a numeric value which is the aggregation of member-set values.
On the example we aggregate the sales for all the calendar months.
Query
WITH
MEMBER [Time].[Calendar].[All sales] AS Aggregate([Time].[Calendar].[Month].members)
SELECT
[Time].[Calendar].[All sales] ON COLUMNS,
[Geography].[Geo].[Country].members ON ROWS
FROM
[Sales]
Result
|
All sales |
| Canada |
|
| Mexico |
|
| United States |
768 |
| Venezuela |
|
| France |
4 |
| Spain |
3 |
| Switzerland |
248 |
Assertion : MDX Equals
WITH
MEMBER [Time].[Calendar].[All sales] AS Sum([Time].[Calendar].[Month].members,[Measures].[Amount])
SELECT
[Time].[Calendar].[All sales] ON COLUMNS,
[Geography].[Geo].[Country].members ON ROWS
FROM
[Sales]
Assertion : MDX Equals
/*specify measure for the aggregate*/
WITH
MEMBER [Time].[Calendar].[All sales] AS Aggregate( [Time].[Calendar].[Month].members, [Measures].[Amount] )
SELECT
[Time].[Calendar].[All sales] ON COLUMNS,
[Geography].[Geo].[Country].members ON ROWS
FROM
[Sales]
Performing a SUM aggregation...
Query
WITH
MEMBER [Geography].[Geo].[Spain+Switzerland] AS Aggregate( { ([Geography].[Geo].[Spain],[Measures].[Amount]), ([Geography].[Geo].[Switzerland],[Measures].[Amount]) } )
SELECT
{[Geography].[Geo].[Spain+Switzerland]} ON 0
FROM
[Sales]
Result
Assertion : Cell Equals
SELECT
{[Measures].[Amount]} ON 0
FROM
[Sales]
WHERE
{[Geography].[Geo].[Spain]}+{[Geography].[Geo].[Switzerland]}
Aggregation over the same tuple is going to add twice the value. Note, we are adding twice the same fact row which is not the case for 'engine' standard calculations.
In our example, we sum the value of [Spain] two times.
Query
WITH
MEMBER [Geography].[Geo].[Spain+Spain] AS Aggregate( { ([Geography].[Geo].[Spain],[Measures].[Amount]), ([Geography].[Geo].[Spain],[Measures].[Amount]) } )
SELECT
{[Geography].[Geo].[Spain+Spain]} ON 0
FROM
[Sales]
Result
Assertion : Cell Equals
WITH
MEMBER [Spain * 2] AS ([Geography].[Geo].[Spain],[Measures].[Amount]) * 2
SELECT
{[Measures].[Spain * 2]} ON 0
FROM
[Sales]
Performing a COUNT aggregation...
Query
WITH
MEMBER [Geography].[Geo].[Spain+Switzerland] AS Aggregate( { ([Geography].[Geo].[Spain],[Measures].[Amount-Count]), ([Geography].[Geo].[Switzerland],[Measures].[Amount-Count]) } )
SELECT
{[Geography].[Geo].[Spain+Switzerland]} ON 0
FROM
[Sales]
Result
Assertion : Cell Equals
SELECT
{[Measures].[Amount-Count]} ON 0
FROM
[Sales]
WHERE
{[Geography].[Geo].[Spain]}+{[Geography].[Geo].[Switzerland]}
Performing a MIN aggregation...
Query
WITH
MEMBER [Geography].[Geo].[Spain+Switzerland] AS Aggregate( { ([Geography].[Geo].[Spain],[Measures].[Amount-Min]), ([Geography].[Geo].[Switzerland],[Measures].[Amount-Min]) } )
SELECT
{[Geography].[Geo].[Spain+Switzerland]} ON 0
FROM
[Sales]
Result
Assertion : Cell Equals
SELECT
{[Measures].[Amount-Min]} ON 0
FROM
[Sales]
WHERE
{[Geography].[Geo].[Spain]}+{[Geography].[Geo].[Switzerland]}
Performing a MAX aggregation...
Query
WITH
MEMBER [Geography].[Geo].[Spain+Switzerland] AS Aggregate( { ([Geography].[Geo].[Spain],[Measures].[Amount-Max]), ([Geography].[Geo].[Switzerland],[Measures].[Amount-Max]) } )
SELECT
{[Geography].[Geo].[Spain+Switzerland]} ON 0
FROM
[Sales]
Result
Assertion : Cell Equals
SELECT
{[Measures].[Amount-Max]} ON 0
FROM
[Sales]
WHERE
{[Geography].[Geo].[Spain]}+{[Geography].[Geo].[Switzerland]}
Performing a AVERAGE (leaves) aggregation...
Query
WITH
MEMBER [Measures].[Spain+Switzerland] AS Aggregate( { ([Geography].[Geo].[Spain],[Measures].[Amount-AvgLeaves]), ([Geography].[Geo].[Switzerland],[Measures].[Amount-AvgLeaves]) } )
SELECT
{[Measures].[Spain+Switzerland]} ON 0
FROM
[Sales]
Result
| Spain+Switzerland |
| 35.857142857142854 |
Assertion : Cell Equals
SELECT
{[Measures].[Amount-AvgLeaves]} ON 0
FROM
[Sales]
WHERE
{[Geography].[Geo].[Spain]}+{[Geography].[Geo].[Switzerland]}
Not supported.
Query
WITH
MEMBER [Geography].[Geo].[Spain+Switzerland] AS Aggregate( { ([Geography].[Geo].[Spain],[Measures].[Amount-None]), ([Geography].[Geo].[Switzerland],[Measures].[Amount-None]) } )
SELECT
{[Geography].[Geo].[Spain+Switzerland]} ON 0
FROM
[Sales]
Result
| Spain+Switzerland |
| #ERROR : (OLAP_AGGREGATE_TYPE_NOT_SUPPORTED) |
Error Code : OLAP_AGGREGATE_TYPE_NOT_SUPPORTED
Error Message : Aggregate() : the aggregation 'none' is not supported (measure or calculated measure/member:[Measures].[Amount-None])
Not supported.
Query
WITH
MEMBER [Geography].[Geo].[Spain+Switzerland] AS Aggregate( { ([Geography].[Geo].[Spain],[Measures].[Amount-AvgChildren]), ([Geography].[Geo].[Switzerland],[Measures].[Amount-AvgChildren]) } )
SELECT
{[Geography].[Geo].[Spain+Switzerland]} ON 0
FROM
[Sales]
Result
| Spain+Switzerland |
| #ERROR : (OLAP_AGGREGATE_TYPE_NOT_SUPPORTED) |
Error Code : OLAP_AGGREGATE_TYPE_NOT_SUPPORTED
Error Message : Aggregate() : the aggregation 'average (children)' is not supported (measure or calculated measure/member:[Measures].[Amount-AvgChildren])
Not supported.
Query
WITH
MEMBER [Geography].[Geo].[Spain+Switzerland] AS Aggregate( { ([Geography].[Geo].[Spain],[Measures].[Amount-Open]), ([Geography].[Geo].[Switzerland],[Measures].[Amount-Open]) } )
SELECT
{[Geography].[Geo].[Spain+Switzerland]} ON 0
FROM
[Sales]
Result
| Spain+Switzerland |
| #ERROR : (OLAP_AGGREGATE_TYPE_NOT_SUPPORTED) |
Error Code : OLAP_AGGREGATE_TYPE_NOT_SUPPORTED
Error Message : Aggregate() : the aggregation 'open' is not supported (measure or calculated measure/member:[Measures].[Amount-Open])
Not supported.
Query
WITH
MEMBER [Geography].[Geo].[Spain+Switzerland] AS Aggregate( { ([Geography].[Geo].[Spain],[Measures].[Amount-Close]), ([Geography].[Geo].[Switzerland],[Measures].[Amount-Close]) } )
SELECT
{[Geography].[Geo].[Spain+Switzerland]} ON 0
FROM
[Sales]
Result
| Spain+Switzerland |
| #ERROR : (OLAP_AGGREGATE_TYPE_NOT_SUPPORTED) |
Error Code : OLAP_AGGREGATE_TYPE_NOT_SUPPORTED
Error Message : Aggregate() : the aggregation 'close' is not supported (measure or calculated measure/member:[Measures].[Amount-Close])
Not supported.
Query
WITH
MEMBER [Geography].[Geo].[Spain+Switzerland] AS Aggregate( { ([Geography].[Geo].[Spain],[Measures].[Amount-OpenNoEmpty]), ([Geography].[Geo].[Switzerland],[Measures].[Amount-OpenNoEmpty]) } )
SELECT
{[Geography].[Geo].[Spain+Switzerland]} ON 0
FROM
[Sales]
Result
| Spain+Switzerland |
| #ERROR : (OLAP_AGGREGATE_TYPE_NOT_SUPPORTED) |
Error Code : OLAP_AGGREGATE_TYPE_NOT_SUPPORTED
Error Message : Aggregate() : the aggregation 'open (non-empty)' is not supported (measure or calculated measure/member:[Measures].[Amount-OpenNoEmpty])
Not supported.
Query
WITH
MEMBER [Geography].[Geo].[Spain+Switzerland] AS Aggregate( { ([Geography].[Geo].[Spain],[Measures].[Amount-CloseNoEmpty]), ([Geography].[Geo].[Switzerland],[Measures].[Amount-CloseNoEmpty]) } )
SELECT
{[Geography].[Geo].[Spain+Switzerland]} ON 0
FROM
[Sales]
Result
| Spain+Switzerland |
| #ERROR : (OLAP_AGGREGATE_TYPE_NOT_SUPPORTED) |
Error Code : OLAP_AGGREGATE_TYPE_NOT_SUPPORTED
Error Message : Aggregate() : the aggregation 'close (non-empty)' is not supported (measure or calculated measure/member:[Measures].[Amount-CloseNoEmpty])
Not supported.
Query
WITH
MEMBER [Geography].[Geo].[Spain+Switzerland] AS Aggregate( { ([Geography].[Geo].[Spain],[Measures].[Amount-Unique]), ([Geography].[Geo].[Switzerland],[Measures].[Amount-Unique]) } )
SELECT
{[Geography].[Geo].[Spain+Switzerland]} ON 0
FROM
[Sales]
Result
| Spain+Switzerland |
| #ERROR : (OLAP_AGGREGATE_TYPE_NOT_SUPPORTED) |
Error Code : OLAP_AGGREGATE_TYPE_NOT_SUPPORTED
Error Message : Aggregate() : the aggregation 'distinct count' is not supported (measure or calculated measure/member:[Measures].[Amount-Unique])
Not supported.
Query
WITH
MEMBER [Measures].[Spain+Switzerland] AS Aggregate( { ([Geography].[Geo].[Spain],[Measures].[Amount-NoEmpty]), ([Geography].[Geo].[Switzerland],[Measures].[Amount-NoEmpty]) } )
SELECT
{[Measures].[Spain+Switzerland]} ON 0
FROM
[Sales]
Result
| Spain+Switzerland |
| #ERROR : (OLAP_AGGREGATE_TYPE_NOT_SUPPORTED) |
Error Code : OLAP_AGGREGATE_TYPE_NOT_SUPPORTED
Error Message : Aggregate() : the aggregation 'no-empty' is not supported (measure or calculated measure/member:[Measures].[Amount-NoEmpty])