hi all,
I'm trying to create an active form in Excel. the rows shall be controlled by a MDX expression in the TM1RPTROW function. I wish to use a cell reference in the worksheet as the filtering condition to the MDX expression.
However, i'm not good in writing MDX statements. I tried to copy and paste the MDX expression generated by the Subset Editor (again, i used the Filter by attribute function that yields "{FILTER( {TM1DRILLDOWNMEMBER( {TM1SubsetBasis()}, ALL, RECURSIVE )}, [Product].[Item ID] = "10017")}").
but when i insert it into the TM1RPTROW function, it does not work.
I suppose the expression created by Subset Editor is not compatible with Excel? Please advise. thanks!
Need help on MDX used in TM1RPTROW function
-
- Regular Participant
- Posts: 226
- Joined: Thu Apr 02, 2009 2:51 pm
- OLAP Product: IBM Planning Analytics
- Version: Latest version
- Excel Version: 2003 to 2019
Need help on MDX used in TM1RPTROW function
Planning Analytics latest version, including Cloud
-
- MVP
- Posts: 3651
- Joined: Fri Mar 13, 2009 11:14 am
- OLAP Product: TableManager1
- Version: PA 2.0.x
- Excel Version: Office 365
- Location: Switzerland
Re: Need help on MDX used in TM1RPTROW function
Harry - although you can substitute an MDX expression for a subset argument you don't need to use any MDX in the TM1RptRow formula! Read the manual, ... for what you want to do all you need to do is refer to a dynamic named public subset using your expression and this will do what you need.
Syntax: TM1RptRow(ReportView, Dimension, Subset, SubsetElements, Alias, ExpandAbove, MDXStatement, Indentations, ConsolidationDrilling)
Only the first 3 arguments are required, the rest are optional.
Syntax: TM1RptRow(ReportView, Dimension, Subset, SubsetElements, Alias, ExpandAbove, MDXStatement, Indentations, ConsolidationDrilling)
Only the first 3 arguments are required, the rest are optional.
-
- Regular Participant
- Posts: 226
- Joined: Thu Apr 02, 2009 2:51 pm
- OLAP Product: IBM Planning Analytics
- Version: Latest version
- Excel Version: 2003 to 2019
Re: Need help on MDX used in TM1RPTROW function
Hi,
thanks for the reply. perhaps i did not state my requirements clearly. I understand fully about the syntax of TM1RPTROW and the use of dynamic subsets.
However, due to the number of products which is growing by days in the dimensions, we need to attach an "entity" value as an attribute to the n-elements of the product dim. we can then shorten the Active Form for users base on the selected Entity which will hopefully return a list of products base on their matching attributes.
again, the "entity" value has many different forms and it is concatenated bu Org, BU, Region.... so you see, we are not able to create dynamic subsets. i hope to incorporate MDX into TM1RPTROW so that the product list can be generated dynamically within Excel.
thanks for the reply. perhaps i did not state my requirements clearly. I understand fully about the syntax of TM1RPTROW and the use of dynamic subsets.
However, due to the number of products which is growing by days in the dimensions, we need to attach an "entity" value as an attribute to the n-elements of the product dim. we can then shorten the Active Form for users base on the selected Entity which will hopefully return a list of products base on their matching attributes.
again, the "entity" value has many different forms and it is concatenated bu Org, BU, Region.... so you see, we are not able to create dynamic subsets. i hope to incorporate MDX into TM1RPTROW so that the product list can be generated dynamically within Excel.
Planning Analytics latest version, including Cloud
-
- MVP
- Posts: 3651
- Joined: Fri Mar 13, 2009 11:14 am
- OLAP Product: TableManager1
- Version: PA 2.0.x
- Excel Version: Office 365
- Location: Switzerland
Re: Need help on MDX used in TM1RPTROW function
In that case you could do either one of the following
- rebuild a user based dynamic subset with action button running SUbsetCreateByMDX from the sheet. Call the subset something like }<user>_<report>
OR
- your original approach, following is an easy way to do it.
Your formula should look something like this ....
=TM1RPTROW($B$9,"server:product","Default",,,,sMDX)
sMDX is a name range reference which holds your MDX string, the formula in sMDX would look something like:
="{FILTER( {TM1DRILLDOWNMEMBER( {TM1SubsetBasis()}, ALL, RECURSIVE )}, [Product].[Item ID] = "&CHAR(34)&sItemIDFilter&CHAR(34)&")}"
sItemIDFilter is a name range reference which holds a drop-down validation list or some form of free text entry for your "Item ID" attribute filter.
Hey presto this should do what you want.
Edit:
Don't forget that if you use the TM1SubsetBasis function then the subset basis will be based on the subset in the subset argument. I think it would be better to avoid use of the TM1SubsetBasis function and explicitly name the element or elements in the set that you want to drill down on prior to filtering.
Eg. ="{FILTER( {TM1DRILLDOWNMEMBER( {[Product].[All Products]}, ALL, RECURSIVE )}, [Product].[Item ID] = "&CHAR(34)&sItemIDFilter&CHAR(34)&")}"
- rebuild a user based dynamic subset with action button running SUbsetCreateByMDX from the sheet. Call the subset something like }<user>_<report>
OR
- your original approach, following is an easy way to do it.
Your formula should look something like this ....
=TM1RPTROW($B$9,"server:product","Default",,,,sMDX)
sMDX is a name range reference which holds your MDX string, the formula in sMDX would look something like:
="{FILTER( {TM1DRILLDOWNMEMBER( {TM1SubsetBasis()}, ALL, RECURSIVE )}, [Product].[Item ID] = "&CHAR(34)&sItemIDFilter&CHAR(34)&")}"
sItemIDFilter is a name range reference which holds a drop-down validation list or some form of free text entry for your "Item ID" attribute filter.
Hey presto this should do what you want.
Edit:
Don't forget that if you use the TM1SubsetBasis function then the subset basis will be based on the subset in the subset argument. I think it would be better to avoid use of the TM1SubsetBasis function and explicitly name the element or elements in the set that you want to drill down on prior to filtering.
Eg. ="{FILTER( {TM1DRILLDOWNMEMBER( {[Product].[All Products]}, ALL, RECURSIVE )}, [Product].[Item ID] = "&CHAR(34)&sItemIDFilter&CHAR(34)&")}"
-
- Regular Participant
- Posts: 226
- Joined: Thu Apr 02, 2009 2:51 pm
- OLAP Product: IBM Planning Analytics
- Version: Latest version
- Excel Version: 2003 to 2019
Re: Need help on MDX used in TM1RPTROW function
thanks! will give it a go.
Planning Analytics latest version, including Cloud
-
- Posts: 62
- Joined: Mon Aug 15, 2016 8:48 am
- OLAP Product: TM1 (Windows) & SSAS 2014 Ent.
- Version: 10.2.0 FP3
- Excel Version: Excel 2013
- Location: Hamburg, DE, EU
- Contact:
Re: Need help on MDX used in TM1RPTROW function
Could the SubsetElements argument be a list of elements that is not consecutive, e.g. "El1, El4, El7, El10" from a subset consisting of El1..El10 ?
-----------------------------------
Best regards,
Stefan
Best regards,
Stefan
-
- MVP
- Posts: 3105
- Joined: Mon Dec 29, 2008 6:26 pm
- OLAP Product: TM1, Jedox
- Version: PAL 2.0.9.18
- Excel Version: Microsoft 365
- Location: Brussels, Belgium
- Contact:
Re: Need help on MDX used in TM1RPTROW function
Then make a second subset (static), or make an MDX-driven subset (dynamic) or simply put the MDX query in the sheet.
Maybe an attribute can help you to filter the desired elements ?
Maybe an attribute can help you to filter the desired elements ?
Best regards,
Wim Gielis
IBM Champion 2024
Excel Most Valuable Professional, 2011-2014
https://www.wimgielis.com ==> 121 TM1 articles and a lot of custom code
Newest blog article: Deleting elements quickly
Wim Gielis
IBM Champion 2024
Excel Most Valuable Professional, 2011-2014
https://www.wimgielis.com ==> 121 TM1 articles and a lot of custom code
Newest blog article: Deleting elements quickly