MDX in TI to include additional group of the same dimension

Post Reply
kenship
Posts: 81
Joined: Thu May 30, 2013 1:41 pm
OLAP Product: Cognos
Version: TM1 10.2
Excel Version: 2010

MDX in TI to include additional group of the same dimension

Post by kenship » Tue Feb 13, 2018 2:53 pm

Hi,

By help I received here certain months ago I have the following MDX statement to drill down and filter to include only level 0 elements of a certain group in a dimension. It looks like this:

sMDX = '{TM1FILTERBYLEVEL( TM1DRILLDOWNMEMBER( {[' | vDimName | '].[(PROPOSED BUDGET)]}, ALL, RECURSIVE), 0)}';

Dimension = vDimName
Group to include = '(PROPOSED BUDGET)'

Now I require to include one more group in the same dimension
New Group to include = '(RESTATED BUDGET')

After looking up some reference I believe I need to add "TM1DRILLDOWNMEMBER" and include
",{[' | vDimName | '].[(RESTATED BUDGET)]}". My new statement looks like the following:

sMDX = '{TM1FILTERBYLEVEL( TM1DRILLDOWNMEMBER (TM1DRILLDOWNMEMBER {[' | vDimName | '].[(PROPOSED BUDGET)]},{[' | vDimName | '].[(RESTATED BUDGET)]}, ALL, RECURSIVE), 0)}';

But it doesn't work.

May I ask where I did wrong?

User avatar
tomok
MVP
Posts: 2378
Joined: Tue Feb 16, 2010 2:39 pm
OLAP Product: TM1, Palo
Version: Beginning of time thru 10.2
Excel Version: 2003-2007-2010-2013
Location: Atlanta, GA
Contact:

Re: MDX in TI to include additional group of the same dimension

Post by tomok » Tue Feb 13, 2018 4:45 pm

The MDX function is UNION. You want to UNION your two results together.
Tom O'Kelley - Manager Finance Systems
American Tower
http://www.onlinecourtreservations.com/

kenship
Posts: 81
Joined: Thu May 30, 2013 1:41 pm
OLAP Product: Cognos
Version: TM1 10.2
Excel Version: 2010

Re: MDX in TI to include additional group of the same dimension

Post by kenship » Tue Feb 13, 2018 6:17 pm

Read about it then forgot.

Thanks a lot! Will try.

Kenneth
tomok wrote:
Tue Feb 13, 2018 4:45 pm
The MDX function is UNION. You want to UNION your two results together.

User avatar
gtonkin
MVP
Posts: 563
Joined: Thu May 06, 2010 3:03 pm
OLAP Product: TM1
Version: PAL 2.0.1
Excel Version: 2016 64-bit
Location: JHB, South Africa
Contact:

Re: MDX in TI to include additional group of the same dimension

Post by gtonkin » Tue Feb 13, 2018 6:19 pm

kenship wrote:
Tue Feb 13, 2018 2:53 pm
sMDX = '{TM1FILTERBYLEVEL( TM1DRILLDOWNMEMBER (TM1DRILLDOWNMEMBER {[' | vDimName | '].[(PROPOSED BUDGET)]},{[' | vDimName | '].[(RESTATED BUDGET)]}, ALL, RECURSIVE), 0)}';
..
Think you were almost there, just duplicated the TM1DRILLDOWNMEMBER( and mixed some brackets- try:

Code: Select all

sMDX = '{TM1FILTERBYLEVEL( {TM1DRILLDOWNMEMBER({[' | vDimName | '].[(PROPOSED BUDGET)],[' | vDimName | '].[(RESTATED BUDGET)]}, ALL, RECURSIVE)}, 0)}';
Left the round brackets around Proposed Budget and Restated Budget but if your element names do not contain these, remove them.
HTH.

kenship
Posts: 81
Joined: Thu May 30, 2013 1:41 pm
OLAP Product: Cognos
Version: TM1 10.2
Excel Version: 2010

Re: MDX in TI to include additional group of the same dimension

Post by kenship » Tue Feb 13, 2018 6:40 pm

It works, Thanks!

May I ask when curly bracket is needed?

Kenneth
gtonkin wrote:
Tue Feb 13, 2018 6:19 pm
kenship wrote:
Tue Feb 13, 2018 2:53 pm
sMDX = '{TM1FILTERBYLEVEL( TM1DRILLDOWNMEMBER (TM1DRILLDOWNMEMBER {[' | vDimName | '].[(PROPOSED BUDGET)]},{[' | vDimName | '].[(RESTATED BUDGET)]}, ALL, RECURSIVE), 0)}';
..
Think you were almost there, just duplicated the TM1DRILLDOWNMEMBER( and mixed some brackets- try:

Code: Select all

sMDX = '{TM1FILTERBYLEVEL( {TM1DRILLDOWNMEMBER({[' | vDimName | '].[(PROPOSED BUDGET)],[' | vDimName | '].[(RESTATED BUDGET)]}, ALL, RECURSIVE)}, 0)}';
Left the round brackets around Proposed Budget and Restated Budget but if your element names do not contain these, remove them.
HTH.

User avatar
gtonkin
MVP
Posts: 563
Joined: Thu May 06, 2010 3:03 pm
OLAP Product: TM1
Version: PAL 2.0.1
Excel Version: 2016 64-bit
Location: JHB, South Africa
Contact:

Re: MDX in TI to include additional group of the same dimension

Post by gtonkin » Tue Feb 13, 2018 6:46 pm

Generally the brackets are used to enclose a set. I have had varied experiences when using or omitting them. I try use them when expecting multiple elements being returned.
Maybe someone has a more technical answer to your question though.

Have a look at this link: https://mobile.databasejournal.com/feat ... Basics.htm

User avatar
PavoGa
Community Contributor
Posts: 158
Joined: Thu Apr 18, 2013 6:59 pm
OLAP Product: TM1
Version: 10.2.2 fixpack 2 & 7
Excel Version: 2013
Location: Cleveland, Tennessee

Re: MDX in TI to include additional group of the same dimension

Post by PavoGa » Wed Feb 14, 2018 2:03 pm

kenship wrote:
Tue Feb 13, 2018 6:40 pm
It works, Thanks!

May I ask when curly bracket is needed?

Kenneth


When the MDX function/property returns a member, not a set, the curly bracket defines it as a set. In your case with the TM1drilldownmember, it operates on each member of a set, so it requires a set definition. If an MDX function or property (dimname.members) returns a set, the curly brackets are not required.

So in:
TM1DrillDownMember({[dim1].[member], [dim1],[member2]}, all, recursive) tells the function to drill down on each specified member.

TM1DrillDownMember( TM1FILTERBYLEVEL( TM1SUBSETALL( [Organization] ), 4), ALL, RECURSIVE) does not require any curly brackets.
Ty
Cleveland, TN

kenship
Posts: 81
Joined: Thu May 30, 2013 1:41 pm
OLAP Product: Cognos
Version: TM1 10.2
Excel Version: 2010

Re: MDX in TI to include additional group of the same dimension

Post by kenship » Wed Feb 14, 2018 2:29 pm

Thanks for the explanation!
PavoGa wrote:
Wed Feb 14, 2018 2:03 pm
kenship wrote:
Tue Feb 13, 2018 6:40 pm
It works, Thanks!

May I ask when curly bracket is needed?

Kenneth


When the MDX function/property returns a member, not a set, the curly bracket defines it as a set. In your case with the TM1drilldownmember, it operates on each member of a set, so it requires a set definition. If an MDX function or property (dimname.members) returns a set, the curly brackets are not required.

So in:
TM1DrillDownMember({[dim1].[member], [dim1],[member2]}, all, recursive) tells the function to drill down on each specified member.

TM1DrillDownMember( TM1FILTERBYLEVEL( TM1SUBSETALL( [Organization] ), 4), ALL, RECURSIVE) does not require any curly brackets.

Post Reply