“Set Variables Before First Refresh“ using Callbacks (Analysis Office 2.2)

SAP BusinessObjects Analysis, edition for Microsoft Office 2.2 is generally available since end of November 2015 on the SMP and offers among others the long-awaited possibility to define variable values before the initial refresh.

How does it work?

Your own coding is called before the initial prompt. Here you can provide variable values and pre-fill the prompt. If everything is fine, no prompt is shown and the query will start straight.

This enables many scenarios that you might want to implement.

Some examples are:

• Take values from some places in your excel workbook
• Define your own initial selection screen
• Derive time values dynamically based on current date.

The time value example is explained in detail in this blog.

Understand Analysis Office Callbacks

In short: you need a macro with some callbacks.

Analysis Office offers a range of several callbacks that are executed with certain events. Most of the callbacks must be registered with the API method ‘SAPExecuteCommand’ before being used for the first time. To deregister the callbacks, you can also use ‘SAPExecuteCommand’. -For more information regarding Callbacks, please refer to the Analysis Office guide.

Example – derive date variable values from “today”:

We have a report based on a query with 2 variables: “Time period” and “Product Group”. Our report is executed once a month and always for a specific time period, which changes with every month.

In the prompt screen, you don’t want to always select the last 12 months (e.g. 03.2015-02.2016) , but you can have Excel do this automatically.

Values for the variable “Product Group” can be changed manually. So we start our workbook, “change” the variable values and refresh our report.

How is our workbook built/designed?

Our workbook consists of 4 sections/elements/parts:

(1)  “Alternative Prompt”, get your personalized prompt for your report/query

(2)  “Help table” with formulas (to support automated setting of variable values)

– Both sections can be defined in a separate sheet, e.g. Settings. In our example we have all in a one sheet.

(3)  Report

(4)  Macro

Let’s explain the single sections:

(1)  “Alternative Prompt” (~ my personalized variable values) for setting variable values before first refresh

In our example we have two variables, but you can define more than 2 variables and the data range containing the values we defined in our example as name range AOVarPrefill. (This content will be later accessed by the VBA coding/macros to populate/prefill the prompt)

Note: All the needed data such data source, variable name and variable value should be defined as a key.

For product group nothing specific needs to be done – just enter the value for variable “Product Group” manually.

For the time variable for sure we need to implemented the logic to derive the value from “today”. The rule to define the time value is implemented using excel formulas (see Help Table). The result is referenced from “Alternative Prompt” and then executed via callback handler.

Additional, there is a button “Refresh DataSource” for refresh the data after the variable values were set and this is the coding called by the button:

(2)  “Help table” with formulas (to support automated setting of variable values)

As described above, our report is executed once a month and always for a specific time period: (ThisMonth.ThisYear-1) – (ThisMonth-1.ThisYear), e.g. 03.2015 – 02.2016 –> this can be set in an automated mode using Excel formulas.- Using the excel formulas you are not forced to create specific variables in the Query Designer.

(3)  Report

Additionally you can set „Force Prompts“

to get the prompting dialog with prefilled values as a check (Optional).

(4)  Macro

Now we need some VBA coding: we start with open the workbook and initialize it. Both should be placed in the “ThisWorkbook” section of the VBA editor. The other callbacks should be defined in a separate module.

The callback “Workbook_SAP_Initialize” has to be defined as a subroutine without input parameters.

In the next step we create a module “AOPrefillVar” with some VBA functions prefilling the variable values.

At first we declare the data range to read the data from the “Alternative Prompt” and set the values for variables:

Then we need the callback “BeforeFirstPromptsDisplay” to perform the definitions of variables before the initial display of the prompting dialog:

The Callback has to be registered; if the registration was successful, you get a message:

This should just give you some idea what you can do with the API, so check it out.

I thank Olaf Fischer and Matthias Gemmel for providing some example coding for this blog.