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?
MDX in TI to include additional group of the same dimension
-
- MVP
- Posts: 2832
- 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
The MDX function is UNION. You want to UNION your two results together.
- gtonkin
- MVP
- Posts: 1202
- 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
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)}';
HTH.
-
- 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
It works, Thanks!
May I ask when curly bracket is needed?
Kenneth
May I ask when curly bracket is needed?
Kenneth
gtonkin wrote: ↑Tue Feb 13, 2018 6:19 pmThink you were almost there, just duplicated the TM1DRILLDOWNMEMBER( and mixed some brackets- try:Left the round brackets around Proposed Budget and Restated Budget but if your element names do not contain these, remove them.Code: Select all
sMDX = '{TM1FILTERBYLEVEL( {TM1DRILLDOWNMEMBER({[' | vDimName | '].[(PROPOSED BUDGET)],[' | vDimName | '].[(RESTATED BUDGET)]}, ALL, RECURSIVE)}, 0)}';
HTH.
- gtonkin
- MVP
- Posts: 1202
- 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
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
Maybe someone has a more technical answer to your question though.
Have a look at this link: https://mobile.databasejournal.com/feat ... Basics.htm
- PavoGa
- MVP
- Posts: 617
- 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
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
Cleveland, TN
-
- 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
Thanks for the explanation!
PavoGa wrote: ↑Wed Feb 14, 2018 2:03 pm
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.