How to establish in a mdx query 2 measures and a hierarchy

Question

I Have this query:

SELECT NON EMPTY {
    [Art].[Art].[Art].ALLMEMBERS * [Measures].[Costs] * [Measures].[Margin]
} ON COLUMNS 
FROM  [Model]  
CELL PROPERTIES VALUE, BACK_COLOR, FORE_COLOR, FORMATTED_VALUE,
    FORMAT_STRING, FONT_NAME, FONT_SIZE, FONT_FLAGS

I get an error, stating that this cannot be done. My idea is to show, for each member, its cost and margin, as follows:

Article 1     | Article 2     | Article 3
cost | margin | cost | margin | cost | margin

Which would be the correct way? By the way, any good tutorial or website to learn about creating mdx queries?

score 2 · Accepted Answer · answered Jul 11 '13 at 17:40

Your query is crossjoining the measures together: [Measures].[Costs] * [Measures].[Margin] but your idea for the expected results is different. What you show in your expected result is a crossjoin of two sets: {Articles} * {Measures}

I suggest something more like this:

SELECT 
    {[Measures].[Costs], [Measures].[Margin]} ON COLUMNS,
    NON EMPTY {[Art].[Art].[Art].ALLMEMBERS} ON ROWS 
FROM [Model]  
CELL PROPERTIES VALUE, BACK_COLOR, FORE_COLOR, FORMATTED_VALUE, FORMAT_STRING, FONT_NAME, FONT_SIZE, FONT_FLAGS

Or if you want it all on the columns, like your expected result:

SELECT 
    NON EMPTY {[Art].[Art].[Art].ALLMEMBERS} * 
        {[Measures].[Costs], [Measures].[Margin]} ON COLUMNS
FROM [Model]  
CELL PROPERTIES VALUE, BACK_COLOR, FORE_COLOR, FORMATTED_VALUE, FORMAT_STRING, FONT_NAME, FONT_SIZE, FONT_FLAGS

For learning about MDX, I recommend the book "MDX Solutions" as a gentle introduction.

How to establish in a mdx query 2 measures and a hierarchy

1 Answers1