Introduction
EPM offline mode is a key feature when it comes to procuring input from users not having EPM Addin installed. However, the offline mode comes with its own set of challenges.
Scope
Offline mode provided by EPM Excel Addin required when converting a file to a offline mode - manually or via Distribution functionality.
Software Components
- SAP BW7.31
- SAP BPC 10.0 Version for SAP NW
- EPM Excel Addin - Version 10 SP21 .NET4
- MS Excel 2013 (32-bit)
Challenge
The offline mode overrides the protection provided in the application and disables the permitted Excel features.
Additional Details
SAP Note 1738690.
Case
Let us say we have an input form with a few thousand rows with Auto-filter enabled and EPM protection provided with the permission to use Auto-filter. It is to be sent across to people on field to capture forecast closer to the customer for better accuracy and visibility. These representatives do not have EPM Addin. The input form is sent across to them after turning on the offline mode.
Challenge
Even though representatives are able to input numbers, they are not able to perform auto-filter. The auto-filter is disabled by the protection forced by Offline mode.
Cause
When a report or input form is switched to offline mode, it forces an additional protection on the worksheet which overrides the existing one. The default password for offline mode is "PASSWORD" - refer SAP Note 1728690. Even if the same password is supplied under EPM protection or your VBA code, the template/form/ report stays locked except the data entry cells.
Approach to Resolve
A small VBA macro can be executed when the file is opened to remove the Offline Mode password and re-enable it again with required permissions. In this case, we need Auto-filter to be enabled.
Sub Offline_Tackling()
Dim RowID, ColID As Integer
RowID = "Row number of any column axis member"
ColID = "Column number of the same column axis member"
If InStr(CStr(Cells(RowID, ColID).Formula), "_epmOfflineCondition_") > 0 Then
ActiveSheet.Unprotect Password:="PASSWORD"
ActiveSheet.Protect Password:="PASSWORD", AllowFiltering:=True
ActiveSheet.EnableAutoFilter = True
End If
End Sub
What this macro does is to look into a cell (any fixed cell that can be reference for offline condition formula) of the form to check if it is offline or not. It can be executed at the time of opening the workbook or user discretion. It will help to enable the Auto filter with other protection remaining intact allowing the template to be a little more flexible to use.
When template/form is brought online, the original password will be restored and the data can be saved successfully.
