I am fairly new to MDX. I have a cube with a measure SalesRevenue and
a number of dimensions eg. location, product etc. The cube is built
and processed ok in AS 2005. I want to get a result like the following

North 1000
South 2000
East 4000
West 5000
Product1 3000
Product2 5000
Product3 4000

I can get measure SalesRevenue for each member of location eg.

select {[Measures].[SalesRevenue] } on columns,
{ [Dim location].[name].members } on rows
from myCube

then another MDX to get SalesRevenue for products

select {[Measures].[SalesRevenue] } on columns,
{ [Dim product].[name].members } on rows
from myCube

I tried union to get the result above but it complains with

select {[Measures].[SalesRevenue] } on columns,
{ [Dim location].[name].members, [Dim product].[name].members } on
rows
from myCube

Query (2, 1) Two sets specified in the function have different
dimensionality.

I just want to see SalesRevenue for each dimension and member nothing
too fancy no hierarchy or anything.

Thanks in advance.

you can't "union" 2 different dimension, but you can crossjoin these
dimensions or display in rows and columns...

first option:
select location.members on rows, products.member on columns
from mycube
where (measures.salesrevenue)

second option:
select {location.members * products.member } on rows,
measures.salesrevenue on columns
from mycube

or
select nonemptycrossjoin(location.members, products.member) on rows,
measures.salesrevenue on columns
from mycube


"Me" <adrian1234 (AT) hotmail (DOT) co.uk> wrote

Thanks

however the results give me a crossjoin and I only want each
dimension.member on its own like

North 1000
South 2000
East 4000
West 5000
Product1 3000
Product2 5000
Product3 4000

I can write

select {[Measures].[SalesRevenue] } on columns,
{ [Dim location].[name].members } on rows
from myCube
go
select {[Measures].[SalesRevenue] } on columns,
{ [Dim product].[name].members } on rows
from myCube

This is going in the right direction but I have two result sets when I
want one.

Any other ideas welcome

Ta

I think the client shoud be responsible for the kind of requirement.

If I remember right, extensity Decision supports that feature you want.

Ohjoo

"Me" <adrian1234 (AT) hotmail (DOT) co.uk> wrote