3

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 :)

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Browse other questions tagged or ask your own question.