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.