locked
MDX query - 2 hierarchy levels on 1 axis RRS feed

  • Question

  • I'm trying to write a basic query with 2 different levels of a hierachy displayed on 1 axis.

    SELECT { [Date].[Calendar].[Year].&[2013] } ON COLUMNS ,     
      
    { NONEMPTY(     

    { [Product].[Product Hierarchy].[Product Category].members, [Product].[Product Hierarchy].[Product Subcategory].members}

    * { [Measures].[Sales], [Measures].[COS] } , { [Date].[Calendar].[Year].&[2013] } 

    ) } ON ROWS       

     
    FROM [Cube]     

    The query executes but I only have the Product Category returned along with the measures. 

    There should be no need to crossjoin right as I'm querying the same dim.

    Any ideas?

    Lee

    Thursday, February 21, 2013 9:24 AM

Answers

  • Can you use the attributes outside of the hierarchy?

    Something like this:

    SELECT 

     { [Date].[Calendar].[Year].&[2013] } ON COLUMNS ,      
       
    { NONEMPTY(     

    {[Product].[Product Category].[Product Category].members}*{[Product].[Product Subcategory].[Product Subcategory].members}

    * { [Measures].[Sales], [Measures].[COS] } , { [Date].[Calendar].[Year].&[2013] } 

    ) } ON ROWS       

      
    FROM [Cube]
    • Proposed as answer by V.Rocca Monday, February 25, 2013 3:09 PM
    • Marked as answer by Elvis Long Friday, March 1, 2013 1:33 AM
    Thursday, February 21, 2013 4:49 PM

All replies

  • The cubeset being returned actual merges the 2 hierarchy elements into 1 column.  I suppose this is because of the hierarchy.

    Is there anyway to split the results into seperate columns?

    Thursday, February 21, 2013 2:10 PM
  • Try using the DESCENDANTS function to return the 2 levels in the hierarchy

    DESCENDANTS([Product].[Product Hierarchy].[Product Category], [Product].[Product Hierarchy].[Product Subcategory], SELF_AND_BEFORE)

    hth


    -Remember to mark as helpful/the answer if you agree with the post.

    Thursday, February 21, 2013 2:28 PM
  • I tried this originally but it gives the same effect. 

    Thursday, February 21, 2013 3:27 PM
  • Can you use the attributes outside of the hierarchy?

    Something like this:

    SELECT 

     { [Date].[Calendar].[Year].&[2013] } ON COLUMNS ,      
       
    { NONEMPTY(     

    {[Product].[Product Category].[Product Category].members}*{[Product].[Product Subcategory].[Product Subcategory].members}

    * { [Measures].[Sales], [Measures].[COS] } , { [Date].[Calendar].[Year].&[2013] } 

    ) } ON ROWS       

      
    FROM [Cube]
    • Proposed as answer by V.Rocca Monday, February 25, 2013 3:09 PM
    • Marked as answer by Elvis Long Friday, March 1, 2013 1:33 AM
    Thursday, February 21, 2013 4:49 PM
  • Try using the attribute hierarchy instead of the user-defined hierarchy

    Something like

    { [Product].[Product Category].[Product Category].members*[Product].[Product Subcategory].[Product Subcategory].members}


    -Remember to mark as helpful/the answer if you agree with the post.

    • Proposed as answer by V.Rocca Monday, February 25, 2013 3:10 PM
    Thursday, February 21, 2013 7:49 PM
  • Hi Guys,

    Is there any way if I dont have that attribute, but both required attributes are present in only one hierarchy. Any work around.

    Thanks,

    Dhir M

    Wednesday, July 19, 2017 11:17 AM