I am trying to combine two dimensions in an MDX query.
SELECT
NON EMPTY
(
[XXX].[YYY].children
) ON ROWS,
NON EMPTY
{
[Measures].[Dimension1A],
[Measures].[Dimension1B],
[Measures].[Dimension2A],
[Measures].[Dimension2B]
} ON COLUMNS
FROM [RePro]
WHERE
(
[ABC].[ABC].[Val]
) CELL PROPERTIES VALUE
This produces results where all the [Measures]
dimensions are in separate columns. How can I make it so that [Dimension1A]
and [Dimension1B]
are combined/merged into the same column, and likewise for [Dimension2A]
and [Dimension2B]
?
I've tried the solution here, but I get '#Error' in my results with the message "Aggregate functions cannot be used on calculated members in the Measures dimension". This is what I tried:
WITH SET [Combined] AS
{
[Measures].[Dimension1A],
[Measures].[Dimension1B]
}
MEMBER [Measures].[Dimension1] AS Aggregate([Combined])
SELECT
NON EMPTY
(
[XXX].[YYY].children
) ON ROWS,
UNION
{
Except([Measures], [Combined]),
[Measures].[Dimension1]
} ON COLUMNS
FROM [RePro]
WHERE
(
[ABC].[ABC].[Val]
) CELL PROPERTIES VALUE
I have removed [Dimenion2A]
and [Dimension2B]
for brevity. Any ideas?
Thanks :)