Page 1 of 1

MDX in TI to include additional group of the same dimension

Posted: Tue Feb 13, 2018 2:53 pm
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?

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

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

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

Posted: Tue Feb 13, 2018 6:17 pm
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.

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

Posted: Tue Feb 13, 2018 6:19 pm
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.

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

Posted: Tue Feb 13, 2018 6:40 pm
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.

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

Posted: Tue Feb 13, 2018 6:46 pm
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

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

Posted: Wed Feb 14, 2018 2:03 pm
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.

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

Posted: Wed Feb 14, 2018 2:29 pm
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.