ssasmdxmeasures

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


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?


Solution

  • 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.