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.

Getting Started

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. This action opens a pop-up window with the entire list of functions (Figure 2). 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. Select the formula you want to insert into your worksheet and click the OK button.

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.

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

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.

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). 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 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. Using the VBA macro editor, insert the code snippet shown in Figure 17. 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). 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. 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. 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.