MDX for getting Parent and Children

Post Reply
User avatar
macsir
Community Contributor
Posts: 594
Joined: Wed May 30, 2012 6:50 am
OLAP Product: TM1, SSAS
Version: 9.5 - 10.2
Excel Version: 97 - 2013
Contact:

MDX for getting Parent and Children

Post by macsir » 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.PNG (1.75 KiB) Viewed 390 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
2.PNG (1.02 KiB) Viewed 390 times
Not sure why DRILLDOWNMEMBER works differently? I changed to TM1DRILLDOWNMEMBER, it is still the same behavior and same in PA.
In TM1,the answer is always yes though sometimes with a but....
http://tm1sir.blogspot.com.au/

User avatar
macsir
Community Contributor
Posts: 594
Joined: Wed May 30, 2012 6:50 am
OLAP Product: TM1, SSAS
Version: 9.5 - 10.2
Excel Version: 97 - 2013
Contact:

Re: MDX for getting Parent and Children

Post by macsir » Thu Jan 31, 2019 4:43 am

And I don't see any difference between consolidation1 and consolidation2. That's weird.
In TM1,the answer is always yes though sometimes with a but....
http://tm1sir.blogspot.com.au/

jrizk
Posts: 48
Joined: Thu Nov 19, 2009 10:38 pm
OLAP Product: Tm1
Version: 10.2.2
Excel Version: 2010

Re: MDX for getting Parent and Children

Post by jrizk » 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.
J.Rizk
Tm1 for everyone

User avatar
macsir
Community Contributor
Posts: 594
Joined: Wed May 30, 2012 6:50 am
OLAP Product: TM1, SSAS
Version: 9.5 - 10.2
Excel Version: 97 - 2013
Contact:

Re: MDX for getting Parent and Children

Post by macsir » 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. :?
In TM1,the answer is always yes though sometimes with a but....
http://tm1sir.blogspot.com.au/

Wim Gielis
MVP
Posts: 2012
Joined: Mon Dec 29, 2008 6:26 pm
OLAP Product: TM1
Version: PAL 2.0.6
Excel Version: 2016 - Office 365
Location: Brussels, Belgium
Contact:

Re: MDX for getting Parent and Children

Post by Wim Gielis » 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.
Best regards,

Wim Gielis

Excel Most Valuable Professional, 2011-2014
http://www.wimgielis.com ==> 109 TM1 articles and a lot of custom code
Newest blog article: Active form claustrophobia

User avatar
PavoGa
Community Contributor
Posts: 314
Joined: Thu Apr 18, 2013 6:59 pm
OLAP Product: TM1
Version: 10.2.2 FP7, PA2.0.05
Excel Version: 2013 PAW
Location: Cleveland, Tennessee

Re: MDX for getting Parent and Children

Post by PavoGa » 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?
Ty
Cleveland, TN

User avatar
Steve Rowe
Site Admin
Posts: 1884
Joined: Wed May 14, 2008 4:25 pm
OLAP Product: TM1
Version: 10.2.2., PAW
Excel Version: Nearly all of them

Re: MDX for getting Parent and Children

Post by Steve Rowe » 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...

User avatar
macsir
Community Contributor
Posts: 594
Joined: Wed May 30, 2012 6:50 am
OLAP Product: TM1, SSAS
Version: 9.5 - 10.2
Excel Version: 97 - 2013
Contact:

Re: MDX for getting Parent and Children

Post by macsir » 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
h1.PNG (5.65 KiB) Viewed 312 times
After MDX
h2.PNG
h2.PNG (5.52 KiB) Viewed 312 times
No difference for using TM1DRILLDOWNMEMBER or DRILLDOWNMEMBER.
To me, those two functions have no difference so far based on my experience.
In TM1,the answer is always yes though sometimes with a but....
http://tm1sir.blogspot.com.au/

User avatar
PavoGa
Community Contributor
Posts: 314
Joined: Thu Apr 18, 2013 6:59 pm
OLAP Product: TM1
Version: 10.2.2 FP7, PA2.0.05
Excel Version: 2013 PAW
Location: Cleveland, Tennessee

Re: MDX for getting Parent and Children

Post by PavoGa » 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)
Ty
Cleveland, TN

Wim Gielis
MVP
Posts: 2012
Joined: Mon Dec 29, 2008 6:26 pm
OLAP Product: TM1
Version: PAL 2.0.6
Excel Version: 2016 - Office 365
Location: Brussels, Belgium
Contact:

Re: MDX for getting Parent and Children

Post by Wim Gielis » 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 ?
Best regards,

Wim Gielis

Excel Most Valuable Professional, 2011-2014
http://www.wimgielis.com ==> 109 TM1 articles and a lot of custom code
Newest blog article: Active form claustrophobia

User avatar
PavoGa
Community Contributor
Posts: 314
Joined: Thu Apr 18, 2013 6:59 pm
OLAP Product: TM1
Version: 10.2.2 FP7, PA2.0.05
Excel Version: 2013 PAW
Location: Cleveland, Tennessee

Re: MDX for getting Parent and Children

Post by PavoGa » 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.
Ty
Cleveland, TN

User avatar
macsir
Community Contributor
Posts: 594
Joined: Wed May 30, 2012 6:50 am
OLAP Product: TM1, SSAS
Version: 9.5 - 10.2
Excel Version: 97 - 2013
Contact:

Re: MDX for getting Parent and Children

Post by macsir » 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
11.PNG (867 Bytes) Viewed 272 times
And the whole structure is like this,
22.PNG
22.PNG (2.83 KiB) Viewed 272 times
In TM1,the answer is always yes though sometimes with a but....
http://tm1sir.blogspot.com.au/

User avatar
macsir
Community Contributor
Posts: 594
Joined: Wed May 30, 2012 6:50 am
OLAP Product: TM1, SSAS
Version: 9.5 - 10.2
Excel Version: 97 - 2013
Contact:

Re: MDX for getting Parent and Children

Post by macsir » 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
33.PNG (4.51 KiB) Viewed 272 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
44.PNG (4.13 KiB) Viewed 272 times
55.PNG
55.PNG (3.38 KiB) Viewed 272 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.
In TM1,the answer is always yes though sometimes with a but....
http://tm1sir.blogspot.com.au/

User avatar
macsir
Community Contributor
Posts: 594
Joined: Wed May 30, 2012 6:50 am
OLAP Product: TM1, SSAS
Version: 9.5 - 10.2
Excel Version: 97 - 2013
Contact:

Re: MDX for getting Parent and Children

Post by macsir » 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
111.PNG (3.15 KiB) Viewed 267 times
1. After show all, the first [c] in [ba] is collapsed and the second [c] is expanded.
222.PNG
222.PNG (5.86 KiB) Viewed 267 times
333.PNG
333.PNG (10.03 KiB) Viewed 267 times
In TM1,the answer is always yes though sometimes with a but....
http://tm1sir.blogspot.com.au/

User avatar
macsir
Community Contributor
Posts: 594
Joined: Wed May 30, 2012 6:50 am
OLAP Product: TM1, SSAS
Version: 9.5 - 10.2
Excel Version: 97 - 2013
Contact:

Re: MDX for getting Parent and Children

Post by macsir » 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
1111.PNG (6.15 KiB) Viewed 267 times
2222.PNG
2222.PNG (5.91 KiB) Viewed 267 times
3333.PNG
3333.PNG (13.58 KiB) Viewed 267 times
In TM1,the answer is always yes though sometimes with a but....
http://tm1sir.blogspot.com.au/

User avatar
PavoGa
Community Contributor
Posts: 314
Joined: Thu Apr 18, 2013 6:59 pm
OLAP Product: TM1
Version: 10.2.2 FP7, PA2.0.05
Excel Version: 2013 PAW
Location: Cleveland, Tennessee

Re: MDX for getting Parent and Children

Post by PavoGa » 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.
Ty
Cleveland, TN

User avatar
macsir
Community Contributor
Posts: 594
Joined: Wed May 30, 2012 6:50 am
OLAP Product: TM1, SSAS
Version: 9.5 - 10.2
Excel Version: 97 - 2013
Contact:

Re: MDX for getting Parent and Children

Post by macsir » 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 :)
In TM1,the answer is always yes though sometimes with a but....
http://tm1sir.blogspot.com.au/

Wim Gielis
MVP
Posts: 2012
Joined: Mon Dec 29, 2008 6:26 pm
OLAP Product: TM1
Version: PAL 2.0.6
Excel Version: 2016 - Office 365
Location: Brussels, Belgium
Contact:

Re: MDX for getting Parent and Children

Post by Wim Gielis » Tue Feb 05, 2019 9:02 pm

Or, shorter:

Code: Select all

DrilldownLevel( {[test2].[c]} )
Best regards,

Wim Gielis

Excel Most Valuable Professional, 2011-2014
http://www.wimgielis.com ==> 109 TM1 articles and a lot of custom code
Newest blog article: Active form claustrophobia

Post Reply