AGGREGATE() issue

• Question

• Hi

I want to aggregate two or more tuplets using either SUM or AGGREGATE function. I faced a paradoxical issue in that:

For Example, if  I try this MDX query

SELECT [Measures].[Sales Amount] ON 0,

AGGREGATE ({([Dim Product].[Product Line].&[M]),([Dim Product].[Product Line].&[T])}) ON 1

FROM [ProdCube]

It doesn't work as ROWS accept only members

In the other hand,  if I try to define the aggregation within a member as follow

WITH MEMBER X AS
{AGGREGATE({([Dim Product].[Product Line].&[M]),([Dim Product].[Product Line].&[T])})}

SELECT [Measures].[Sales Amount] ON 0,
X ON 1
FROM [ProdCube]

Then I face another problem. In fact, the member is considered as measure and I think that measure couldn't be positioned in the 1/ROWS position

So how do I to make use of the aggregation functions like the AGGERGATE() and SUM()

Thank you

The complexity resides in the simplicity
Thursday, December 30, 2010 8:17 PM

• For Example, if  I try this MDX query

SELECT [Measures].[Sales Amount] ON 0,

AGGREGATE ({([Dim Product].[Product Line].&[M]),([Dim Product].[Product Line].&[T])}) ON 1

FROM [ProdCube]

Aggregate() returns a value not a set/tuple/member, so you cannot use that in Axis1.

WITH MEMBER X AS
{AGGREGATE({([Dim Product].[Product Line].&[M]),([Dim Product].[Product Line].&[T])})}

SELECT [Measures].[Sales Amount] ON 0,
X ON 1
FROM [ProdCube]

1)    The Measures hierarchy already appears in the Axis0 axis, so you cannot use it in Axis1.

2)    The calculated member expects a value not a set, so please remove the {}.

You can try this:

WITH MEMBER X AS

AGGREGATE({([Dim Product].[Product Line].&[M]),([Dim Product].[Product Line].&[T])}, measures.defaultmember)

SELECT {[Measures].[Sales Amount],x} ON 0

FROM [ProdCube]

Hope this helps,

Raymond

Raymond Li - MSFT
Monday, January 3, 2011 6:04 AM

All replies

• From your aggregate calculated member above I am assuming you want to create a new member that represents the union of the two product lines? if so, what you need to use is

```with member [Dim Product].[Product Line].x as
[Dim Product].[Product Line].&[M] + [Dim Product].[Product Line].&[T]

SELECT [Measures].[Sales Amount] ON 0,
X ON 1
FROM [ProdCube]

```

Javier Guillen
MCTS / MCITP SQL Server 2008 BI
http://www.msbicentral.com/Blogs/JavierGuillen.aspx
• Proposed as answer by Thursday, December 30, 2010 8:42 PM
Thursday, December 30, 2010 8:35 PM
• Aggregate function take two parameters the first is a set and the second is a non-calculated measure.

For example

with member measures.x Aggregate({[Dim Product].[ProductLine].&[M],[Dim Product].[ProductLine].&[T]},Measures.[Sales Amount])

As far as I now, In addition to your problem. you can not use aggregate function over calculated measure because the function doesn't know the aggregation property. Instead use sum, avg etc. what is appropriate. I faced the same problem =)

if you want to show the the total for M and T in one row. Javier is right. You can create a new member by + operator with two members you have.

BI Developer
Thursday, December 30, 2010 8:50 PM
• Hi erdemz and Javier,

In fact, I want to use AGGREGATE because of the flexibility. I can define the aggergate function later to perform more custom aggregation combination that's way I prefered using AGGREGATE over + operator

Thank you

The complexity resides in the simplicity
Thursday, December 30, 2010 10:18 PM
• The aggregate function applies to measures.  However, in your example above there are only dimension members used in the aggregate function which made me think you wanted to union them into a new calculated member.

This new calculated member could then be used as part of the aggregate function, when relating it to a measure; such as

```Aggregate(X, [Measures].[Sales Amount])

```

In that way, the new calculated member will roll up the measure based on the underlying aggregation, and it would do so for the two product line members.

Javier Guillen
MCTS / MCITP SQL Server 2008 BI
http://www.msbicentral.com/Blogs/JavierGuillen.aspx
Friday, December 31, 2010 2:09 AM
• For Example, if  I try this MDX query

SELECT [Measures].[Sales Amount] ON 0,

AGGREGATE ({([Dim Product].[Product Line].&[M]),([Dim Product].[Product Line].&[T])}) ON 1

FROM [ProdCube]

Aggregate() returns a value not a set/tuple/member, so you cannot use that in Axis1.

WITH MEMBER X AS
{AGGREGATE({([Dim Product].[Product Line].&[M]),([Dim Product].[Product Line].&[T])})}

SELECT [Measures].[Sales Amount] ON 0,
X ON 1
FROM [ProdCube]

1)    The Measures hierarchy already appears in the Axis0 axis, so you cannot use it in Axis1.

2)    The calculated member expects a value not a set, so please remove the {}.

You can try this:

WITH MEMBER X AS

AGGREGATE({([Dim Product].[Product Line].&[M]),([Dim Product].[Product Line].&[T])}, measures.defaultmember)

SELECT {[Measures].[Sales Amount],x} ON 0

FROM [ProdCube]

Hope this helps,

Raymond

Raymond Li - MSFT
Monday, January 3, 2011 6:04 AM