MDX for getting Parent and Children

Post Reply
User avatar
macsir
MVP
Posts: 782
Joined: Wed May 30, 2012 6:50 am
OLAP Product: TM1
Version: PAL 2.0.9
Excel Version: Office 365
Contact:

MDX for getting Parent and Children

Post by macsir »

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 9776 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 9776 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
MVP
Posts: 782
Joined: Wed May 30, 2012 6:50 am
OLAP Product: TM1
Version: PAL 2.0.9
Excel Version: Office 365
Contact:

Re: MDX for getting Parent and Children

Post by macsir »

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 »

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
MVP
Posts: 782
Joined: Wed May 30, 2012 6:50 am
OLAP Product: TM1
Version: PAL 2.0.9
Excel Version: Office 365
Contact:

Re: MDX for getting Parent and Children

Post by macsir »

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: 3105
Joined: Mon Dec 29, 2008 6:26 pm
OLAP Product: TM1, Jedox
Version: PAL 2.0.9.18
Excel Version: Microsoft 365
Location: Brussels, Belgium
Contact:

Re: MDX for getting Parent and Children

Post by Wim Gielis »

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

IBM Champion 2024
Excel Most Valuable Professional, 2011-2014
https://www.wimgielis.com ==> 121 TM1 articles and a lot of custom code
Newest blog article: Deleting elements quickly
User avatar
PavoGa
MVP
Posts: 616
Joined: Thu Apr 18, 2013 6:59 pm
OLAP Product: TM1
Version: 10.2.2 FP7, PA2.0.9.1
Excel Version: 2013 PAW
Location: Charleston, Tennessee

Re: MDX for getting Parent and Children

Post by PavoGa »

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: 2410
Joined: Wed May 14, 2008 4:25 pm
OLAP Product: TM1
Version: TM1 v6,v7,v8,v9,v10,v11+PAW
Excel Version: Nearly all of them

Re: MDX for getting Parent and Children

Post by Steve Rowe »

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...
Technical Director
www.infocat.co.uk
User avatar
macsir
MVP
Posts: 782
Joined: Wed May 30, 2012 6:50 am
OLAP Product: TM1
Version: PAL 2.0.9
Excel Version: Office 365
Contact:

Re: MDX for getting Parent and Children

Post by macsir »

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 9698 times
After MDX
h2.PNG
h2.PNG (5.52 KiB) Viewed 9698 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
MVP
Posts: 616
Joined: Thu Apr 18, 2013 6:59 pm
OLAP Product: TM1
Version: 10.2.2 FP7, PA2.0.9.1
Excel Version: 2013 PAW
Location: Charleston, Tennessee

Re: MDX for getting Parent and Children

Post by PavoGa »

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: 3105
Joined: Mon Dec 29, 2008 6:26 pm
OLAP Product: TM1, Jedox
Version: PAL 2.0.9.18
Excel Version: Microsoft 365
Location: Brussels, Belgium
Contact:

Re: MDX for getting Parent and Children

Post by Wim Gielis »

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

IBM Champion 2024
Excel Most Valuable Professional, 2011-2014
https://www.wimgielis.com ==> 121 TM1 articles and a lot of custom code
Newest blog article: Deleting elements quickly
User avatar
PavoGa
MVP
Posts: 616
Joined: Thu Apr 18, 2013 6:59 pm
OLAP Product: TM1
Version: 10.2.2 FP7, PA2.0.9.1
Excel Version: 2013 PAW
Location: Charleston, Tennessee

Re: MDX for getting Parent and Children

Post by PavoGa »

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
MVP
Posts: 782
Joined: Wed May 30, 2012 6:50 am
OLAP Product: TM1
Version: PAL 2.0.9
Excel Version: Office 365
Contact:

Re: MDX for getting Parent and Children

Post by macsir »

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 9658 times
And the whole structure is like this,
22.PNG
22.PNG (2.83 KiB) Viewed 9658 times
In TM1,the answer is always yes though sometimes with a but....
http://tm1sir.blogspot.com.au/
User avatar
macsir
MVP
Posts: 782
Joined: Wed May 30, 2012 6:50 am
OLAP Product: TM1
Version: PAL 2.0.9
Excel Version: Office 365
Contact:

Re: MDX for getting Parent and Children

Post by macsir »

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 9658 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 9658 times
55.PNG
55.PNG (3.38 KiB) Viewed 9658 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
MVP
Posts: 782
Joined: Wed May 30, 2012 6:50 am
OLAP Product: TM1
Version: PAL 2.0.9
Excel Version: Office 365
Contact:

Re: MDX for getting Parent and Children

Post by macsir »

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 9653 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 9653 times
333.PNG
333.PNG (10.03 KiB) Viewed 9653 times
In TM1,the answer is always yes though sometimes with a but....
http://tm1sir.blogspot.com.au/
User avatar
macsir
MVP
Posts: 782
Joined: Wed May 30, 2012 6:50 am
OLAP Product: TM1
Version: PAL 2.0.9
Excel Version: Office 365
Contact:

Re: MDX for getting Parent and Children

Post by macsir »

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 9653 times
2222.PNG
2222.PNG (5.91 KiB) Viewed 9653 times
3333.PNG
3333.PNG (13.58 KiB) Viewed 9653 times
In TM1,the answer is always yes though sometimes with a but....
http://tm1sir.blogspot.com.au/
User avatar
PavoGa
MVP
Posts: 616
Joined: Thu Apr 18, 2013 6:59 pm
OLAP Product: TM1
Version: 10.2.2 FP7, PA2.0.9.1
Excel Version: 2013 PAW
Location: Charleston, Tennessee

Re: MDX for getting Parent and Children

Post by PavoGa »

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
MVP
Posts: 782
Joined: Wed May 30, 2012 6:50 am
OLAP Product: TM1
Version: PAL 2.0.9
Excel Version: Office 365
Contact:

Re: MDX for getting Parent and Children

Post by macsir »

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: 3105
Joined: Mon Dec 29, 2008 6:26 pm
OLAP Product: TM1, Jedox
Version: PAL 2.0.9.18
Excel Version: Microsoft 365
Location: Brussels, Belgium
Contact:

Re: MDX for getting Parent and Children

Post by Wim Gielis »

Or, shorter:

Code: Select all

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

Wim Gielis

IBM Champion 2024
Excel Most Valuable Professional, 2011-2014
https://www.wimgielis.com ==> 121 TM1 articles and a lot of custom code
Newest blog article: Deleting elements quickly
Post Reply