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

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
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
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
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"

---------------------------------------------------------------------