5 Replies Latest reply: Mar 16, 2011 6:19 PM by 762759 RSS

    Display two members of same dimension hierarchy using mdx on 2 diff columns

    599500
      Hi,

      Is it possible to print the parent of a member that belongs to a certain level in the hierarchy. For this example, the Dimension hierarchy is a Variable hierarchy based on a Row Source, - BOM - Bill of Materials.


      Let say there is a BOM hierarchy with 4 levels.


      Finished Good - Comp1 - Comp2 - Comp3.

      Then we want to display in the IOP report as follows.

      Finished Good Supply Chain--These are the column headers
      -------------------------------------------------------------
      Finished Good1 Finished Good1
      Comp1
      Comp2


      Finished Good2 Finished Good2
      Comp3
      Comp4
      Comp3

      Edited by: user596497 on Mar 16, 2011 1:39 PM
        • 1. Re: Display two members of same dimension with hierarchy in same mdx query
          847765
          hi,
          To display two members of same dimension with different level we have following functions in mdx:
          parent ,children,ancestor,descendant etc.

          to find parent at particular level use ancestor function.
          ancestor(Member m,Level l)

          Select {Ancestor([Calendar].[Jan-07],[fiscaldim].[Quarter])} on columns from [Demand]
          It will return parent of Jan-07 at Quarter level
          • 2. Re: Display two members of same dimension with hierarchy in same mdx query
            599500
            But can we display it the parent in one column and the child in the next column
            • 3. Re: Display two members of same dimension with hierarchy in same mdx query
              762759
              The question is wrong ...

              MDX helps you to construct cross-product of indices for the underlying cube and then it tells the system to find whether there is any cell with that index, and if so, returns a value for that combination.

              If you put two dimensions next to each other, what does it mean? There is no such combination in the underlying cube as each dimension is uniquely represented. We will throw an error as follows:

              You cannot mix-up display semantics with the syntax/semantics of MDX.

              "Can only cross join sets with different dimensions Statement"

              ---------------------------------------------------------------------
              An internal error occurred during the query execution phase. Please contact your administrator.
              Query Definition

              WITH SET [AnotherGeography] AS '{[Geography].DefaultMember}' SELECT {Fiscal.DefaultMember} ON COLUMNS, { CrossJoin( {[Geography].DefaultMember}, {[Customers].DefaultMember}, {[AnotherGeography]}, {Hierarchize(Descendants([Product].DefaultMember, [Product].[Product Family], SELF_AND_BEFORE), POST) }, { [Measures].[Target Revenue], [Measures].[Demand Plan Revenue], [Measures].[Market Share], [Measures].[Adjusted Demand Plan Quantity] }) } ON ROWS FROM [Demand]
              Can only cross join sets with different dimensions Statement: WITH SET [AnotherGeography] AS '{[Geography].DefaultMember}' SELECT {Fiscal.DefaultMember} ON COLUMNS, { CrossJoin( {[Geography].DefaultMember}, {[Customers].DefaultMember}, {[AnotherGeography]}, {Hierarchize(Descendants([Product].DefaultMember, [Product].[Product Family], SELF_AND_BEFORE), POST) }, { [Measures].[Target Revenue], [Measures].[Demand Plan Revenue], [Measures].[Market Share], [Measures].[Adjusted Demand Plan Quantity] }) } ON ROWS FROM [Demand]
              • 4. Re: Display two members of same dimension with hierarchy in same mdx query
                599500
                Hi ,

                Thanks for your response. I have changed the title related to this thread accordingly.

                I understand we cannot crossjoin - but is there a workaround available
                • 5. Re: Display two members of same dimension hierarchy using mdx on 2 diff columns
                  762759
                  well, only way is to land the data separately using two queries somewhere in a sheet and then splice them together. Or have a parent attribute for each member (assuming only one parent) and then you can refer to that in your query.