subnm with variable overriden

[mnasra]

Hi expert,

i have a websheet with 2 title headers.
1- What cost center?
2- Depending on the cost center chosen, the subset shows only the accounts (level1) of that cost center (subnm with variable).

everything works perfectly. Change the cost center, the list of accounts change too
UNTIL ...
the user goes down to the leaf level of the subset.
it is like the system lost my formula. it stays on that leaf level account. I can recalc, rebuild the websheet, nothing!

the only thing the user can do, is get out completely of the websheet, and reload it-which is extremely ennoying.

is there a way to have the websheet keep the SUBNM formula live?

[paulsimon]

Hi

I think you are going to need to explain a lot more about what you are doing before anyone can help you.

Regards

Paul Simon

[mnasra]

I created a subset of accounts for each cost center.
lets say cc200 , i create a subset on the account dim called sub200 ( containing 4 accounts -level 1 in the hierarchy. not deployed to leaf level)

the user chooses his cost center (straight subnm command) cc200
the next subnm command, is on the account, and it shows the sub200 automatically. 4 accounts

the user can choose another cc (300) and the subnm sub300 on the accounts is shown.
this works perfectly until the user deploys the accounts and chooses a leaf level account.

after that, when he chooses another cc400, the subnm sub400 is not shown. it stays on the leaf level account of the previous cc.
rebuild does not do the trick. they have to get out of the websheet and reload it again.
Thank you to anyone who can give me an idea on to how to resolve this.

[daniel.havassy]

Hi Micheline,

maybe it's clear for everyone else, but can you explain to me what you mean by "the user deploying the accounts"? Furthermore, can you paste the SUBNM formulas that you are using?

Thanks

[Mark2007]

I think the problem is, that once the user goes down a level, he does not work in the subset "CC200" (which consists only of Level1) any more, so the SUBNM looses the link to the Subsets Name - but actually, I've got no idea how to solve.

[tomok]

I think I know what you are talking about. Sometimes I like to have the subset parameter of the SUBNM formula point to a cell instead of a literal value in order to make the subset used dynamic. This works great the first time you actually use the SUBNM formula to pick an item from a subset but once you do, the formula is converted to a plain SUBNM formula and you lose the link to the cell. This only happens in Excel. If you use TM1Web then the link to the cell stays there. For this reason I only employ that in sheets that will be consumed on TM1Web.

[bgregs]

I have also noticed this same issue in Excel only. Another option (dare I say ) is to use the E_PICK macro. This would ultimately make it incompatible with web, more of a pain to update, and obfuscated for no real benefit, BUT it will work. Just don't blame it on me if you decide to go that route....

[mnasra]

Thank you all.
Yes, this is exactly what I meant. and I am using it on Tm1web.
I just realized that I could have said this:
I have this command: Subnm (sdim, $E$9 , 1) in E9 I have the name of a subset (that is variable).
If the user stays in the subset, it continues to work. But if he chooses another element in the dimension, the E9 is not applied anymore for this web session.

Bgregs: I have never seen or used E_pick. Just investigated it. It would not help me.
Thanks all.
the workaround I found, is to have the subset (in e9) down to the lowest level of the dim (a pain because it is long) but it works all the time.