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.