MDX: How to turn tuples with multiple members into tuples with a single member?

Question

I have this MDX query (based on the Foodmart sample database):

SELECT
{[Measures].[Profit]} ON COLUMNS,
{CROSSJOIN([Product].[All Products].Children, [Time].[1997].Children)} ON ROWS
FROM [Sales]

This generates a result like this:

[Product].[Product Family] [Time].[Year] [Time].[Quarter] [Measures].[Profit]
--------------------------+-------------+----------------+-------------------
Drink                     |1997         |Q3              |7203.3445
Drink                     |1997         |Q4              |8005.2245
Food                      |1997         |Q1              |60814.47140000001
Food                      |1997         |Q2              |57323.3736

What I would like to have, is this:

[Product Family],[Year],[Quarter] [Measures].[Profit]
---------------------------------+-------------------
Drink, 1997, Q3                  |7203.3445
Drink, 1997, Q4                  |8005.2245
Food, 1997, Q1                   |60814.47140000001
Food, 1997, Q2                   |57323.3736

I know I can use SetToStr() to serialize the row headers to one string. So now I would like to use that result as rowheader: basically turning each tuple from the original multi-member tuples on the ROW axis into a tuple with just one member, who'se value is a concatenation of the original member names. So basically this:

SELECT
{[Measures].[Profit]} ON COLUMNS,
SetToStr(
    {CROSSJOIN([Product].[All Products].Children, [Time].[1997].Children)}
) ON ROWS
FROM [Sales]

...but of course this does not work, because SetToStr() returns a string, not a set. So I need some way to 'cast' this string back to a set, but with only one member.

Is this possible in standard MDX? How? I can rework the resultset after receiving it but I could really use a pure MDX solution to tackle this problem.

Answer 1

WITH MEMBER [Measures].[name] 
AS [Product].currentMember.Name || ',' || [Time].CurrentMember.Name
SELECT { [Measures].[name] , [Measures].[Sales]} ON COLUMNS
,      ([Product].Children * [Time].Children)    ON ROWS
FROM   [SteelWheelsSales]