Skip to Content

Using the VBA API in SAP BusinessObjects Analysis Office 1.1 Webcast Notes

Tobias Kaufmann, Customer Solution Adoption (formerly known as RIG), provided a webcast as part of the SAP NetWeaver Know-How Network Conference series in SAP BusinessObjects Analysis 1.1.  Version 1.1 is currently in ramp-up, he said look to be GA for some time in July.

The purpose of this webcast was to provide information on about the new API functions in SAP BusinessObjects Analysis Office 1.1.  The API can be used  to build sophisticated workbooks in Analysis.  Below are my notes from this webcast and at the end I provide an example of how I applied what I learned from this webcast.  Because the product is in ramp-up the usual disclaimer applies and things are subject to change.

The agenda was as follows:

  • Enable Analysis Office Add-in
  • Refresh
  • Drilldown with Button
  • Set Filter
  • Dynamic Grid

       

image

Figure 1 – Use the Online Help as a Reference: Source: SAP

Tobias suggested looking at the Working with Formulas help, as most are already available in the 1.0 version.  The change in 1.1 is “Working with Macros” where new functions such as SAPMoveDimension SAPAddMessage, and SAPGetCellInfo have been added.  The help contains some examples that help you call these functions.

What is the API Used For?

Tobias explained that the API is used for building sophisticated BI workbooks.  The API can be used for formulas or macros.

Formulas  are entered behind a cell and for retrieving information, getting data and showing data.

The difference between formulas and macros is that we are executing functionality with macros, such as moving a dimension, performing a drilldown, or a UI function.  You can also execute planning functions and sequences. Normally the Application.Run option is used inside the macros.

Enable Analysis Office Add-In

When you start to work with macros that are performed automatically when the workbook is opened we have to ensure that the Analysis Office Add-in is really loaded

image

Figure 2 Source: SAP

Figure 2 shows the Analysis tab.

Open VBA and it is in ThisWorkbook object and copy in this code as shown below:

Option Explicit

Private Sub Workbook_Open()

    Call EnableAnalysisOffice

End Sub

Private Sub EnableAnalysisOffice()

    Dim addin As COMAddIn

    For Each addin In Application.COMAddIns

        If addin.progID = “SBOP.AdvancedAnalysis.Addin.1” Then

            If addin.Connect = False Then addin.Connect = True

        End If

    Next

End Sub

image

Figure 3, Source: SAP

To view the macros, go to the Developer toolbar and select Visual Basic, as shown in Figure 3.

Refresh

In the Refresh scenario, Tobias said you wanted to ensure that data is refreshed before calling your own functions or SAP functions.  He suggested using error handling for the refresh.  See the following code:

Public Function MyGetData() As String

    Dim lCellContent As String

     On Error GoTo refresh

    lCellContent = Application.Run(“SAPGetData”, “DS_1”, “4J97S26KX1BYBQ4GGQJNZGD9T”, “0D_PH1=DS20”)

    MyGetData = lCellContent

     Exit Function

refresh:

    Dim lResult As Long

     MsgBox “Refresh”

     lResult = Application.Run(“SAPSetRefreshBehaviour”, “Off”)

    lResult = Application.Run(“SAPExecuteCommand”, “Refresh”)

    lResult = Application.Run(“SAPSetRefreshBehaviour”, “On”)

     lCellContent = Application.Run(“SAPGetData”, “DS_1”, “4J97S26KX1BYBQ4GGQJNZGD9T”, “0D_PH1=DS20”)

     MyGetData = lCellContent

End Function

As shown above, MyGetData function is a public function and if you define a public function you can call it like you would a normal formula.  It shows we are getting data from a specific cell content.  Included is some error handling; if it is in error it executes the refresh method.

A tip from Tobias is to change the refresh before and after calling it to avoid conflicts of refreshing Excel and SAP data.

You can review the Refresh in the Analysis Help button under the “Working with Macros” section.  You can refresh all data sources as the example shows or only refresh a specific data source.

Drilldown with a Button

image

Figure 4

Figure 4 provides an example of using a button for pre-defined navigation.

image

Figure 5 Source: SAP

If you do not have the Developer toolbar active, go to Excel options.  Figure 5 shows that we are inserting a button for the macro.  Double clicking on the button will bring up the editor.

image

Figure 6 Source: SAP

Figure 6 shows the Excel variable ActiveCell which is the selected cell and we are checking to see if there is some Excel information.  The information is retrieved.  The code is shown below:

Sub Button1_Click()

    Dim lResult

    On Error GoTo leave

    lResult = Application.Run(“SAPGetCellInfo”, ActiveCell, “DIMENSION”)

    lResult = Application.Run(“SAPMoveDimension”, lResult(1), “0CALYEAR”, “BEFORE”, lResult(2))

    Exit Sub

leave:

    lResult = Application.Run(“SAPSetRefreshBehaviour”, “Off”)

    lResult = Application.Run(“SAPExecuteCommand”, “Refresh”)

    lResult = Application.Run(“SAPSetRefreshBehaviour”, “On”)

    lResult = Application.Run(“SAPGetCellInfo”, ActiveCell, “DIMENSION”)

    If IsError(lResult) = True Then

        MsgBox “No dimension selected.”

    Else

        lResult = Application.Run(“SAPMoveDimension”, lResult(1), “0CALYEAR”, “BEFORE”, lResult(2))

    End If

End Sub

image

Figure 7 – Stepping through the Code, Source: SAP

In Figure 7, Tobias set a breakpoint so we could see the results of the code. IResult(1) is the data source and IResult(2) is the dimension.

image

Figure 8, Source: SAP

Figure 8 shows how Tobias is passing the parameters lResult(1) and IResult(2) to the SAPMoveDimension macro function.

image

Figure 9, Source: SAP

Figure 9 shows the results of clicking the button – calendar year is now before product.

The code below shows Tobias’s tip with the drilldown – he suggests combining all three together: enable Analysis Office Add-in, Refresh and Drilldown with Button.  The code shown in Figure 12 shows the macro first tries to retrieve the cell information.  If not successful, then it goes to the “leave” part of the program.

Option Explicit

Sub Button1_Click()

    Dim lResult

    On Error GoTo leave

    lResult = Application.Run(“SAPGetCellInfo”, ActiveCell, “DIMENSION”)

    lResult = Application.Run(“SAPMoveDimension”, lResult(1), “0CALYEAR”, “BEFORE”, lResult(2))

    Exit Sub

leave:

    lResult = Application.Run(“SAPSetRefreshBehaviour”, “Off”)

    lResult = Application.Run(“SAPExecuteCommand”, “Refresh”)

    lResult = Application.Run(“SAPSetRefreshBehaviour”, “On”)

    lResult = Application.Run(“SAPGetCellInfo”, ActiveCell, “DIMENSION”)

    If IsError(lResult) = True Then

        MsgBox “No dimension selected.”

    Else

        lResult = Application.Run(“SAPMoveDimension”, lResult(1), “0CALYEAR”, “BEFORE”, lResult(2))

    End If

End Sub

Set Filter

image

Figure 10, Source: SAP

Figure 10 shows the various formats to select the filter.  Tobias recommended again reviewing the Analysis help to review the SetFilter functionality and parameters.

image

Figure 11, Source: SAP

For the SetFilter to work, you have to select the right format in this example.

image

Figure 12, Source: SAP

Figure 12 shows how to set up a combo box.  First, go to the Developer tool bar, select Insert, then select combo box.   Then go to the cell, right click, and from there you can assign the macro, or select Format control.

The right side of Figure 12 shows the Control tab of the Format Control.  You have an input range and a cell link. The input range is the values you would like to show in the dropdown box.  The cell link the selected index is passed.

image

Figure 13, Source: SAP

Figure 13 shows that Tobias put the information to be passed on a second sheet.  Circled in blue (Column A) is the input range, green is the cell index (column B) and Column C is the index function (out of this range, what is the value of Index 4).

The code below shows the Assign macro to control

Option Explicit

Sub DropDown2_Change()

    Dim selectedType As String

    Dim selectedValue As String

    Dim dimension As String

    Dim formulaAlias As String

    Dim r

    selectedType = Worksheets(“Sheet2”).Range(“C1”).Value

    selectedValue = Worksheets(“Sheet2”).Range(“C7”).Value

    dimension = “0D_PH2”

    formulaAlias = “DS_1”

    r = Application.Run(“SAPSetFilter”, formulaAlias, dimension, selectedValue, selectedType)

End Sub

Dynamic Grid

A dynamic grid keeps formatted cells after refresh and during navigation.

image

Figure 14, Source: SAP

Figure 14 shows the event Workbook_SheetChange to reaction on changes.  As an example, if you click the delete button it will remove the dimension product group.

The second example is to type in a dimension a drilldown will be performed.

image

Figure 15, Source: SAP

Figure 15 shows the code behind the Worksheet Event change.

Question & Answer:

Q: Can I use existing planning function from an aggregation level built on BW?

A: Yes; look at this part of the help (Source: SAP)

image

In 1.2 release it is planned to be more interactive

Q: Re: SetFilter – can I use a Data Source as content in a drop down box (Filter) e.g. Master Data (BW)

A: Yes

I want to thank Tobias Kaufmann for a great webcast and thank you Alex Peter, SAP, for reviewing this.

I was able to successfully follow the webcast and apply it in an Analysis workbook here thanks to this webcast (link plays better in Internet Explorer).  Some lessons learned include to always use the Analysis help for macro syntax.  Tobias has shared his examples at this link.  I encourage you to listen to the webcast FEATURED EVENTS and visit the Analysis Page for more information.

To report this post you need to login first.

11 Comments

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

  1. Olaf Fischer
    Dear all,

    you can ask a data source if it active/refreshed or not:
    lResult= Application.Run(“SAPGetProperty”, “IsDataSourceEditable”, “DS_1”)

    Using this the implementation proposal could be signicantly improved – just ask in the error handler if it is inactive, if yes perform a refresh , if not something else caused the problem.

    Best regards, Olaf

    (0) 
  2. Marcel Salein
    Hi Tammy,

    very interessting and detailed blog. I have installed so far Analysis Office 1.1 and I have checked the documentation regarding Macros.

    I know from BEx Analyzer that it is possible to use the BEx connection to BW to call RFC function modules. I wonder if this is also possible in the future due to the fact that I have some customers who are using this feature to call function modules in their workbooks.

    I would really appreciate if you could provide us with some insights if this is possible.

    Thanks in advance for you support.

    Cheers,
    Marcel

    (0) 
    1. Tammy Powlas Post author
      Hello Marcel and thank you for responding.
      I haven’t used the feature of the BEx connection to BW to call RFC function modules – can you provide a business/use case for this?  I would be interested to know.

      Thank you,
      Tammy

      (0) 
      1. Marcel Salein

        Hello Tammy,

        some of our customers using the capabilities of BEx to call function modules in order to store comments in BW. The filter values and the comment is passed to a function module to write the information into BW and another function module passes only the filer values and returns the comments back to Excel. My customer is considering to replace the existing BEx solution with Analysis Office, however it is critical for the customer to call function modules for the comment functionality that is already implemented in BEx.

        Cheers,
        Marcel

        (0) 
  3. Gerardo Rendina

    Hello Tammy,

     

    I have a short questions concerning VBA within Excel 2010. If I wand to save a workbook with a macro, so I have to save it as a xlsm file. But when I try to open that file within BI Launch Pad, I get an error message -> file format and / or extension invalid.

     

    Isn’t it possible to save files on BI Plattform with macros?

     

    Best regards,
    Gerardo

    (0) 
  4. Rui Romba

    Hi everyone,

     

    I have a Workbook with a button to save data. The macro associated to this button first runs a planning sequence and then the SaveData.

     

    Can you please tell me if it is possible to run the SaveData only if the Planning Sequence runs OK?

     

    How can I do that?

     

    Many thanks.

     

    Rui

    (0) 
  5. Borislav Kostadinov

    Dear Tammy,

     

         All above is very usefull for which I thank you! I have one question, I have problems when I have a VBA code ( excel format .xlsm or .xlsb format ) and I am trying to activate the Analysis Add-in. In this situation the file would go into not reponding stage for a very long time until the add in is activated. If I have the add-in in xlsx file format I dont have such problems. Have you experienced such similar issues?

     

    Thanks for the feedback

    Regards

    Borislav

    (0) 

Leave a Reply