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
add comment

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.