Conditional Lock Cell in TM1Web / Worksheet

[Ah3U]

Hi all,
i need your help about TM1Web / Worksheet..
i have a cell that define what year the data will show.. say i put it in A1..
and there's another cell, say B1, where the year of SYSDATE will showed..
i have another cell, say in C1, that include DBRW function in it. (To show or edit the data in the cube based on the year the user put in A1)
i need to lock the C1 based on the following condition :
- if B1 <= A1 then the user can edit the data showed in C1
- else then the user cannot do anything to the cell so the data in the cube won't make any change

The only thing i know is to put a VB code in the excel but it won't work after i upload the sheet to TM1Web.
Is there any good advice to solve this problem ?

Sorry for my bad english..

[declanr]

Assuming you have your year as a dimension you can just apply TM1 security against the element dimensions.

[qml]

TM1 security or, better yet, applying TM1 element Locking.

If it absolutely needs to be controlled within the websheet only, then you can have a little conditional logic in your DBRW formula that would take a consolidated element instead of a leaf element in one of the dimensions when the condtions for not being able to write are met. Since you cannot write to consolidated elements (exceptions: data spreading or string values), this works like a read-write switch. The C element should of course return the same value as the N element would, so you need to design the dimension right. Ideally, it would be your smallest dimension, so that having the C-N pairing is easy to do and has minimal impact.

Another thing to consider is that you cannot use DBR(W) functions to write values if they are not at the beginning of your Excel formula. E.g. =""&DBRW(...) is always read-only. You can think about that and maybe find a way to utilise this behaviour somehow.

[lotsaram]

I really like qml's suggestion, it is a good creative solution. But I think that in your case it sounds like you should be using element or cell security to control what is writeable. I have to read between the lines to guess at your requirements here as you didn't give much to go on but I am guessing that "if B1 <= A1" probably refers to a date range where past is editable future is not or vice versa and if so this woudl be a system parameter that should be controlled within the model and dealt with by normal security.

[Ah3U]

Thank you for your reply.
Yes, the year that i put in A1 has its own dimension.
But i don't understand what you mean by "apply TM1 security against the element dimensions"
Is it to apply the element level security ?
The thing is the conditions are not depend on user's group. The conditions depend on the user's input in A1 (i put SUBNM function so the user can pick the element in the year dimension) and fyi, the year date the user put in B1 is not always the sysdate, it can be a random year (user's input year)

Or is there any TM1 security that i need to know ?
Please, i need more explanation.. i'm new at this..

[Ah3U]

Here's ilustration of the worksheet :

http://www.postimage.org/image/6kd15j9kx/


The colored cells under the year is the user's input cell
And there's another problem. I can't use =IF function because the cell is unwriteable after it uploaded to TM1Web

Please help me to solve this problem.

[tomok]

Please help me to solve this problem.

You've already been given the answer, secure the year dimension (with element-level security) and make all prior years READ. it is extremely bad design to try and implement security in a template because now ALL your templates have to do the same thing. What happens when you want to allow users to enter via a cube view????? Wouldn't it be easier to do this in the model? Read the security section in the TM1 manuals. This is extremely basic stuff and there are several examples in there about implementing element level security.