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
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
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
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
Figure 4
Figure 4 provides an example of using a button for pre-defined navigation.
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.
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
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.
Figure 8, Source: SAP
Figure 8 shows how Tobias is passing the parameters lResult(1) and IResult(2) to the SAPMoveDimension macro function.
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
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.
Figure 11, Source: SAP
For the SetFilter to work, you have to select the right format in this example.
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.
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.
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.
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)
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.
- SAP Managed Tags:
11 Comments
