TM1 Forum

Posted: **Thu Jan 31, 2019 4:31 am**

Hi, not sure if it is bug or not. In a dimension, when I attache the MDX for getting Parent and its direct children, it works fine.

Code: Select all

{ DRILLDOWNMEMBER( {TM1FILTERBYPATTERN( {TM1SUBSETALL( [DimName] )}, "Consolidation1")}, { [DimName].[Consolidation1] } ) }

: 1.PNG (1.75 KiB) Viewed 12934 times

But if I switch to another parent in the same dimension, it just shows the parent only although it has direct children. It is still in collapsed way.

Code: Select all

{ DRILLDOWNMEMBER( {TM1FILTERBYPATTERN( {TM1SUBSETALL( [DimName] )}, "Consolidation2")}, { [DimName].[Consolidation2] } ) }

: 2.PNG (1.02 KiB) Viewed 12934 times

Not sure why DRILLDOWNMEMBER works differently? I changed to TM1DRILLDOWNMEMBER, it is still the same behavior and same in PA.

Posted: **Thu Jan 31, 2019 4:43 am**

And I don't see any difference between consolidation1 and consolidation2. That's weird.

Posted: **Thu Jan 31, 2019 4:55 am**

Doesn't seem to behave differently. Though it might be worth trying:

{ [DimName].[Consol1], [DimName].[Consol1].Children }
{ [DimName].[Consol2], [DimName].[Consol2].Children }

rather than filtering by pattern.

Posted: **Thu Jan 31, 2019 5:45 am**

jrizk wrote: ↑Thu Jan 31, 2019 4:55 am Doesn't seem to behave differently. Though it might be worth trying:

{ [DimName].[Consol1], [DimName].[Consol1].Children }
{ [DimName].[Consol2], [DimName].[Consol2].Children }

rather than filtering by pattern.

Thanks, yes, that's exactly what I did after failure on filtering by pattern. But just want to know why.

Posted: **Thu Jan 31, 2019 5:41 pm**

macsir wrote: ↑Thu Jan 31, 2019 5:45 am
jrizk wrote: ↑Thu Jan 31, 2019 4:55 am Doesn't seem to behave differently. Though it might be worth trying:

{ [DimName].[Consol1], [DimName].[Consol1].Children }
{ [DimName].[Consol2], [DimName].[Consol2].Children }

rather than filtering by pattern.
Thanks, yes, that's exactly what I did after failure on filtering by pattern. But just want to know why.

Hi

Can you add the dim file, change the element names for privacy, as long as the issue persists.

Posted: **Thu Jan 31, 2019 6:58 pm**

I always use TM1DRILLDOWNMEMBER with the ALL and RECURSIVE arguments. Is there a reason for not using that function vs. DRILLDOWNMEMBER?

Posted: **Thu Jan 31, 2019 10:04 pm**

I think I vaguely remember a tech note from an age ago to the effect that DRILLDOWNMEMBER should not be used and the TM1 specific variant should be or perhaps it was the "solution" to a defect that was raised...

Posted: **Mon Feb 04, 2019 12:50 am**

Hi, after testing around, I think I can replicate the issue.
Very simple, as long as the parent exists in multiple rollups and one of grandparent has "&" in between, then it won't show the children of the parent.
See the snapshot,
Dim structure

: h1.PNG (5.65 KiB) Viewed 12856 times

After MDX

: h2.PNG (5.52 KiB) Viewed 12856 times

No difference for using TM1DRILLDOWNMEMBER or DRILLDOWNMEMBER.
To me, those two functions have no difference so far based on my experience.

Posted: **Mon Feb 04, 2019 2:49 pm**

macsir wrote: ↑Mon Feb 04, 2019 12:50 am Hi, after testing around, I think I can replicate the issue.
Very simple, as long as the parent exists in multiple rollups and one of grandparent has "&" in between, then it won't show the children of the parent.
No difference for using TM1DRILLDOWNMEMBER or DRILLDOWNMEMBER.
To me, those two functions have no difference so far based on my experience.

The TM1DRILLDOWNMEMBER does not return the desired subset with that syntax regardless of whether there is an ampersand or not in the grandparents' name.

This does return the children however:

Code: Select all

TM1DRILLDOWNMEMBER( 
    TM1FILTERBYPATTERN( 
        TM1SUBSETALL( [test2] ), 'c'), ALL, RECURSIVE)

and this returns the exact same subset of elements:
        
TM1DRILLDOWNMEMBER( {[test2].[c]}, ALL, RECURSIVE)

Posted: **Mon Feb 04, 2019 3:13 pm**

I never understood the use of a TM1FILTERBYPATTERN and then a pattern without wildcards. We can reference the element in a direct way, can't we ?

Posted: **Mon Feb 04, 2019 4:52 pm**

Wim Gielis wrote: ↑Mon Feb 04, 2019 3:13 pm I never understood the use of a TM1FILTERBYPATTERN and then a pattern without wildcards. We can reference the element in a direct way, can't we ?

Agree, that does not make a lot of sense to me, either.

I never use TM1FILTERBYPATTERN as a general rule within the context of TM1DRILLDOWNMEMBER, but could see using it to return a set of like elements to drill down upon. Otherwise, it seems a waste.

Posted: **Tue Feb 05, 2019 12:56 am**

Thanks. OK, my original purpose is to use TM1DRILLDOWNMEMBER to get just one level down from that parent, which is this one (add another child for d). See below,

: 11.PNG (867 Bytes) Viewed 12816 times

And the whole structure is like this,

: 22.PNG (2.83 KiB) Viewed 12816 times

Posted: **Tue Feb 05, 2019 1:00 am**

The TM1FILTERBYPATTERN function here gives me the right result after adding asterisk in it but still not give me right result after combining with TM1DRILLDOWNMEMBER funtion.

: 33.PNG (4.51 KiB) Viewed 12816 times

Literally, the following two are the same things,

Code: Select all

{ [test2].[c] }

But this gives me the right result all the time.

Code: Select all

{TM1FILTERBYPATTERN( {TM1SUBSETALL( [test2] )}, "c*")}

: 44.PNG (4.13 KiB) Viewed 12816 times

: 55.PNG (3.38 KiB) Viewed 12816 times

Anyway, it is not a big issue and there are a number of ways to avoid it. I am just curious to know why.

Posted: **Tue Feb 05, 2019 1:23 am**

I think I found the root cause of this different behavior. Forget about the ampersand in the parent.
It is just collapsed/expanded status of [c] in the dimension that impacts the different behavior of TM1FILTERBYPATTERN function.
In multiple rollups for [c] in the dimension, TM1FILTERBYPATTERN function just look up for the first appearance of the [c], if that [c] is expanded, then TM1DRILLDOWNMEMBER function can show the expanded status as well. if it is collapsed, then collapsed also.
See the difference below,
0. The whole structure now,

: 111.PNG (3.15 KiB) Viewed 12811 times

1. After show all, the first [c] in [ba] is collapsed and the second [c] is expanded.

: 222.PNG (5.86 KiB) Viewed 12811 times

: 333.PNG (10.03 KiB) Viewed 12811 times

Posted: **Tue Feb 05, 2019 1:25 am**

2. I change the status in the dimension editor to let the first [c] in [ba] is expanded and the second [c] is collapsed for show all button.

: 1111.PNG (6.15 KiB) Viewed 12811 times

: 2222.PNG (5.91 KiB) Viewed 12811 times

: 3333.PNG (13.58 KiB) Viewed 12811 times

Posted: **Tue Feb 05, 2019 1:32 pm**

This will get you the one level down including the target:

Code: Select all

TM1DRILLDOWNMEMBER( {[test2].[c]}, ALL)

will return c and d only.

Posted: **Tue Feb 05, 2019 8:45 pm**

PavoGa wrote: ↑Tue Feb 05, 2019 1:32 pm This will get you the one level down including the target:
Code: Select all
TM1DRILLDOWNMEMBER( {[test2].[c]}, ALL)
will return c and d only.

Thanks

Posted: **Tue Feb 05, 2019 9:02 pm**

Or, shorter:

Code: Select all

DrilldownLevel( {[test2].[c]} )

TM1 Forum

MDX for getting Parent and Children

MDX for getting Parent and Children

Re: MDX for getting Parent and Children

Re: MDX for getting Parent and Children

Re: MDX for getting Parent and Children

Re: MDX for getting Parent and Children

Re: MDX for getting Parent and Children

Re: MDX for getting Parent and Children

Re: MDX for getting Parent and Children

Re: MDX for getting Parent and Children

Re: MDX for getting Parent and Children

Re: MDX for getting Parent and Children

Re: MDX for getting Parent and Children

Re: MDX for getting Parent and Children

Re: MDX for getting Parent and Children

Re: MDX for getting Parent and Children

Re: MDX for getting Parent and Children

Re: MDX for getting Parent and Children

Re: MDX for getting Parent and Children