Serious Excel users, such as analysts, probably have extensive experience using Microsoft Excel’s library of formulas. SAP augments this capability by providing a library of formulas that enable advanced analysis. Why should this matter to you? The answer is simple: Microsoft Excel provides you with general functions, while SAP BusinessObjects Analysis for Microsoft Office formulas provide functions that are tailored to extracting the maximum information from your SAP functionality, data, and metadata. Additionally, by combining these formulas with those provided natively by Microsoft Excel, you can build some additional capabilities that enhance the power of SAP BusinessObjects Analysis for Microsoft Office.
By incorporating formulas and APIs into your analysis view, you can transform your queries into self-contained applications that are customized to meet your informational, analytical, and formatting needs. In these example scenarios, I show how to use both SAP BusinessObjects Analysis for Microsoft Office formulas and API libraries. These methods help you unleash the latent power of these libraries.
Once the SAP BusinessObjects Analysis for Microsoft Office application has been launched and the familiar Excel spreadsheet interface opens, go to the Formulas tab (Figure 1). Place your cursor on an empty cell in the spreadsheet and then click the Insert Function button.
Click the Insert Function button
This action opens a pop-up window with the entire list of functions (Figure 2).
Select a function to be inserted in the worksheet from the list of available functions
This brings up the question, how do you know which functions are SAP functions? In the category drop-down field (e.g., Or select a category field), select the Analysis option (Figure 3). This action opens a display of all the SAP BusinessObjects Analysis for Microsoft Office functions in the Select a function box underneath.
A list of SAP formulas
Select the formula you want to insert into your worksheet and click the OK button.
(Note: For guidance on how to use a specific formula, select the desired formula and click the Help on this function link (on the bottom left of Figure 3). This link provides information about the input parameters and the expected output. It also usually contains an example. If you are new to these formulas or to SAP BusinessObjects Analysis for Microsoft Office, in general, this is a great reference point to learn more.)
Using Formulas in SAP BusinessObjects Analysis for Microsoft Office to Enhance Queries
Now, let’s look at some ways to use some of these formulas to enhance queries.
(Note: The query that I created for this article is based on standard SAP BW Business Content InfoCube 0FIAR_C05. It is in the FI Accounts Receivable InfoArea and contains FI payment history.)
The SAPGetInfoSource Formula
This formula accepts two inputs: the data source’s alias/name and the property whose value you seek. In return, it outputs the value of the property. However, you can retrieve only one property value for one invocation of the formula. In this example scenario, I created a custom area in the worksheet and labeled it Query General Information (Figure 4). I also included the description of each of the four properties or fields that I want retrieved by this formula.
An SAP BusinessObjects Analysis for Microsoft Office worksheet with a general query overview area
The next step is to populate the cell next to the Query Source with the SAPGetInfoSource formula. Place your cursor on the empty cell next to the Query Source field in Figure 4. Then go back to the SAP functions list in Figure 3 to select a function with which to populate the cell. In this example, select SAPGetInfoSource. After you select this function, a pop-up window opens asking you for the values of the two inputs to be displayed (Figure 5). Enter the value of the data source (which is by default “DS_1”) and the name of the property, and click the OK button. The value of the InfoProvider technical name is displayed (Figure 6).
Enter the values in the Data Source and Property Name fields with the formula
The query’s general information fields are populated with additional information from the formula’s values
Figure 6 shows the results. Notice that the formula has returned the values. The Query Source field in Figure 6 is now populated with the technical name of the InfoProvider—in this case, 0FIAR_C05. Now, back in Figure 3, invoke the formula for the remaining fields and enter values for the relevant property names in Figure 5—the Query Technical Name, Query Creator, and Query Last Refreshed At fields—to get the values shown in Figure 6.
The SAPListOfDimensions Formula
The SAPListOfDimensions formula provides the list of dimensions for the analysis fields in your rows and columns. The screen in Figure 7 shows the parameters for this formula. (This is the same pop-up as shown in Figure 5, but this time you need to insert different values.) In this case, “DS_1” is still the (default) Data Source, “AXIS” is the Search In parameter, and “ROWS” is the Search parameter. Once you’ve made your entries, click the OK button (not shown) to output the data to the Excel worksheet.
Provide arguments for the list of the dimensions’ function
The output of this formula appears in the worksheet along with the data (Figure 8), assuming you had originally placed your cursor on an empty cell in the spreadsheet. As you can now see, the company code is part of the 0COMP_CODE dimension, the customer is part of the 0DEBITOR dimension, and the fiscal year is part of the 0FISCYEAR dimension in the underlying InfoCube.
The query results with the new list of displayed dimensions
(Note: The library of functions that SAP BusinessObjects Analysis for Microsoft Office provides does not allow for calculations. Calculations can be done using regular Excel formulas. Since the scope of this article is SAP BusinessObjects Analysis for Microsoft Office formulas, I cover only them. The following section is about one of these get-member formulas. The features discussed previously are easily done using the insert infofield option and without knowing any functions.)
The SAPGetDimensionDynamicFilter Formula
SAP BusinessObjects Analysis for Microsoft Office offers many functions that fetch information from the underlying data sources. These functions allow you to combine that information with the navigation activities that you carry out with the displayed results. The SAPGetDimensionDynamicFilter formula allows you to display the filters that you dynamically set during navigation. In this scenario, I’m only interested in these four customers: C00, C02, C03, and C04. Therefore, I moved these four customers to the filter section as shown in Figure 9. (The filtered company codes are boxed in red.)
The filtered customer list
Place your cursor on an empty cell and call the relevant function from the list of functions as before. Once the relevant function is selected (SAPGetDimensionDynamicFilter), you need to insert the necessary values (Figure 10). Enter the data source, in this case the default “DS_1”. Since you filtered the results based on the company code dimension, provide 0DEBITOR as the value for the Dimension field since 0DEBITOR is the technical name of the company code dimension. Finally, since you want the filter information to display as text, enter TEXT as the value for the Member Display field. Click the OK button.
Provide the values to the function fields
The filtered information is now displayed in the cell where you originally placed your cursor (Figure 11). For easy identification, I named the cell Dynamic Filters (Company Codes).
The results of the SAPGetDimensionDynamicFilter function
APIs in SAP BusinessObjects Analysis for Microsoft Office
SAP BusinessObjects Analysis for Microsoft Office provides you with both basic and advanced options for analysis. You can also embed features and functions that are not available to you out of the box. Since it is an add-on to Microsoft Excel, it also allows users to leverage Visual Basic for Applications (VBA) programming language macros that are an integral feature of Excel. This capability is significantly enhanced by the ability to embed a set of standard SAP APIs in these macros. When I compare SAP BusinessObjects Analysis for Microsoft Office formulas with APIs, the former scores higher in ease of use. APIs are really a programming artifact and are more suited for use by power users or technically savvy users.
Macros are accessible through the Developer tab in the main menu of Excel. This tab, however, does not appear by default. If it is not shown in your main menu, follow Excel menu path File > Options. In the screen that opens (Figure 12) choose Customize Ribbon. This action opens the Customize the Ribbon screen on the right. Here the Developer tab can be activated by selecting the check box next to it. After you click the OK button, the Developer tab should now be visible in your Excel workbooks. Once it’s added, you can write macros and run previously recorded macros.
Enable the Developer tab in Excel
Using Selected APIs in SAP BusinessObjects Analysis for Microsoft Office
In this section, I show you how to use the APIs that I believe are the most useful in specific scenarios.
The SAPSetFilter API
This API enables you to filter by individual members of one or more dimensions. Some technically savvy SAP BusinessObjects Analysis for Microsoft Office users prefer to use this macro or API and pre-set filter conditions based on their custom needs instead of achieving the same outcome via drag-and-drop functionality.
In this example query, you only care about customer C00 and want to use this API to set this value. Go to the Developer tab in the main menu and click the Macros button (Figure 13). This action displays a pop-up prompting you to enter a new name for your macro. In this case, call the macro Set_filter. After you click the Create button, the Visual Basic macro editor pop-up screen opens with the mandatory opening and closing statements pre-populated (Figure 14).
Create a macro to package your API
Call the SAPSetFilter API with the appropriate parameters
Add the code snippet as shown in Figure 14 and click the Run option and then the Run Macro button (not shown). Once the macro has run, close the macro screen and return to your query. You see that the filter has been applied and the results are filtered for the customer C00, as shown in Figure 15.
The result set after the macro is run with filters
The SAPMoveDimension API
This API helps sets the location of a dimension in your worksheet, swaps one dimension with another, or moves a dimension with respect to another one. Although this behavior might seem primitive when you can do the same things using drag-and-drop functionality, some users still prefer simple programmatic techniques for slicing and dicing over drag-and-drop functionality. For those users, this API is very useful.
In this scenario, I have done some slicing and dicing with this query and now want to drill down into the data using customer, company code, and fiscal year parameters, in that order. Figure 16 displays the partial screen of my query results. Additionally, I want to introduce an additional dimension—the posting period—as the fourth drill-down parameter.
The Excel worksheet after some slicing and dicing
Using the VBA macro editor, insert the code snippet shown in Figure 17.
Call the APMoveDimension API with appropriate parameters
Run the API (macro). After you run this macro, switch to your Excel worksheet to check the results. The posting period has been added as the fourth column, and the rest of the new data is displayed accordingly (Figure 18).
The result set after the macro is run
There is one interesting result I want to point out. If you compare Figure 16 against Figure 18 after this API is run, the result of the formula used earlier in this article (SAPGetListofDimensions) is now automatically updated and now also lists the posting period as a displayed dimension.
(Tip! As a consumer of the data, it would be convenient if the above API or macro or all macros could be run at the push of a button. This can be done in SAP BusinessObjects Analysis for Microsoft Office. Let me explain the steps. Place your cursor on an empty cell in an Excel worksheet and click the Insert button [Figure 19]. Then click the push-button icon under Form Controls.)
Insert a button for running APIs or macros
This action opens a pop-window that prompts you to assign a macro to the button (Figure 20). In this case, select the Set_filter macro. Click the OK button.
Assign a macro to a button
Back in the Excel worksheet, in what had been an empty cell, now a button shows up (Figure 21). Give the new button an appropriate label—in this case, Set Format API—and that’s it. Going forward all you need to do is to click this button for your API or macro to run. This approach can be extended for all the APIs or macros that have been created so that you have a set of descriptive push-buttons to click for each of these APIs or macros you wish to execute.
The Excel worksheet with the new button for executing the Set Format API (macro)