TM1 Forum

TM1 9.4 FP2 64-bit
Windows Server 2003 64-bit
Excel 2003

I've got a SubNm control on a websheet where the user can select a Store.

An Excel formula builds a subset name on the Employee dimension from the user's selection that is a list of all Employees that work at the selected Store.

There is a second SubNm control on the same sheet that uses this subset on the Employee dimension.

The first time the user selects the store the second SubNm control subset changes properly, and as long as the Employee SubNm is not used the user can change the Store as many times as they like and I can see the first Employee in the second SubNm changing appropriately.

However, as soon as an Employee is selected from the second SubNm further changes to the Store SubNm have no affect on the Employee SubNm (the subset and the available Employees do not change).

Is this normal behavior? Am I a fool that does not know something obvious about the product? Is there a way to force the Employee SubNm control to change its subset every time the Store is changed, and not just the first time?

Setting autocalc on the TM1Web toolbar does not make any difference, and neither does setting the RecalcWebsheetOnActivate key in the Web.Config file to true.

Thanks for any help, suggestions, commiserating...

Alright, a combination of testing and digging around in some threads here has uncovered the answer: subnm controls in TM1Web are truely dynamic only once (when the report first opens).

So if I use the Store selected in the first subnm to build the subset name for the Employee subnm it will only work until a selection is made from the Employee subnm. At that point the parameters for the subnm formula are hardcoded in the report.

I tried to get fancy and have the Store subnm selection passed to a Parameter cube in the backend, and just have a single dynamic subset for the Employee subnm that uses the Store value from the Parameter cube in its MDX to build a list of Employees that have been assigned to that Store (in another Assignments cube).

This mostly works (the Employee subset will actually remain dynamic regardless of how many times the Store is changed), but I'm finding that after selecting an Employee the Store subnm is blank when the report refreshes, and the user must re-select the Store. Also the visible employee in the subnm (before the user actually clicks on it) does not refresh when the Store changes, though the actual list of Employees will.

The solution I've settled on for now (until TM1 provides the built-in dropdowns we saw on the product roadmap) is a subnm for the Store, and a hidden range in the websheet with a list of 300 Employee subnms (with incrementing indexes in the subnm formulas) that drive a visible Validation in a cell.

The user can select any Store from the subnm, which changes the Employee subset the hidden subnms draw from, which changes the list of Employees available in the Validation cell.

Not pretty, but it works....