This discussion is archived
5 Replies Latest reply: Mar 16, 2011 4:19 PM by 762759 RSS

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

599500 Newbie
Currently Being Moderated
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 Newbie
    Currently Being Moderated
    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 Newbie
    Currently Being Moderated
    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 Explorer
    Currently Being Moderated
    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 Newbie
    Currently Being Moderated
    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 Explorer
    Currently Being Moderated
    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.

Legend

  • Correct Answers - 10 points
  • Helpful Answers - 5 points