MDX in TI to include additional group of the same dimension

Post Reply
kenship
Regular Participant
Posts: 194
Joined: Thu May 30, 2013 1:41 pm
OLAP Product: Cognos
Version: Planning Analytics 2.0.7
Excel Version: 2010

MDX in TI to include additional group of the same dimension

Post by kenship »

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?
tomok
MVP
Posts: 2831
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 »

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
Regular Participant
Posts: 194
Joined: Thu May 30, 2013 1:41 pm
OLAP Product: Cognos
Version: Planning Analytics 2.0.7
Excel Version: 2010

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

Post by kenship »

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: 1192
Joined: Thu May 06, 2010 3:03 pm
OLAP Product: TM1
Version: Latest and greatest
Excel Version: Office 365 64-bit
Location: JHB, South Africa
Contact:

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

Post by gtonkin »

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
Regular Participant
Posts: 194
Joined: Thu May 30, 2013 1:41 pm
OLAP Product: Cognos
Version: Planning Analytics 2.0.7
Excel Version: 2010

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

Post by kenship »

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: 1192
Joined: Thu May 06, 2010 3:03 pm
OLAP Product: TM1
Version: Latest and greatest
Excel Version: Office 365 64-bit
Location: JHB, South Africa
Contact:

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

Post by gtonkin »

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
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 in TI to include additional group of the same dimension

Post by PavoGa »

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 braces define 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 member of the set designated by the {} braces.

TM1DrillDownMember( TM1FILTERBYLEVEL( TM1SUBSETALL( [Organization] ), 4), ALL, RECURSIVE) does not require the braces.
Last edited by PavoGa on Tue Feb 20, 2018 1:10 pm, edited 2 times in total.
Ty
Cleveland, TN
kenship
Regular Participant
Posts: 194
Joined: Thu May 30, 2013 1:41 pm
OLAP Product: Cognos
Version: Planning Analytics 2.0.7
Excel Version: 2010

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

Post by kenship »

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