TM1 Forum

I did look around at similar topics, but couldn't find what I was looking for.

mdx is a parameter in TM1ROWVIEW, but it is not a parameter in SUBNM.

I have a rather elaborate View based filter that I managed to jam into an active form and it works a charm.

But, now I want a picklist to be used as an input for a button process. It is very similar to the MDX used in the row function above, but I can't see how to get the user a picklist based on it (at least, not by using the subset editor.)

Suggestions?

Not familiar with TM1ROWVIEW, do you mean TM1RPTROW?

You can't base a picklist on MDX directly, but you can use MDX indirectly by defining the picklist members as a dimension subset referring to a public dynamic subset. It depends what exactly you want to do with the MDX: this may get you there or it may not quite be flexible enough. To achieve more flexibility you could use string rules to dynamically change the Picklist attribute depending on context/circumstance.

You can also define a picklist cube and use rules there and this could give more contextual flexibility again. However it is still going to rely on upfront work to have a number of dynamic and/or static subsets pre-defined and sitting there waiting to be referred to by a rule. (I'm not suggesting to try and piece together a list of element names using string rules, I think this would be a bit of a nightmare.)

If you are prepared to have a "button click" rather then things seamlessly changing on the fly then you could use TI to generate static picklists. This could actually give you the most flexible and easiest to code a nd troubleshoot solution but obviously has some drawbacks as it is definitely not quite so seamless for the end user.

If the UI is Excel vs websheet or cube viewer then you could use cell validation. (You can do some pretty fancy stuff with offset and named formulas)

Oops, yes, I meant TM1RPTROW.
And, yes, I'm delivering the app on the browser, so I have to live within the functions available there.

But your suggestions gave me some ideas. I managed a workable solution:

I made a second worksheet in the workbook. Placed a simple active form on it that returned items in the row headings using the mdx in TM1RPTROW like the mdx in the main active form.
I then used a range name as a data validation range that points at this column in the secondary active form. It works great, as long as one arrives at the page via a macro button that rebuilds the form. Which, in this case is probably close enough.

FYI. The MDX in the main active form lists the items that are actually used. The MDX in the secondary active form lists the items that are available for that title item.

Thanks for your help and prompt reply.