Locking data using Work Status or GetonlyRange

[Former Member]

Hello All,

I have few questions on the usage of the workstatus locks. I am trying to use it to lock my actuals/plan data for every month after the month is closed and in the case of actuals, lock it always. How do I do this? Actuals and Plan data are displayed in most of the input templates. What is the best way to do it? Is work status the best route or get only range in EVDRE?

The problem with getonly Range in EVDRE is it has to dynamically determine the months it has to lock for the forecast/plan month. How do I do this? Also the get only range does not let submit data into the server but it still lets user edit data. How can avoid it? So I have to find a way to dynamically lock the cells too..

Also, I see that work status can be used. Though I feel work status is helpful more on BPFs. can anyone shed some light on how to achieve this functionality? In BPC 7.0, on the work status settings, i see an additional column for base level hierarchy. It does not let me save the work setting without providing value to it. What is the impact of this property?

Thanks in advance....

[Former Member · Accepted solution]

A couple of notes and questions.

- If you are locking ACTUALS as a category, I would use SECURITY Member profiles, and always set it to read.

- If you have a process to copy ACTUAL data to PLAN periods and want to lock the periods as they move, then I would either use the work status process at the admin level, OR use EVDRE FORMAT functions to dynamically lock the periods that have actual data in EACH sheet.

The tradeoff is related to the process your group plans to utilize for a PLANNING process. The impact of re-building each template to have format driven locks may be expensive and time consuming. Locking using the WORK STATUS may be quick, but may interfere with other operations and process control factors. In addition, logic and DM packages would need to be reviewed and revised to incorporate skipping locked data if needed during a calculation.

So, there is no correct way, just ones that fits the customer and profile of the process you are performing. As for your question in the work status box in Admin; 1st you need to setup WORK STATUS in the Admin web page, then you can make changes; The BAS and PARENT process is for using VALIDATION accounts and intersections to the workstatus process and may not apply to your needs.

Hope this helps.

[Former Member · Accepted solution]

Peter,

Thanks for your answer. Can you elaborate on "EVDRE FORMAT functions to dynamically lock the periods that have actual data in EACH sheet"?

I would like to also lock the forecast data as each month closes. Problem with workstatus is it provides error only data is posted, but still users can edit data.

Thanks...

[Former Member · Accepted solution]

In EVDRE you have the option to use a FORMAT section to add specific format behaviors to members in the data range. So if I wanted to lock "ACTUAL" period data in a report, I would use the Excel Protection feature that is native, and then in the command sections of the format area, select a trigger to lock the cells based on the column key members or properties. Then using the fornat commands, set the whole data range to unlocked. Then in a command string in the format process, set the CRITERIA section to use the command, KEY="ACTUAL", then set the condition on the CELL to be LOCKED. That would then evaluate the COlumns for the KEY equal to ACTUAL and then LOCK the cells so that values cannot be entered. That way, as the key changes, so does the number of locked columns. This is only one example and there are various combinations to build these results. (for more details, check for the how to guides on SDN about formatting)

One item I failed to also mention is the following: It is my practise that when I have a FORECAST and I build a report or plan, I copy the Actual values to that version, so that I don't have to pull multiple categories in an EVDRE. It also means that I can always re-copy the actual values over any ill entered data that does creep into the process, and I would schedule that as a routine during the planning cycle. So even if I don't lock the periods of data from Actuals that I copied, I make it a process point to explain that if a number is entered over that combination, it may be replaced. I also sedn the Actual data to its own DataSrc member, just so I can track any bad values that may get entered. So there are many, many methods to this process; you just have to build one that works for your team/customer.

Hope this helps.

[Former Member · Accepted solution]

Hello Peter,

Thanks for your response. But I do not understand your response in full detail. Let us take an example and may be you can explain how to do it.

I have a EVDRE Report for Forecast data for year 2009 (say columsns J:V). Column J being Jan'09. Once Jan closes, I would like to lock all the cells in the column. So in July, Jan thru Jun thru should be locked. How will I do this in Formatting section in the EVDRE?

Thanks in advance for your help.

[Former Member · Accepted solution]

Hello,

in your case the best solution will be to use Workstatus functionality.

The business process will be next:

1. User responsible for forecast data input enters data

2. The same user changes the workstatus for the 2010.JAN data slice to "Locked" (eSubmit->change workstatus)

After these 2 steps users won't be allowed to change the data for 2010.JAN.

Hope this info will be useful,

Dzmitry

Edited by: Dzmitry Lebedzeu on Oct 7, 2009 9:14 PM

[Former Member · Accepted solution]

I cannot give you a specific method, since there are so many options to build into the Excel template. The key to the problem is to use the EVDRE FORMATTING table (check the formatting how to guide on SDN), set the excel template to be PROTECTED(after the FORMAT Section is complete). Then using some EVDRE formatting structure and native excel functions in the KEY column ranges, build a method to check a value by column to see if the range should be LOCKED or UNLOCKED.

For example, you could set a property by category to tell the sheet what month to START a forecast. Then use that to build an excel test by each column. Then test that value in the EVDRE FORMAT process to unlock data cells. But this is just one method of setting up the template.

Hope this is useful.

[Former Member]

Work status can be used, but users could still change data and after send receive an error that work status settings are incorrect for sending data.

If you want to make sure inputcells are locked for actual or closed months, you can use the formatting range en make the report lock cells automatically based on a property value, or if you have fairly straitforward reports, us EVTIM functions to remove the TIME dimension expansion, and lock the columns using standard excel formatting options.