Aggregate() function not working when a measure is not specified.

Hi,

I am new to Analysis Services, having used it for less than a month. I do apologise if this problem is the result of a stupid newbie mistake, but I could really use some help.

I am totally unable to get the Aggregate() function to work unless I specify the optional measure. I have build a cube from the Adventure Works DW database, based on the internet sales fact table and related tables. I used the wizard to design the hierarchy for the time dimension.

Both of the following queries fail with the same error message:

Query 1:

WITH MEMBER [Time Aggregate Test] AS 'Aggregate({[Ship Date].[Calendar Year - Calendar Semester - Calendar Quarter - English Month Name - Day Number Of Month].[Calendar Year].&[2002].&[2].&[3].&[7]:[Ship Date].[Calendar Year - Calendar Semester - Calendar Quarter - English Month Name - Day Number Of Month].[Calendar Year].&[2003].&[1].&[2].&[5]})'

SELECT [Time Aggregate Test] ON 0,{[Measures].[Sales Amount], [Measures].[Tax Amt]} ON 1

FROM [Adventure Works DW]

Query 2

WITH MEMBER [Aggregate Test]

AS 'Aggregate({[Dim Product].[English Product Name].&[Blade],[Dim Product].[English Product Name].&[Chain]})'

SELECT [Aggregate Test] ON 0,{[Measures].[Sales Amount], [Measures].[Tax Amt]} ON 1

FROM [Adventure Works DW]

The error message is: 'The Measures hierarchy already appears on the axis0 axis. It is very important for me to be able to show the aggregate members on columns and the measures on rows if at all possible. I have checked the AggregateFunction on both measures, and it is set to Sum, which is what I want. Again, I do apologise if this is a simple newbie mistake and I would be really grateful for any help.

Since in the WITH statement you didn't specify parent hierarchy, it assumed Measures. So you ended up with same hierarchy (Measures) being used both on columns and rows. To fix you can do this:

WITH MEMBER [Dim Product].[English Product Name].[Aggregate Test]

AS 'Aggregate({[Dim Product].[English Product Name].&[Blade],[Dim Product].[English Product Name].&[Chain]})'

SELECT [Aggregate Test] ON 0,{[Measures].[Sales Amount], [Measures].[Tax Amt]} ON 1

FROM [Adventure Works DW]

|||This works perfectly. Thank you very much for your help, Mosha. Your blog has made very useful reading, by the way.