Tell us what you think

We want to create the best possible experience for our learners. Your feedback helps us to improve the experience for everyone.

Understanding Data Protection and Data Validation Options

Objective

After completing this lesson, you will be able to lock key figure from editing

Options for Data Protection

The Protection tab in the Settings → Options window gives users the ability to protect the active worksheet or workbook completely or in relation to certain features. There are 14 options that can be selected once the Protection radio button is on.

Initially, the Protection tab is unlocked, and you can add a password and adjust the settings. This password can be used later to unlock the sheet or workbook again or change the settings.

If a sheet or workbook is locked, unlock the settings on the protections tab to change them. Choose the lock sign in the upper right corner and enter the password.

To lock the sheet or workbook again (in case a password is already entered), choose OK.

Although you unlock the protection tab settings by entering the password, the worksheet or workbook is only unprotected when you select No Protection and confirm with OK.

Note

You cannot access the Member Names tab if the workbook or sheet is protected. The tab is greyed out and a tooltip indicates why it is greyed out.

If you have protected the workbook, you must unprotect the workbook first.

You cannot set the protection as default.

Data Validation

Data Validation in SAP IBP Microsoft Excel add-in works in the same way as the standard Data Validation functionality of Microsoft Excel.

For the data to be validated in a certain SAP IBP planning view and for that setting to persist, data validation must be enabled through the SAP IBP formatting sheet.

To do so, navigate to SAP IBP Formatting Sheet, select the key figure which needs to have data validation in the Dimension Member/Property Formatting space, and select the validation setting.

It is also possible to specify an error alert and input message for the range of values that is acceptable for certain key figures.

Lock Key Figure

Business Example

You must be able to protect values in certain key figures using SAP Integrated Business Planning, add-in for Microsoft Excel, and not via configuration. If you have more time, introduce data validation for a key figure using SAP IBP, add-in for Microsoft Excel.

ExerciseStart Exercise

Steps

Using the favorite from the previous exercise, lock the Sales Forecast Qty key figure so that it is not possible to enter data values manually.
1. In your Consensus Demand 2 Levels ## favorite, choose Edit View → View Formats.
2. Choose the SAP IBP Formatting Sheet tab.
3. Choose Add Member/Property under Dimension Member/Property Formatting for Row.
4. Choose Add Condition, and select Key Figures under Dimension Selection.
5. Choose Sales Forecast Qty under Members , and then choose OK.
6. Choose OK.
7. Right-click 10000 under Data and choose IBP → Lock Selected Cells.
8. Double-click ALL in the Use column. Select Lock and deselect other settings. Then cchoose OK.
9. Choose the first planning view sheet, where you have three key figures, including Sales Fcst Qty, and choose Options... under Settings in the About section.
10. Choose the Protection tab and select Protect Active Worksheet.
11. Enter your password, for example, 110, and then choose OK.
12. Choose Refresh and verify that a warning message is displayed when you try to enter data in the Sales Forecast Qty key figure row.
Using the favorite from the previous exercise, lock the Marketing Forecast Qty key figure so that it is not possible to enter data values outside of a certain range.
1. In your Consensus Demand 2 Levels ## favorite, choose Edit View → View Formats.
2. Choose the SAP IBP Formatting Sheet tab.
3. Choose Add Member/Property under Dimension Member/Property Formatting for Row.
4. Choose Add Condition and select Key Figures under Dimension Selection.
5. Choose Marketing Fcst Qty under Members, and then choose OK.
6. Choose OK.
7. Right-click 10000 under Data and choose IBP → Data Validation.
8. When the validation form will appear, select Whole Number and specify a range, for example, between 40,000 and 50,000.
9. Choose OK.
10. Double-click ALL in the Use column. Choose Data Validation and deselect other settings. Then choose OK.
11. Choose the first sheet in the planning view, where you have three key figures, including Marketing Forecast Qty, and refresh your view.
12. Try entering a number in one of the weeks outside of the range and verify that a warning message is displayed.

Continue to quiz