TM1 Forum

Hello all

I have a problem with a SUBNM function on TM1 Web. In fact, I am using 2 SUBNM functions: the subset name argument to the second function is a simple cell reference to the first SUBNM. So:

=SUBNM("server:dimension1","Default";"element";"alias") in cell A1
=SUBNM("server:dimension2",A1;"element";"alias") in cell B1

The A1 is the interesting part over here. Now, this approach works perfectly in Excel. It allows to have a top-down approach: first you select the name of a consolidated element, after which in the second SUBNM you select 1 of the children of the consolidated element. It is not a problem to have one subset for each consolidated element, they have exactly the same name.

But on TM1 Web, after the first SUBNM is used, the second SUBNM is not updated for the subset name (coming from the first).

I know this might not be supported behaviour. But the alternative of having a temporary sheet with hundreds of ELCOMP functions and data validation is not appealing and slower. I don't know upfront the maximal number of children of any consolidation. And VBA cannot be used on the web.

I tried hitting F9, an action button to refresh the sheet, adding volatile functions in Excel, the recal button in the toolbar, ... none of them seem to work.

Any pointers, small or big? Or a creative workaround?

Thanks in advance,

Wim

Hi Wim,

I have come up against this before and I am pretty sure this issue has been discussed on this board before. Basically how dynamic reference based formula arguments work with SUBNMs in Excel and what you want to do on the web simply don't work that way on the web. The first time the "dependent" SUBNM is selected on the web the dynamic references become hardcoded.

The only way around this is to reload the worksheet. Maybe if it is a small sheet and if you can do this via action button in some way that is intuitive to the user then this might work! Otherwise you have to use Excel.

Hopefully this might be enhanced in a future version but who knows ...

PS if you can you might want to edit the title of the thread as it is misleading. Maybe "SUBNM cell reference arguments do not update on TM1 Web" would be more appropriate?

Hello Lotsaram

Thank you for confirming my thoughts. I will test your suggestion.

Wim

Topic title improved, thanks.

Hi I have encountered the same issue. With Dynamic SUBNM fuctions.
Did you find any solutions in the mean time?

Thanks
Thomas