SUBNM MDX Formula

Context:

In the context of our complex simulation model, most of our end-user interface is built through websheet (accessible through TM1Web) to access and edit cube data. However there’s one limitation that strongly hinders our end-user adoption of the application: the interaction with the websheet activeform (TM1RPTROW).

In a classic cubeview, users are able to interact with rows in a seamless way: no matter what they select in the subset editor, their selection will be displayed exactly as selected.

In a websheet, we can’t properly connect the content selected in the set editor (SUBNM) with the TM1RPTROW. The subnm formula only returns:

• the name of the selected subset, which we can’t retrieve in TM1Web (because it doesn’t support FORMULATEXT() function that can read the text within a formula)
• The name of the first selected element

To our knowledge, transferring multiple elements selected in a SUBNM to a TM1RPTROW is impossible. We don't have the ability to interact seamlessly with the TM1RPTROW the same way we interact with the rows in a cubeview

Workaround

See attached file

Solution

Planning Analytics is capable of interpreting a selection in the subset editor as an MDX statement in real time. In fact, when we create a dynamic report, the selection in rows is no longer a hardcoded list but a hardcoded MDX statement. What we need is dedicated Excel formula that returns the MDX interpretation of the selection within a subset. Such a formula would connect the SUBNM and the TM1RPTROW in a seamless way. It would completely align the cube selection and websheet selection.

Benefits

Huge improvement of the end-user interface.