EPM Cell locking and Worksheet Protection
SAP Business Objects and Consolidation for NetWeaver 10.0
This document explains how to prevent a user from changing, moving, or deleting important elements of the report using dynamic formatting sheet and EPM worksheet protection.
Author: Dolly Katyal
Company Name: Wipro Technologies
Author Bio: Dolly is a SAP BI/BPC developer. Worked in an end to end SAP BI/ABAP/BO implementation, with domain knowledge of various utilities modules Meter reads, meter data collection, finance, enquiries, new connections, fieldwork etc. Currently working as SAP BPC developer on various planning modules like HR, Benefits etc. for a US customer.
A requirement specifies that the user should be able to enter the data in an input form based on property of a dimension. Based on that property values, some cells should be locked for input and user should not be able to enter data against them. We can accomplish this requirement using cell locking and EPM worksheet protection.
To prevent a user from changing, moving, or deleting important elements of the report, you can protect the current worksheet or the whole workbook. If you protect a worksheet containing reports in which a user will enter data, you can lock certain and unlock other cells of the report.
Dynamic formatting sheet
Dynamic formatting is defined in a dedicated sheet. A specific format (such as font, color, or size) can
be defined for levels of a hierarchy, types of members, odd/even rows or columns and page axis
Rules for dynamic formatting
Below are some rules that should be taken care of while formatting:
1. If you check more than one formatting section, the format settings in lower sections override the ones in upper sections in case of conflicts (for example: inconsistency between a member format and a level format).
2. Inside each section, if you check several Apply checkboxes, the format settings are applied from
the lowest area to the upper area.
Steps for locking cells
Step 1: Firstly create a report
Step 2: Apply locking in formatting sheet
1. To restrict single member, you can directly chose the member and restrict.
Go to EPM formatting sheetMember selection tab
2. To restrict multiple members on the basis of a property, maintain any property in Account dimension.
Example: DISPLAY in this case. Mention the property values as “N” for which you want the lock the cells.
Then go to — EPM formatting sheet –Property selection — make selections as shown below.
It looks like this once the property selection is done:
Step 3: Lock in formatting sheet
Now we have selected the property on the basis of which we want to lock the data.
Right click on the cell in front of the property selection cell Go to EPM Lock selected cells.
STEP 4: Apply Worksheet protection
Dynamic formatting locks will not work without applying work sheet locking
Note: The Microsoft Excel sheet protection always overrides the EPM add-in sheet protection. You can unprotect a sheet using the Microsoft Excel feature or the EPM add-in feature. However, to protect a sheet, you must use the EPM add-in feature.
How to protect the workbook using EPM Add-in
To protect the sheet, the workbook or only certain elements, and to enter a password:
Select EPM — Options — Sheet Options — select the Protection tab.
Choose a password that is easy to remember, because if you lose the password, you can no longer
access the protected worksheet or workbook.
Step 5: Verify locking
Once you are done with protection. Try to insert data in the cells that we have already locked using EPM protection.
To edit your report further you will first have to un-protect the workbook and then edit the report. Steps are mentioned below:
Select EPM — Options — Sheet Options.
A message appears in which you can enter the protection password. In the Protection tab of the Sheet Option select NoProtection.
We have learnt how to lock the cells in EPM workbook by using dynamic formatting template and EPM workbook protection. We can lock and unlock the workbooks using EPM options.