MDX : how to create a 2x2 array with a query, using hard coded values

Question

in MDX I would like to create a select which returns a 2x2 array, filled with hard coded values. Here is an attempt:

WITH 
MEMBER a AS 1
MEMBER b AS 2
SET un AS { a , b }


SELECT { un} ON 0,
[Dim misc].[Gender].[(All)] ON 1
FROM [my cube]

it returns:

but I would like it to return an other row, with other values than 1,2.

Answer 1

You have solved half the problem already. However defining a set is not necessary. You need to do the following

1. Use defaultmember,
2. Use Case
3. Define a dummy member for the dimension you use.
4. Instead of Set just define members.

Take a look at the example below based on adventure works.

with 
member Column1Row1 as 1
member Column2Row1 as 2
member Column1Row2 as 3
member Column2Row2 as 4

member Column1 as case when [Product].[Product].currentmember  is [Product].[Product].defaultmember then Column1Row1 else Column1Row2  end 
member Column2 as case when [Product].[Product].currentmember  is [Product].[Product].defaultmember then Column2Row1 else Column2Row2  end

member [Product].[Product].[Row2]
as [Product].[Product].defaultmember

select 
{Column1,Column2} on 0,
{
[Product].[Product].defaultmember,
[Product].[Product].[Row2]}
on 1 
from 
[Adventure Works]

Result