Tm1 MDX - set element collapse using element name in dimension

Post Reply
kavitha2002
Community Contributor
Posts: 180
Joined: Sat May 05, 2018 11:48 am
OLAP Product: tm1
Version: 10.3.10100.8
Excel Version: 14

Tm1 MDX - set element collapse using element name in dimension

Post by kavitha2002 »

Hi,

I have a quick clarification on Dimension using mdx statement.

Is there a way to collapse and expand a particular element in dimension using mdx statement.

Thanks in advance.
User avatar
orlando
Community Contributor
Posts: 167
Joined: Fri Aug 04, 2017 8:27 am
OLAP Product: TM1
Version: PAL 2.0.8
Excel Version: Office 365

Re: Tm1 MDX - set element collapse using element name in dimension

Post by orlando »

this guide will help you http://www.bihints.com/book/export/html/68

regards, orlando
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: Tm1 MDX - set element collapse using element name in dimension

Post by PavoGa »

If I understand your question correctly, then this:

Code: Select all

{[dimname].[element]}
returns your "collapsed" member. This:

Code: Select all

TM1DrillDownMember({[dimname].[element]}, ALL, RECURSIVE)
will return your consolidation expanded to all its descendants.
Ty
Cleveland, TN
kavitha2002
Community Contributor
Posts: 180
Joined: Sat May 05, 2018 11:48 am
OLAP Product: tm1
Version: 10.3.10100.8
Excel Version: 14

Re: Tm1 MDX - set element collapse using element name in dimension

Post by kavitha2002 »

Thanks @PavoGa

But, I would like to pass the element to drill down the particular element in dimension and save to subset or dimension itself.

Example;

A1 Consolidated
A2 Consolidated
A3 Consolidated

When i passs A2 drilldown, then the result should be

A1
A2
.... A21
.... A22
.....A23
A3

and save it in subset or dimension.

I too have used
TM1DRILLDOWNMEMBER( {[dimname].[element]}, ALL ) - return only those element
TM1SUBSETALL( [dimname] ), ALL - returns all.

but it doesnt work as i expect. Is there a way to combine this above two and get the result???
User avatar
orlando
Community Contributor
Posts: 167
Joined: Fri Aug 04, 2017 8:27 am
OLAP Product: TM1
Version: PAL 2.0.8
Excel Version: Office 365

Re: Tm1 MDX - set element collapse using element name in dimension

Post by orlando »

kavitha2002 wrote: Tue Sep 11, 2018 12:57 pm Thanks @PavoGa

But, I would like to pass the element to drill down the particular element in dimension and save to subset or dimension itself.

Example;

A1 Consolidated
A2 Consolidated
A3 Consolidated

When i passs A2 drilldown, then the result should be

A1
A2
.... A21
.... A22
.....A23
A3

and save it in subset or dimension.

I too have used
TM1DRILLDOWNMEMBER( {[dimname].[element]}, ALL ) - return only those element
TM1SUBSETALL( [dimname] ), ALL - returns all.

but it doesnt work as i expect. Is there a way to combine this above two and get the result???
Did you tried "ToggleDrillState" as described in the guide?
kavitha2002
Community Contributor
Posts: 180
Joined: Sat May 05, 2018 11:48 am
OLAP Product: tm1
Version: 10.3.10100.8
Excel Version: 14

Re: Tm1 MDX - set element collapse using element name in dimension

Post by kavitha2002 »

Hi,

I tried the TOGGLEDRILLSTATE but it doesnt work as I expect.

Example its a dimension original state of elements

A0
....A1
............A11
............A12
....A2
........... A21
........... A22
........... A23
....A3
....A4
B0
....B1
....B2

{TOGGLEDRILLSTATE( {[dimname].[A0].Children},{[dimname].[A1]})}
gives the following set

A0
....A1
............A11
............A12
....A2
....A3
....A4

It doesnt give rest of the element of the element B0, B1, B2...


Basically I want to expand the list of dimension elements and need all the elements.

Is it possible???
User avatar
orlando
Community Contributor
Posts: 167
Joined: Fri Aug 04, 2017 8:27 am
OLAP Product: TM1
Version: PAL 2.0.8
Excel Version: Office 365

Re: Tm1 MDX - set element collapse using element name in dimension

Post by orlando »

kavitha2002 wrote: Wed Sep 12, 2018 9:38 pm

It doesnt give rest of the element of the element B0, B1, B2...


Basically I want to expand the list of dimension elements and need all the elements.

Is it possible???
I would try to combine two different MDX with UNION

Like:

Union(
{TOGGLEDRILLSTATE( {[dimname].[A0].Children},{[dimname].[A1]})}
,
{[dimname].[B0].children})

Regards,
orlando
User avatar
Harvey
Community Contributor
Posts: 236
Joined: Mon Aug 04, 2008 4:43 am
OLAP Product: PA, TM1, CX, Palo
Version: TM1 8.3 onwards
Excel Version: 2003 onwards
Contact:

Re: Tm1 MDX - set element collapse using element name in dimension

Post by Harvey »

kavitha2002 wrote: Tue Sep 11, 2018 12:57 pm But, I would like to pass the element to drill down the particular element in dimension and save to subset or dimension itself.
I don't really understand your question, but you're talking about two incompatible concepts here. There is no way to pass anything to a subset or dimension. Subsets don't accept parameters, that's not how they work.

Are you editing the MDX in the Expression Window in the Subset Editor, using it to generate a temporary subset in a TI process, or passing MDX to REST API (and if so, which function)?

If you're using saved subsets to get different views of data, in, say, an Excel Websheet, you can pre-generate multiple subsets, one for each possible state of the data. If you use a smart naming convention (ie, the element you want to drill is part of the name, effectively active as a parameter), sometimes this is enough to get some cool functionality going.

I've done this in scenarios where there are, say, there are only a limited subset of "product attribute"s relevant to a "product type". You could generate subsets on the "product attribute" dimension, one for each "product type" and then select the subset in your SUBNM formula using string concatenation. Each subset would contain just the "product attribute"s valid for the relevant "product type".

Clunky, but it works.
Take your TM1 experience to the next level - TM1Innovators.net
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: Tm1 MDX - set element collapse using element name in dimension

Post by PavoGa »

kavitha2002 wrote: Tue Sep 11, 2018 12:57 pm Thanks @PavoGa

But, I would like to pass the element to drill down the particular element in dimension and save to subset or dimension itself.

Example;

A1 Consolidated
A2 Consolidated
A3 Consolidated

When i passs A2 drilldown, then the result should be

A1
A2
.... A21
.... A22
.....A23
A3

and save it in subset or dimension.

I too have used
TM1DRILLDOWNMEMBER( {[dimname].[element]}, ALL ) - return only those element
TM1SUBSETALL( [dimname] ), ALL - returns all.

but it doesnt work as i expect. Is there a way to combine this above two and get the result???
To make sure I understand, you want to return a subset where only a single element is drilled down on?

Two things, yes you can combine them using UNION. You can also pass in a parameter, do it all the time using the EXPAND function. Now, the problem may be you cannot get the top layer elements in the order you may want because the UNION will append whatever your second set is to the first. HOWEVER, if you are only looking to drill down one member, you can build three subsets and UNION them. One for all the sibling elements before your drill-down element, the drill-down element and all its progeny and then the drill-down element's young siblings (the ones after).

Hope I understand your question. If so, I'll be glad to help you with some code on it if you need it.

And it's Ty... :D
Ty
Cleveland, TN
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: Tm1 MDX - set element collapse using element name in dimension

Post by PavoGa »

One additional thought: may be possible to do this without doing all the UNIONs I was thinking about. Will try it and let you know.
Ty
Cleveland, TN
kavitha2002
Community Contributor
Posts: 180
Joined: Sat May 05, 2018 11:48 am
OLAP Product: tm1
Version: 10.3.10100.8
Excel Version: 14

Re: Tm1 MDX - set element collapse using element name in dimension

Post by kavitha2002 »

Thank you all for your response.

@PavoGa If i understood your reply correctly, I have just shared the sample example, but in real time dimension may have any number of levels, i cant create those many subset rite...

If you have any code samples, kindly share here...

@orlando I wil try your idea..

When I have same element of different parent in dimension, then how will i get to know which element to drilldown..Any idea of how to find the exact element either by level or parent??
Post Reply