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


Report_Aufbau.jpg

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.

AO_VarPrefil_PersVarVal.jpg

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:

Button_refrDS.jpg

(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.

HelpTable.jpg

(3)  Report

Additionally you can set „Force Prompts“

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


ForcePrompts_Optional.jpg


(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.

Macro_01.jpg

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

Macro_02.jpg

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

Macro_03.jpg

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

Macro_04.jpg

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

Macro_05.jpg

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.


To report this post you need to login first.

24 Comments

You must be Logged on to comment or reply to a post.

      1. Lokesh Shivanna

        hi Isabella,

        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.

        Thank you

        Lokesh

        (0) 
  1. Tobias Meyer

    Hi,

    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?

    regards

    Tobias

    (0) 
      1. Tobias Meyer

        Hi Isabella,

        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.

        /wp-content/uploads/2016/05/replacement_var_962675.png

        and here the settings from Z_YEAR

        2016-05-02 10_47_50-Change Variable.png

        So you can have a replacement variable which is input ready.

        Any ideas?

        Regards

        Tobias

        (0) 
        1. Isabella Leuschner Post author

          Hi Tobias,

          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.

          Best regards,

          Isabella

          (0) 
  2. Pallavi Indukar

    Hi,

    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

            End If

    Is resulting in subscript overrun error. Is the addin name “SBOP.AdvancedAnalysis.Addin.1” the same for analysis 2.2 SP02?

    Thanks in advance,

    Pallavi.

    (0) 
      1. Pallavi Indukar

        Hi Isabella,

        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

        (0) 
  3. Marvin Johns

    Isabella,

    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?

    mj

    (0) 
    1. Martin Hájek

      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 (martin.hajek@mibcon.cz), that would also help me so much…

       

      Thank you, Martin

      (0) 
      1. Claus Eriksen

        Hi 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):

        Public Sub onBeforeFirstPromptsDisplay(dpNames As Variant)
        
           Dim dpName As Variant
            For Each dpName In dpNames
                If dpName = "DS_1" Then
            Call Application.Run("SAPSetVariable", "COUNTRY", "EN", "INPUT_STRING", "DS_1")
               End If
            Next
        
        End Sub
        
        (1) 
        1. Jan Bauch

          Hi Claus,

          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,

          Jan

          (0) 
  4. Rui Romba

    Hello Isabella,

    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.

    Rui

    (0) 
    1. Marvin Johns

      Rui,

      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.

      mj

      (0) 
      1. Rui Romba

        Hi Marvin,

        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()

        Application.Run (“Macro_1”)

        Application.Run (“Macro_2”)

        End Sub

        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.

        Rui

        (0) 
        1. Marvin Johns

          Rui,

          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.

          https://wiki.scn.sap.com/wiki/display/BOBJ/How+to+Guidelines

          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.

          mj

          (0) 
        2. Marvin Johns

          Rui,

          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:

          snp.PNG

          mj

          (0) 

Leave a Reply