none
MDX query to create a calculated member that works across all hierarchies in a dimension

    Question

  • Hi All,
       I am having a product dimension with product hierarchy that has the levels Product Line,Group,subgroup,brand and SKU and Supplier hierarchy that has Supplier, Brand and Sku. I need to calculate the Market share along the product hierarchy as well as supplier hierarchy. How can I create the calculated measure that works well with both the hierarchies?
    Now, it works only with the product hierarchy and the MDX query I had used is
    Case
    // Test to avoid division by zero.
    When IsEmpty
         (
            [Measures].[Sales Value]
         )
    Then Null

    // Test for current coordinate being on the (All) member.
    When [Dim Product].[Product Hierarchy].CurrentMember.level.ordinal=0

    Then 1

    Else ([Dim Product].[Product Hierarchy].CurrentMember,
           [Measures].[Sales Value] )
         /
         ( Root([Dim Product].[Product Hierarchy].CurrentMember.Parent),
           [Measures].[Sales Value] )
    End

    Thanks in Advance,
    Valli

    Thursday, October 29, 2009 10:14 AM

Answers

All replies

  • Valli,

    I am not sure about your question because your product dimesnion contains Product Line,Group,subgroup,brand and SKU and Supplier hierarchy contains again Brand and SKU.

    So what way does your MDX requires both the hierarchy members to be part of your calculation? I mean to say that both the attributes are already part of your above mentioned MDX?

    Please let me know if Iam wrong in understanding your MDX.


    Thanks,
    Ashok
    Thursday, October 29, 2009 10:36 AM
  • Hi Ashok,

       All products are supplied through a particular supplier.We just want to know out of the total sales that has happened what is the share of different suppliers and which brand is contributing more to the suppliers share and which Sku is contributing to the brands share. Also we would like to know about the contribution of each group,subgroup etc which has been done through the above calculation. I just want to create one calculated measure that would show the market share along the product hierarchy as well as the supplier hierarchy based on what is chosen by the enduser.

    Thanks,

    Valli

    Saturday, October 31, 2009 4:42 AM
  • Hi Valli,


    You mean ratio to parent? If so, you can refer to the below links:

    http://geekswithblogs.net/darrengosbell/archive/2008/07/26/mdx-ratio-of-quotcurrent-parentquot-issue.aspx

    -       using Axis(), but the limitation is the fixed axis and crossjion.

    http://sqlblog.com/blogs/mosha/archive/2008/09/11/ratio-to-parent-on-rows-in-mdx.aspx

    -       a more generate solution with Procedure.

    http://tomislavpiasevoli.spaces.live.com/blog/cns!5BB64CF526505D83!403.entry

    -       Dynamic axis without prefix axis1/ axis2, works on cube browser/OWC/SSMS

     

    Regards,

    Raymond

    • Marked as answer by vallisaravanan Wednesday, November 4, 2009 5:29 AM
    Monday, November 2, 2009 3:37 AM
    Moderator
  • Hi Raymond,
       Thanks a lot.The axis function helped me to do that.

    Regards,
    Valli
    Monday, November 2, 2009 10:01 AM