Need help on MDX used in TM1RPTROW function

Post Reply
harrytm1
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

Post by harrytm1 »

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!
Planning Analytics latest version, including Cloud
lotsaram
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

Post by lotsaram »

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.
harrytm1
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

Post by harrytm1 »

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.
Planning Analytics latest version, including Cloud
lotsaram
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

Post by lotsaram »

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)&")}"
harrytm1
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

Post by harrytm1 »

thanks! will give it a go.
Planning Analytics latest version, including Cloud
st2000
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

Post by st2000 »

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
Wim Gielis
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

Post by Wim Gielis »

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 ?
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
Post Reply