5 Replies Latest reply on Mar 16, 2011 11:19 PM by user729142-Oracle

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

    Laksh
      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
            Laksh
            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
              user729142-Oracle
              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
                Laksh
                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
                  user729142-Oracle
                  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.