TM1 MDX Expressions

[Venkat1688]

I want to create a dynamic subset that will return the parent element with next level children element in the following hierarchy

A - Top most Parent
B - under A with C and D as children
E - under A with F and G as children
H & I - under A as leaf level elemenst

As per the requirement, the query should return A,B,E,H & I.
Can any one help me out with this and i want to know the general syntax for MDX expression

[rmackenzie]

Hmm... but the definition of this subset is fixed.

Generally you opt to use dynamic expressions (using MDX) because you don't know in advance what elements will appear in the subset, but you do know what the logic behind it is. For example, give me all the elements beginning with A. Or, give me all the elements and their parents where the element has an attribute value of X for attribute Y.

Per your definition, it is more efficient to simply arrange the elements in the Subset Editor as you want to see them and then save the subset. However, in answer your question:

Code: Select all

{[A], [B], [C]. [D], [E], [F], [G], [H], [I]}

Or is it this, it isn't clear if you want to include or exclude C, D, F and G:

Code: Select all

{[A], [B], [E], [H], [I]}

TM1 will look after the organisation into a tree structure.

Note that if the element names (A, B, C etc) exist in another dimension then you will need to qualify the element names:

Code: Select all

{[DIM_NAME].[A], [DIM_NAME].[B], etc }

[Venkat1688]

The case is such that , new elements will come in future. For example this is the list of customers in an organisation.
The top most element is All Customers.
Under the top most element will be the Group Clients(with End customers as children) and those customers(End customers) without Group client.
As per the requirement, for planning cube only the End Customers should be visible to plan.
For Review cube ,only following things should be visible.

All Customers
Group Clients(wihout the End customer under it)
End Customers(those customers who don have any Group Client)

Is this clear ?

[lotsaram]

I want to create a dynamic subset that will return the parent element with next level children element in the following hierarchy

A - Top most Parent
B - under A with C and D as children
E - under A with F and G as children
H & I - under A as leaf level elemenst

As per the requirement, the query should return A,B,E,H & I.
Can any one help me out with this and i want to know the general syntax for MDX expression

Without wanting to sound too much like a grumpy old man let me just ask you how hard you tried before posting this question? Did you even open the subset editor and make any attempt to record an expression and view the result? Because it seems to me that had you made such an attempt, even a feeble 2 minute effort, then the answer would have been immediately obvious in the DrillDownMember function.
{ DRILLDOWNMEMBER( { [Your_Dimension].[A] }, { [Your_Dimension].[A] } ) }
This will return the member "A" followed by its immediate children only. Which I believe is what you want?

[Venkat1688]

The answer i found was DrillDownLevel after coming to know that i cant be recorded in Subset editor.
DrillDownLevel worked perfectly.
{DRILLDOWNLEVEL( {[A].[A]})}

this expression gave

A,B,E,H & I as the output.

Thanks