MDX Year on Year Sales by Months

Question

Hi all,

I'm stuck on a MDX query, I'm trying to retrieve the following results:

                          [Time].[2009]       [Time].[2010]
[Time].[Months].Members   [Measures].[Sales]  [Measures].[Sales]

So I would like to compare the sales which were in 2009 against 2010 by month.

In terms of a chart I would have two series one for 2009 and 2010, the y axis would be the sales value and the x axis would be the month.

My query looks like this:

SELECT {[Time].[2009], [Time].[2010]} ON COLUMNS,
[Time].[Months].Members ON ROWS
FROM [SalesProductIndicator] WHERE [Measures].[Sales]

It gives me this error:

Mondrian Error:Dimension '[Time]' appears in more than one independent axis.

Thanks in advance

Answer 1

Hi, Try this query:

SELECT {[Time].[2009], [Time].[2010]} ON 0,
[Time].[Months].Members ON 1
FROM [Your Cube Name] WHERE [Measures].[Sales]

I based that on this query (below) that I've tested on the Adventure Works sample cube from Miscrosoft:

SELECT {[Ship Date].[Fiscal Year].&[2002], [Ship Date].[Fiscal Year].&[2003]} ON 0,
[Ship Date].[Month of Year].Members ON 1
FROM [Adventure Works] WHERE [Measures].[Sales Amount]

UPDATE:

Based on your query I'm not sure why it is working without specifiying a hierarchy on your cube query (like [Time].[2010] instead of [Time].[Hierarchy Name].[2010]) but could you try this:

SELECT EXISTS([Time].Members, {[Time].[2009], [Time].[2010]}) ON COLUMNS, 
[Time].[Months].Members ON ROWS 
FROM [SalesProductIndicator] WHERE [Measures].[Sales] 

Thanks