Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

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

share|improve this question

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Browse other questions tagged or ask your own question.