Page 1 of 1
MDX to drill/get decendents of multiple consolidated elements
Posted: Mon Aug 01, 2016 5:30 pm
by 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.
Re: MDX to drill/get decendents of multiple consolidated elements
Posted: Mon Aug 01, 2016 6:32 pm
by tomok
UNION.
Re: MDX to drill/get decendents of multiple consolidated elements
Posted: Mon Aug 01, 2016 7:11 pm
by gtonkin
Try something like:
Code: Select all
TM1FilterByLevel(Descendants({ [CostCenter].[CCLV1], [CostCenter].[CCLV5], [CostCenter].[CCLV7]}),0)
Re: MDX to drill/get decendents of multiple consolidated elements
Posted: Mon Aug 01, 2016 8:42 pm
by 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.
Re: MDX to drill/get decendents of multiple consolidated elements
Posted: Tue Aug 02, 2016 7:24 am
by initm1
Thanks this is very helpful.
Re: MDX to drill/get decendents of multiple consolidated elements
Posted: Tue Aug 02, 2016 8:36 am
by pandinus
This should work too:
Code: Select all
{TM1FILTERBYLEVEL({TM1SUBSETALL( [CostCenter].[CCLV1] )}, 0)}+
{TM1FILTERBYLEVEL({TM1SUBSETALL( [CostCenter].[CCLV5] )}, 0)}+
{TM1FILTERBYLEVEL({TM1SUBSETALL( [CostCenter].[CCLV7] )}, 0)}
Re: MDX to drill/get decendents of multiple consolidated elements
Posted: Tue Aug 02, 2016 2:50 pm
by initm1
All these options works and very helpful. Thanks everyone for helping. Thanks!
Re: MDX to drill/get decendents of multiple consolidated elements
Posted: Tue Aug 02, 2016 3:05 pm
by 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)} }
Re: MDX to drill/get decendents of multiple consolidated elements
Posted: Tue Aug 02, 2016 4:01 pm
by tomok
qml wrote: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.
Re: MDX to drill/get decendents of multiple consolidated elements
Posted: Thu Aug 04, 2016 12:26 am
by 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.