Using the formula SAPSetData for Offline Planning with SAP BusinessObjects Analysis for Office (2.3 onwards)

Background:

When implementing the option for offline planning for BPC Embedded and IP/PAK Scenarios the following How to Guide was provided:  How To..Build a Simple Offline Planning Scenario.

In addition to that it is also possible to build an Offline Template with the SAPSetData formulas (available since AO 2.3). It should be seen as an alternative, as both options have pros and cons.

The VB based solution is query based and best used for scenarios where the users will not work with the workbook further. It can be easily switched between online and offline.

The Formula based solution can have cell references in the input section e.g so can be linked to other workbooks or sheets while working with it offline and online. The formula based example requires a static example, any change in the master data needs to be adapted in the  data entry area of the worksheet.

Both solutions need to be built and designed in beforehand, in contrast to the EPM plugin where Offline Planning and Distribution comes out of the box.
How to build an Offline Planning Template with SAPSetData:

The fomula SAPSetData is described in the User Guide section of SAP Online Help which is recommended to read in beforehand get the basic understanding of formulas.

SAPSetData is build by the following elements:

As a best practice the technical keys and GUIDs should be used.

Example: =SAPSetData(“DS_2″;’Input Sheet’!F15;;”00A0185ZPDRX8D6UW6M7XVRIK”;”TECOUNTRY=IT”;”ZD_MTLGRP=102″)

Example for Formular Wizard:

Example of a workbook which can be used offline:

The “Data input area” will always need to be separated from the “Formula Area”. In this example they reside on different sheets.

The example workbook consists of 3 sheets, 1 Input Sheet, One Formula Sheet (to be hidden), 1 Datasource Grid sheet for doublechecking and for creation/designing the example.

The data Input sheet shows the data from last year which will not be changed. In the white cells, the input can be made. This grid is no crosstab!
(Optional: It is possible to have the Datasource Grid also located on the first sheet beside or below the input cells for checking the server data. Then  is advised to have a marked “End user entry area” and a “AO Query area” to have the Enduser doublecheck the currently existing figures on the server.
For a simple sheet where end users type in their data, a separate report could be used to check the correctness of the input.)

In the next sheet the Formulas reside, it can and should be hidden from the users.

The third sheet is optional, as it mainly is used for building the template, but also for crosschecking server data.

How to use the workbook by endusers:

The process is as follows: The Excel workbook can be saved offline and send via mail or stored on a folder. You can also insert figures or update references to other sheets. The end users will put in figures in the input cells of the tab “Input Sheet”. In the SetData Sheet there is no value shown as it is not connected. The Datasource Grid shows the last snapshot when the datasource was online.

Then you connect to a Datasource via the Refresh Button. The values will be shown in the tab “set Data” in the output of the formulas, but the Data is not saved. Press “recalculate” and “save” and the data will be saved to the database.

When data needs to be changed now, this can be done via “Recalculate, Save”.

It is very important to always save the plan buffer when changing data, before going “offline” as the AO workbook will save buffer data in the workbook which may lead to inconsistencies

Log off via Design Panel -> Components, place Cursor on Workbook name -> Right Mouseclick-> Logoff.

Tips & Tricks for the Formula and Workbook Creation:

1. Define a BW Query that matches your Input Grid. Open AO and define a workbook with 3 sheets. Insert the Query as Data Source in the third sheet of the workbook. This will then be used for crosscheck later. Copy and paste the Structure format and reference figures (e.g former year) to the first sheet of the workbook and design the Excel layout for an offline Data Input Sheet of your choice. Mark the cells for input, delete the numbers in this input area.
2. Insert a second datasource to the second sheet. Mark the Crosstab and Press “Convert to Formula” , you will then have a SAPGetData Formula in each cell, which gives all information you need for SAPSetData. (Sometimes it is easier to copy only the crosstab via the copy option in the context menue of the design panel section “Components”.
3. Example: =SAPGetData(“DS_1″;”00A0185ZPDRX8D6UW6M7XVRIK”;”TECOUNTRY=IT”;”ZD_MTLGRP=102″)
4. In the cell definition Change SAPGetData to SAPSetData and add two fields: After the Datasource entry, enter the reference to the input Cell. After that include space for the options. (see the user guide for special options here)
5. The result will look like this SAPSetData(“DS_2″;’Input Sheet’!F15;;”00A0185ZPDRX8D6UW6M7XVRIK”;”TECOUNTRY=IT”;”ZD_MTLGRP=102″). This needs to be done in every cell.
6. It is also possible to use the syntax SAPSetData(“DS_2″;’Input Sheet’!F15;;”00A0185ZPDRX8D6UW6M7XVRIK”;”TECOUNTRY”;”IT”;”ZD_MTLGRP”;”102″). This syntay needs to be used when using cell references.

Notes:
-Is advised to check that technical keys are used for formula definition to avoid language dependencies.
-You could use Find/Replace for Changing SAPGetData to SAPSetData.
-The Formula SAPSetData can also contain only cell references, for the first entries the Formula Wizard can be used, but the member combinations can only be inserted manually if they should contain cell references. If the cell references are fixed with $ then the formulas can easily copied throughout the Input Sheet by column, row, working with them according the Excel guidelines

Example: