MDX to drill/get decendents of multiple consolidated elements

[initm1]

Hi, please help me with MDX to drill/get decedents of multiple consolidated elements?
I have a cost center dimension and I want to be able to drill bottom level for 3 of consolidated elements

for single element i use {TM1FILTERBYLEVEL({TM1SUBSETALL( [CostCenter].[CCLV1] )}, 0)}

now i want to do for CCLV1, CCLV5, and CCLV7.

Please help me. Thank you.

[tomok]

UNION.

[gtonkin]

Try something like:

Code: Select all

TM1FilterByLevel(Descendants({ [CostCenter].[CCLV1],  [CostCenter].[CCLV5],  [CostCenter].[CCLV7]}),0)

[declanr]

Or to throw in another option:

Code: Select all

{GENERATE({[CostCentre].[BaseSubset]},
{TM1FilterByLevel({TM1DrillDownMember({[CostCentre].CurrentMember},All,Recursive)},0)})}

Where base subset is a subaet containing the 3 elements you mentioned; you can have that one as an MDX subset itself based on any element with an attribute flagged - that way if you need a new element included its simply a case of adding a Y to an attribute.

[initm1]

Thanks this is very helpful.

[pandinus]

This should work too:

Code: Select all

{TM1FILTERBYLEVEL({TM1SUBSETALL( [CostCenter].[CCLV1] )}, 0)}+
{TM1FILTERBYLEVEL({TM1SUBSETALL( [CostCenter].[CCLV5] )}, 0)}+
{TM1FILTERBYLEVEL({TM1SUBSETALL( [CostCenter].[CCLV7] )}, 0)}

[initm1]

All these options works and very helpful. Thanks everyone for helping. Thanks!

[qml]

UNION is costly because it removes duplicates, so I avoid it if there is no need for that (because there are not going to be any duplicates), especially if the dimension is large.

Pandinus's solution is good and simple. Here is another version of it, with a slightly different notation (a set of sets):

Code: Select all

{ {TM1FILTERBYLEVEL({TM1SUBSETALL( [CostCenter].[CCLV1] )}, 0)},
  {TM1FILTERBYLEVEL({TM1SUBSETALL( [CostCenter].[CCLV5] )}, 0)},
  {TM1FILTERBYLEVEL({TM1SUBSETALL( [CostCenter].[CCLV7] )}, 0)} }

[tomok]

UNION is costly because it removes duplicates, so I avoid it if there is no need for that (because there are not going to be any duplicates), especially if the dimension

Agreed but every other solution given will result in duplicates if any leaf elements are present in one or more of the consolidated nodes. I didn't see anything in the original post to indicate one way or the other whether that was true or not.

[blackhawk]

Interesting...not sure why someone didn't mention this earlier, but TM1DrillDownMember takes a set as a parameter, so to drill down on multiple elements, why not just use the set notation?

Code: Select all

TM1FilterByLevel(
    TM1DrilldownMember(
        { [CostCenter].[CCLV1],  [CostCenter].[CCLV5],  [CostCenter].[CCLV7] }, 
        ALL, 
        RECURSIVE 
    ),
    0
)

That said though, it doesn't remove duplicates as mentioned by tomok. Duplicates gets into a problem because for some reason TM1's DISTINCT clause doesn't work as expected.