“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.
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.
Additionally you can set „Force Prompts“
to get the prompting dialog with prefilled values as a check (Optional).
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.
Thanks for sharing this.
Can you please let us know with which SP Level you completed above work?
I created my workbook with Analysis Office 2.2 SP 2.
thank you for putting above article. it is very helpful. I do have quick question Isabella. would it be possible to create similar macros using SAP BW DSOs instead bex queries?
The reason i'm asking is, our customers are planning to query data directly using DSOs instead of BW Bex queries. We would like to place Filters in AO Workbook so users won't query huge set of data when querying at initial workbook run. Any info would be greatly appreciated.
thanks but it doesn't work when I use a mandatory replacement path variable. When I use the refresh command, the prompt is displayed. And after the refresh you execute the "BeforeFirstPromptsDisplay" but this is "too" late for a mandatory replacement path variable.
Have you any idea, how I can fill this variable before a prompt appears?
it doesn’t work because replacement variables are not input ready:
sorry for my delay answer. I have a customer, who has a replacement variable and this is mandatory. Here are a sample screenshot. It works normal.
and here the settings from Z_YEAR
So you can have a replacement variable which is input ready.
Thank you for your reply.
You mentioned a mandatory replacement path variable in your first info.
Thus I thought you wanted to change the value of this variable within the callback.
According to your screenshots I understand now, that it is not the variable you want to set, but the input variable is the one you use as so to say “source”.
The scenario is more complex as it looked like in the first place.
Nevertheless, at a first glance I assume, that it works if you move the replacement path variable to the “Characteristic Restrictions” – Area (Static filter).
In case this does not work, we’d need a customer message on the component BI_RA_AO_XLA.
Is it possible to get a excel example to download? This would help me alot!
I can send you the example per mail.
Could you provide me your email address, please?
Can you follow me and I will send it with a direct message.
can you please send this template to email@example.com it would be a great help.
Thanks a lot
can you please send me the excel via Email? firstname.lastname@example.org - it help me a lot! Thank you!
How to enable analysis Addin using macros?
The following code:
If Application.COMAddIns.Item("SBOP.AdvancedAnalysis.Addin.1").Connect = False Then
Application.COMAddIns.Item("SBOP.AdvancedAnalysis.Addin.1").Connect = True
Is resulting in subscript overrun error. Is the addin name "SBOP.AdvancedAnalysis.Addin.1" the same for analysis 2.2 SP02?
Thanks in advance,
"SBOP.AdvancedAnalysis.Addin.1" needs to be replaced by "SAPExcelAddIn"
(since AnalysisOffice 2.x).
Thanks for your help. That worked 🙂 .
Got one more doubt.
Is there a scenario where the "advanced calculation" feature might not work in Analysis 2.2?
Thanks in Advance,
Pallavi Indukar A
Thank you for your post ➕ ➕ . Having trouble getting BeforeFirstPromptsDisplay to trigger. SAPSetVariable do not push values into the variable screen. Is your example workbook on the SAP BO server -or- will / should the process work on a local desktop workbook?
Hello Marvin and Isabella.
I also have problems to trigger the CallBack. It is registered fine, all other CallBacks are working fine but the important one BeforeFirstPromptsDisplay seems never triggered at all. I tried to trigger it manually by changing variables settings (as it is written in the user manual), restarted the workbook million times etc, nothing worked.
Marvin, did you solve this issue since then?
My Analysis version is 2.4 SP1 p 2
After many hours trying, I would really appreciate your answer / or Isabellas workbook (email@example.com), that would also help me so much...
Thank you, Martin
You callback function must have the parameter dpNames As Variant for it to be triggered. This is the example in the documentation (2.4.1):
Thank you very much, this was a new information to me.
I´ve got i working somehow 🙂
Thank you again!
I am fairly new to VBA coding and I have the same issue as Martin (at least that's what I think).
If my workbook is refreshed everything seems to work fine. But before the first refresh I can't push the variables into the variable screen.
Actually, I don't even understand what "dpNames" stands for or when/where I fill it with values?!
After reading your post I tried to copy your example before Isabellas code but it didn't work out:
Could you tell me what "dpNames" actually is and where and with what I have to "fill" it?
Many thanks in advance,
dpNames is a one-dimension array with the formula alias of each Data Source that you see on the Prompt screen (e.g. "DS_1", "DS_2"). It is filled by Analysis when the callback is called. This is why it must be present for the callback to be called at all. I don't think you necessarily need to use it to set the variables. You can just use:
Call Application.Run("SAPSetVariable", ...
Thank you! The documentation says “' defined in a module”.
Indeed, my tests show that the callback (BeforeFirstPromptsDisplay) will be triggered only if it is in module of the current Excel workbook. It makes problems for me. I test it with Analysis 2.4 sp4.
We have analysis 2.2 yet and it works differently. We have our Macros in one central .xlam file, and that is great.
Now due to migration from 2.2 to 2.4 I will have to adjust countless Excel files and put this callback in every single workbook’s module. And our excellent centralization of macros will be disturbed.
Does anybody see another way there to handle this?
Thank you in advance!
I'm having some issues with Custom made macros and the execution of the CallBack_AfterReDisplay.
My Workbook has 2 Datasources, when saving the WB, the CallBack_AfterReDisplay is executed 3 times, why it is executed so many times, shouldn't be just one time?
Also, I need to execute some macros only after all the Datasources are refreshed. How can I do this?
Many thanks in advance.
Move the AfterRedisplayRegister out of module ThisWorkbook and into your custom macro. Then sequence the AfterRedisplayRegister following the refresh of DS_1... the Callback would then only run once (e.g., after DS_2 refresh).
If you are refreshing DS multiple times, you may want to consider using UnregisterCallback. By naming Callback functions uniquely (e.g., Callback_DS1, Callback_DS2, Callback_Final), you can register and unregister in a sequence that is conducive to your custom macro.
Thanks for you reply.
In fact, I'm a little bit lost with your answer.
I have 2 Datasources and need to execute some macros after these DS are refreshed.
In the end what I will need is:
Step 1 - Refresh all DS
Step 2 - Execute Macro 1
Step 3 - Execute Macro 2
What I have now is:
Public Sub Callback_AfterReDisplay()
But what I noticed is that the Callback_AfterReDisplay is executed several times, when what I expected was that it was executed only once.
Many thanks for your help.
For Callback_AfterReDisplay to trigger, a function was called to register the callback itself... a function such as "AfterRedisplayRegister". Since the Callback is tripping multiple times, the AfterRedisplayRegister is being initiated too soon and subsequently being triggered after every DS is refreshed, not just the last DS. I would guess that the AfterRedisplayRegister function is in "ThisWorkbook" module and is registered as soon as the workbook opens.
Find the "User Guide" (mid page right side) on the page link above and search the pdf for "afterredisplay". There a a lot of good examples on practical application.
My suggestion would be to place a macro that will refresh each DS and register the callback before the last DS refresh is triggered... something like:
I know this is an old post but I can't get the code to work on v2.8. Is it possible to get a copy of your excel workbook?
Thanks in advance!