Create a drillable consolidator with only leaf level children

Post Reply
JamiseBondi
Posts: 141
Joined: Wed Nov 14, 2012 10:37 am
OLAP Product: TM1
Version: 2.0
Excel Version: Office 365

Create a drillable consolidator with only leaf level children

Post by JamiseBondi »

Hi guys,

I'm trying to create a dynamic subset that shows a collapsed consolidated node that when expanded, only shows the leaf level children (not any consolidated children). Does anyone have any hints? I've gone through the "MDX a primer" site (http://www.bihints.com/book/export/html/68) and been searching on TM1 reference guide but haven't found anything that does the trick yet.
Many thanks.
Wim Gielis
MVP
Posts: 3103
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: Create a drillable consolidator with only leaf level children

Post by Wim Gielis »

JamiseBondi wrote: Tue Oct 24, 2017 7:52 am Hi guys,

I'm trying to create a dynamic subset that shows a collapsed consolidated node that when expanded, only shows the leaf level children (not any consolidated children). Does anyone have any hints? I've gone through the "MDX a primer" site (http://www.bihints.com/book/export/html/68) and been searching on TM1 reference guide but haven't found anything that does the trick yet.
Many thanks.
Hi

That cannot be done natively.
You need to set up a parallel hierarchy in the same dimension.
Either with the new hierarchies feature in TM1, either using a TI process (or manual copy/paste) to set up and maintain the parallel hierarchy.
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
JamiseBondi
Posts: 141
Joined: Wed Nov 14, 2012 10:37 am
OLAP Product: TM1
Version: 2.0
Excel Version: Office 365

Re: Create a drillable consolidator with only leaf level children

Post by JamiseBondi »

Thanks Wim for confirming! much appreciated.
lotsaram
MVP
Posts: 3651
Joined: Fri Mar 13, 2009 11:14 am
OLAP Product: TableManager1
Version: PA 2.0.x
Excel Version: Office 365
Location: Switzerland

Re: Create a drillable consolidator with only leaf level children

Post by lotsaram »

JamiseBondi wrote: Tue Oct 24, 2017 7:52 am I'm trying to create a dynamic subset that shows a collapsed consolidated node that when expanded, only shows the leaf level children (not any consolidated children). Does anyone have any hints?
You could achieve this with separate subsets (e.g. a "summary" subset and a "drilled" subset) but it isn't achievable by clicking on the "+" to expand a consolidated element. The moment you expand an element you are no longer looking at your original subset and you are drilling into the live dimension structure and you will obtain the children of the consolidation. If you want to skip all intermediate levels and go straight from a consolidation to the leaf descendants then you would need to define an alternate (flat) rollup to expand into.
Please place all requests for help in a public thread. I will not answer PMs requesting assistance.
JamiseBondi
Posts: 141
Joined: Wed Nov 14, 2012 10:37 am
OLAP Product: TM1
Version: 2.0
Excel Version: Office 365

Re: Create a drillable consolidator with only leaf level children

Post by JamiseBondi »

Thanks lotsaram, very helpful, much appreciated.
tm123
Posts: 132
Joined: Thu Oct 23, 2014 10:15 pm
OLAP Product: tm1, cognos bi
Version: 10.2
Excel Version: 2010

Re: Create a drillable consolidator with only leaf level children

Post by tm123 »

The workaround i use is, in the websheet, i use checkbox “Drill to Leaf Elements” and then i create a dynamic MDX statement to be used in the TM1RPTRow, so if the user selects the CheckBox, the Active form shows the Dimension Total element and Leaf Elements, otherwise the active form shows the total elemny only

Toggling the checkbox, requires the websheet to be rebuild so i use an action button beside the checkbox
Post Reply