MDX union members in different hierarchies

Question

What I want is to create a query that shows members from two different hierarchies side-by-side on the same axis. For example, something like this:

-----------------------------------------------------------------
|                         |               Product               | 
-----------------------------------------------------------------
| Location | Total Amount | Qty of Product A | Qty of Product B | 
-----------------------------------------------------------------
|   USA    | 9,249.23     |  2,382           | 1,009            |
-----------------------------------------------------------------
|   UK     | 9,998.09     |  5,282           | 5,129            |
-----------------------------------------------------------------

It’s clear we can get the results we need by running two different queries, as follows:

select 
[Measures].[Sales Amount] on 0,
[Country].[USA],[Country].[UK] on 1
from [Cube] 
where [Time].[Year].[2010]

select 
crossjoin([Product].[Type].members, [Measures].[Sales Quantity]) on 0,
[Country].[USA],[Country].[UK] on 1
from [Cube]
where [Time].[Year].[2010]

I found this post http://blog.crossjoin.co.uk/2009/05/20/joining-the-results-of-two-mdx-queries-together which is helpful but is a little bit different from my case.

How can I use MDX get result which has different hierarchies members in the same axises?

Answer 1

Maybe something like this:

SELECT
  {
   ([Product].[Type].[All], [Measures].[Sales Amount])
  ,{[Product].[Type].members * [Measures].[Sales Quantity]}
  } ON 0,
  [Country].[USA],[Country].[UK] ON 1
FROM[Cube]
WHERE [Time].[Year].[2010];