MDX Parent-Child Hierarchy ==> help

All,

I have a parent-child hierarchy and want to acheive the following through MDX in a single query

Filter for a member in parent-child hierarchy (any level ) - I can do that by using key Display the results at a fixed level (say Level 2, with 1 being the top level). The filter in the above point can be set from level 1 to level 5. Display always Level 1 and Level 2 in seperate columns in the report (with filters applied as specified in pt 1)

The foll query is in Adventure Works and says what I'm trying to acheive. 1. Filter for a member in parent-child, 2 & 3. Display Level 1 & 2 members. I know the below will not work but I have listed it to give an idea of what I'm trying to acheive.

Code Snippet

SELECT [Measures].[Amount] on 0

, {

[Organization].[Organizations].[Organization Level 01].Members,

[Organization].[Organizations].[Organization Level 02].Members

} on 1

FROM [Finance]

WHERE [Organization].[Organizations].&[6]

Please can someone help me with the query?

thanks...

Not knowing your application, it seems a little awkward to me, but if it meets your needs, go for it.

Still, one thing to consider is how a sub-select affects the cube space. The best way to explain this is to not and to simply show some data.

If you run your query above, you get a total of $12M for the North America. This is the North America total. However, is this what you really want? (Might be.)

In your sub-select, you attempt to limit on Organizations member with Key of 6. That is the Southeastern division member in Level 4 which has a total of only $216K. You can run the query below to see this.

By moving the criteria to the WHERE clause, we affect the values returned. Now, you can't obviously have the Organizations hierarchy in two places. But if you remember the leaf-level of this dimension is the Organization attribute hierarchy, you can reference the Southeastern division using that hierarchy.

So, the question is, are you wanting the $12M for North America or just wanting to see the Southeastern division's contribution of $216K?

Good luck,

Bryan

Code Snippet

WITH MEMBER Measures.OrgLvl1 AS

'[Organization].[Organizations].CurrentMember.Parent.Member_Value'

select

{Measures.OrgLvl1 ,[Measures].[Amount]} on 0,

[Organization].[Organizations].[Organization Level 02].Memberson 1

from Finance

WHERE [Organization].[Organization].&[6]

|||

Thanks Bryan. You are right. I need to use it in where clause using diff attribute.

Just noted one more thing. The parent value measure is diff in each of the above queries as the context of current member changes. I need the results of your corrected query with the parent as defined in my query (Parent should be AdventureWorks Cycle).

|||

Code Snippet

select

[Measures].[Amount] on 0,

EXISTS(

{

[Organization].[Organizations].[Organization Level 01].Members,

[Organization].[Organizations].[Organization Level 02].Members

},

[Organization].[Organizations].&[6]

)

on 1

from [Adventure Works]

|||

The problem is that I need the results in the following format. (one of the criteria that I had mentioned)

Col 1 (Org Level1 ) col 2 (Org Level 2) Measures.Amount

The above resultset filter for "[Organization].[Organizations].&[i/p]"

|||

I think I got it working using subqueries and calculated member. Pl advice if its not the way to do it or if there is a efficient way

Code Snippet

WITHMEMBER Measures.OrgLvl1 AS '[Organization].[Organizations].CurrentMember.Parent.Member_Value'

select {Measures.OrgLvl1 ,[Measures].[Amount]} on 0,

[Organization].[Organizations].[Organization Level 02].Memberson 1

from

(

Select [Organization].[Organizations].&[6] on 0

FROM [Finance])