How to make some data column not inputable in input schdule of BPC

[Former Member]

As we know in BPS, we can make column not inputable by check the comparison check box, how can we archive the same in BPC? By using Excel protection function?

[Former Member]

This can all be done dynamically, using a combination of the EVDRE formatrange, a few Excel formulas behind the scenes in your XLS template, and perhaps a member property or two on your Category dimension.

Let's say I have four categories, MarchUpdate, JuneUpdate, AugUpdate, DecUpdate.

Using a single input template for all of these, I want to show, respectively, 2, 5, 8 or 11 months of actuals, plus 10, 7, 4 or 1 month of update category data.

Using a simple VLOOKUP, or a member property, I set the category in the ColKeyRange dynamically, to either Actual or the current Update. (What's the current update? Either let the user decide or, use NOW() and some Excel math, or if the schedule is regimented by an admin, put that as a property in the category dimension or somewhere else that makes sense, and use EVPRO to retrieve it.)

Then use just two formatrange entries, each considering the column

- one is default (for all the update categories) - unlocked protection, formatted to look like an input cell

- one looks at the Category.ID = Actual - locked protection, formatted to look like read-only

Sometimes I find it useful to also have a property FirstPlanMonth (MarchUpdate = 2008.Mar, etc.) in my category dimension, so that an admin can centrally control the setup and move the planning clock forward. Then I set the time periods in the input schedule colkeyrange by just taking left(FirstPlanMonth,4) as the year, then concatenating that in Excel with &".JAN" &".FEB" etc. In this way, let's say they want to start the June update planning cycle in April this year, and then change it to May before finalizing the update. The admin just needs to update the FirstPlanMonth, and load actuals into May, and bada-bing bada-boom.

[Former Member]

Hi,

Could someone post an example of this? I am trying to lock all the actual months in forecast scenario (it is dynamic, some times there are 3 months of actual data, other there are 6 or 9 months of actual data) but unfortunatly I can't do this.

Regards,

Aluizio

[Former Member]

There are a few options. The ones that I think work well include, using the GETONLY range for an EVDRE template, using a column marker in the report and then utilizing the EVDRE advanced formatting process to LOCK the data via the Excel defined cell properties. I will look for an example and update the thread.

[Former Member]

Hi Peter

I was looking for locking the actuals in the template while keeping the forecast months open for user input.

So my scenario is lock the months from Jan 2008 to October 2008 since it has actuals while keep Nov 2008 and Dec 2008 open for input since they are forecast months. How can I achieve this?

I know I can put a range in EVGETONLY which will block the data in actuals from sending even though user changes actuals by mistake. However this function is static since I have to hardcode the range and when we progress in the year, we have to change this range every new month to reflect the forecast vs actual months. I know we can develop some logic in excel to get the right range for EVGETONLY dynamically.

However, I know that there is one format in FORMATRANGE which has an option of LOCK. Can you please elaborate on how this works?

If I have a row in the FORMATRANGE which says

CRITERIA: Category = Actual

USE: LOCK

APPLY TO: DATA

Will it lock the actual category and if yes, how will the template react when the users try to change/send the values in Actual Category?

Please let me know.

Thanks for your help

Ameya Kulkarni

[Former Member]

Yes a cell can be locked using the excel protect option. Just select formating and klick locked in the protection tab.

But when you actually lock the workbook, do this using Etools-workbook options-set workbook password. This way is better then the Excel protection, cause it will not cause your BPC reports to behave oddly.

Unprotecting a workbook can be done using standard excell functionality.

If you have an EVDRE input schedule you can also use the getonlyrange to make sure data is not sent to the database (if this is nescesary).

Greetings Tim Vierhout