TM1 Forum

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!

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.

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.

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

thanks! will give it a go.

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 ?

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 ?