Answered by:
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 simplicityThursday, December 30, 2010 8:17 PM
Answers

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 Marked as answer by Jinchun ChenMicrosoft employee Friday, January 7, 2011 2:33 AM
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 Erdem Zengin 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 noncalculated 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 DeveloperThursday, 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 simplicityThursday, 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.aspxFriday, 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 Marked as answer by Jinchun ChenMicrosoft employee Friday, January 7, 2011 2:33 AM
Monday, January 3, 2011 6:04 AM