Local Functions
Use
When entering plan data, a special help tool is available using the Microsoft Excel display tool with integrated SAP-specific local functions. When working in manual planning or when entering data, it is of the utmost importance that the response times from the BW system are as short as possible. By using SAP-specific local functions for Microsoft Excel, you are able to run basic functions for manual data entry locally on your client PC. This means you do not have to go via the BW system. These local functions are displayed in Microsoft Excel in their own toolbar, under the name SAP Menu.
In the following, when local functions in Microsoft Excel are referred to, these are SAP-specific functions only; that is, local functions that are integrated into Microsoft Excel by SAP within BW-BPS.
Integration
Every planning layout in the Microsoft Excel display tool is based on a so-called Excel template which contains VBA macros delivered by SAP (for example, the standard exits SAPAfterDataPut and SAPBeforeDataGet). The local functions are delivered as of support package 04 in release SAP BW 3.5. As of this support package, a new master template for Microsoft Excel exists with the up-to-date SAP VBA macros. If you are using a customer-specific master template, make sure that this corresponds to the following settings:
Using Local Functions in Old Planning Layouts
If you want to use an existing planning layout with the local functions in SAP-BPS, make sure that the Excel template you use is up-to-date:
· The Modul1module must contain the VBA subs SAPCreateCode and SAPCreateCode2.
· The SAP_03_calc and SAP_04_prot modules have to be available. Module SAP_03_calcmust contain the empty sub SAPWorksheetChange.
· In the third screen of layout definition, call the Visual Basic Editor (ALT+F11) and choose Tools → References. Under Available References, make sure that the Microsoft Scripting Runtime selection is activated. Otherwise the local functions will not work properly, for example, you will not be able to enter totals. In old planning layouts the SAP Table Factory selection may have been active. This reference is no longer used for executing the local functions described here and should be deactivated.
Buffer for Excel Templates and VBA Macros
Excel templates for planning layouts are buffered locally on your PC in the TEMP directory.
You find this directory, or its equivalent in the various versions of Microsoft Windows, using the relevant environment variables. Choose Start → Control Panel → System → Environment Variable.
So that bug fixes can be delivered for the VBA implementation of the local functions, the VBA source code is not placed in Excel files but in an ABAP include. If a planning layout uses local functions, the BW system loads the VBA source code into the TEMP directory and loads the macros to Microsoft Excel during runtime. The file name for release SAP BW-BPS 3.5 is SAP_BW_CODE_CALC_350_01.txt. Bug fixes contain the corresponding number of the bug fix in the postfix information, for example, _02.txt.
In order to load macros to Microsoft Excel XP/2003 during runtime, access to VBE has to be permitted in the security settings:
...
i. Open any Excel file.
ii. Choose Tools →Macro →Security →Trusted Sources.
iii. Activate the Visual Basic Project.
Also see SAP Note 429183 on Microsoft Excel security settings.
Prerequisites
The activities and modifications that have to be made so that local functions can be implemented are now described.
Activate Local Functions
The local functions are only available in a non-hierarchical data model because in a hierarchical data model, communication to the server has to be maintained so as to ensure data consistency. However, when local functions are used, the values of the hierarchy nodes can be calculated locally at runtime.
...
Proceed as follows to perform calculations locally:
1. You are in the first screen in layout definition.
Choose Hierarchy.
2. The Hierarchy in the Lead Column dialog box appears.
Choose Non-Hierarchical Data Model.
3. Activate the selection Calculate Data Locally.
Predefine Totals in Rows and Columns
You are able to determine settings for totals in the data columns or self-defined rows. Depending on the planning layout, you do this in either the Data Column area or the Lead Column area in the second screen in layout definition.
...
1. Indicate a data column or lead row that is to be used as a total. To do this, select the appropriate selection in the left-hand totals column (Total).
2. Define a total in the right-hand totals column (sum). The totals for the columns or rows can consist of the following elements:
¡ C(x) or R(x) indicates the column or the row and the number x
¡ C(x):C(y) or R(x):R(y) indicates a total using an interval of columns or rows x to y (inclusive)
¡ Elements of the type shown above can be combined and used for any total, however, they must not contain circular references, for example, C(1)+C(3)+C(7):C(9)
¡ You are able to use totals columns in other totals.
¡ If you are using dynamic columns, these are replaced at runtime by the corresponding totals. The elements of the columns affected by a dynamic column are modified accordingly: If x is a dynamic column, the total C(x) represents the modified columns.
Column number 3 is dynamic and is replaced at runtime by two columns. As column 4 now becomes column 5, the total C(3)+C(4) is replaced at runtime with C(3):C(4)+C(5).
These predefined totals in columns and rows can be used in any planning layout with any display tool (Microsoft Excel, SAP ALV Grid and Web Interfaces/Web Interface Builder). These totals are calculated on the server.
Influence System Behavior with Macros
The local functions are normally incompatible with the predefined VBA macro exits SAPAfterDataPut and SAPBeforeDataGet. For more information, see MS Excel-Specific Formatting Options → Functions → Influencing System Behavior with Macros.
SAP strongly recommends that you do not use the predefined macros referred to above with the local functions.
Functions
Planning layouts contain some general characteristics for calculating the values of hierarchy nodes, totals and sub-totals. In addition, you are able to define totals in the static part of the planning layout or in the data columns or the individually defined rows. These predefined totals are then retained in the layout description and are available for calculating other totals.
The Excel functions Group and Outline (from the main menu: Data → Group and Outline) are also used in the static part of the planning layout: If the predefined totals form a hierarchy, Microsoft Excel reproduces this hierarchy automatically using Group and Outline.
If there are gaps in the columns or rows in the planning layout, this automatic group and outline function may not work.
The following list represents an overview of the local functions:
· Manual data entry. After manual changes to data or the execution of local functions such as distribution or revaluation, data is recalculated locally.
· Locking cells, unlocking cells. Local locks and freezing cells: If you highlight and lock cell areas, these cells cannot be changed manually or by local functions.
· Top-down distribution. Changes to totals are passed down to lower levels by top-down distribution; cells that have been locked are not affected by this distribution.
¡ The distribution methods Analog and Evenly are available.
¡ You can determine the order in which values are distributed as columns then rows, or as rows then columns.
¡ You are also able to define whole reference columns and rows for the distribution method. You use this function to map the existing distribution schema from one column or row to other columns or rows.
· Revaluate. You can revaluate specifically-selected cell areas. You can do this using either absolute or percentage revaluation factors.
The local functions have to be initialized by the back-end system. For example, Microsoft Excel transaction data and information has to be initialized by the existing structure of the planning layout so that local functions can be executed. So that the number of data flows to be transferred for the back-end functions (such as Check, Delete,… from the system menu) can be restricted, settings for local functions are not included in this transfer. A recourse to the server will cause the local functions to be reinitialized. This produces the following symptoms:
· The area for new rows is not affected when local functions are executed. In general, the back-end system has to align the information on these new rows, for example, if the new rows are part of a hierarchy.
Use the back-end function Refresh to sort the new rows in the data area and reinitialize the local functions for the data area.
· Local locks are lost, for example, after changes are made to the settings for totals and after the functions Check (after entering new rows), Delete, Previous/Next/Other Combination, Refresh. The only exceptions to this are the back-end functions Input Help (F4), Create/Open/Delete and Save Document.
· Note that in planning folders, the Save function can trigger planning functions. If these planning functions change the data on the screen, the local locks are removed. Therefore SAP recommends that you follow this procedure instead:
a. Add a new row.
b. Use the Refresh function.
c. Use local locks and other local functions.
· A planning layout can have gaps in the data columns or the self-defined rows. These gaps are not part of the SAP data area in Microsoft Excel. Therefore, the local functions do not change the data in these gaps. Usually these Excel formulas for gaps are included for displaying calculated key figures. The values of these key figures are refreshed after changes are made to data in the SAP data area.
· Data slices can prevent you from making entries in some cells or in whole columns and rows. Totals can also be affected if the cells involved are locked.
Data slices and characteristic relationships (“back-end locks“) are passed on to totals. Intentionally, this does not apply to local locks; as a result, the Lock Cells and Unlock Cells functions represent a reversible, symmetrical process.
The list of revaluation amounts in the SAP Menu toolbar is not changed after a recourse to the server. This is also true for the list of distribution methods.