Tm1 MDX - set element collapse using element name in dimension

Post Reply
kavitha2002
Posts: 95
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 » Tue Sep 11, 2018 9:00 am

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
Posts: 52
Joined: Fri Aug 04, 2017 8:27 am
OLAP Product: TM1
Version: PAL 2.0
Excel Version: Office 365

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

Post by orlando » Tue Sep 11, 2018 9:20 am

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

regards, orlando

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

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

Post by PavoGa » Tue Sep 11, 2018 12:09 pm

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
Posts: 95
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 » 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???

User avatar
orlando
Posts: 52
Joined: Fri Aug 04, 2017 8:27 am
OLAP Product: TM1
Version: PAL 2.0
Excel Version: Office 365

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

Post by orlando » Tue Sep 11, 2018 1:52 pm

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
Posts: 95
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 » Wed Sep 12, 2018 9:38 pm

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
Posts: 52
Joined: Fri Aug 04, 2017 8:27 am
OLAP Product: TM1
Version: PAL 2.0
Excel Version: Office 365

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

Post by orlando » Thu Sep 13, 2018 7:29 am

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: 218
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 » Thu Sep 13, 2018 8:25 am

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.
Flow OLAP - thinking outside the cube.

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

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

Post by PavoGa » Thu Sep 13, 2018 2:02 pm

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
Community Contributor
Posts: 264
Joined: Thu Apr 18, 2013 6:59 pm
OLAP Product: TM1
Version: 10.2.2 FP7, PA2.0
Excel Version: 2013
Location: Cleveland, Tennessee

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

Post by PavoGa » Thu Sep 13, 2018 2:05 pm

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
Posts: 95
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 » Wed Sep 19, 2018 2:27 pm

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