The main focus of this article is about how to use formula mode with SAP’s Excel-centric products, but before I dive into that, it is important to note that there are other web-based tools available and that there is a very big push to use them as many companies don’t want to maintain PC-based software anymore. SAP offers a lot of tools and most companies don’t use every one, at least not initially.

First I give a quick overview of all the tools that are out there. Armed with these basics, as you read the article, you will understand why formula mode makes an Excel-based tool a good choice to selectively deploy to some users. To be clear, most SAP BW and BusinessObjects tools are web based. There are two tools that are Excel based: the newer BusinessObjects Analysis and the older BEx Analyzer, and these are the only ones with formula mode. Although these Excel-based tools come with some disadvantages (namely PC installation and maintenance and the added costs of purchasing Microsoft Office versus the free web-based ones), the underutilized formula mode tool changes the way data is extracted from BW and sent to Excel. This feature alone, in my mind, moves SAP’s Excel-based tools up to the top of the tool list, at least for a few key users.

An Overview of SAP BW and BusinessObjects Reporting Tools

With the acquisition of BusinessObjects, SAP has amassed a huge tool set of reporting tools. For edification, I have compiled a list of the options, along with a short description of each, in Table 1. The ones marked with ** (asterisks) are licensed separately from the core SAP BW.

Why Use Excel?

Although many IT departments don’t like having to maintain Microsoft Windows applications on PCs spread around the world, it’s an uphill battle to convince the FI guy that Excel is a drug they can do without. SAP has obviously thrown in the towel. As evidence, when SAP purchased BusinessObjects, existing SAP customers said, “Where’s my Excel?” Rather than argue why Web Intelligence could solve their needs, SAP created BusinessObjects Analysis for Microsoft Office (e.g., Excel and PowerPoint).

So why does an Excel interface to SAP BW need to exist, and what can analysts do with Excel that can’t be done just as easily using web-based tools? The answer is, really not much, but some tasks are harder to accomplish without Excel; for example, calculating new measures that integrate local spreadsheet data.

Basic use cases of Excel (those that don’t require merging data or wacky custom calculations) can be easily replicated using Web Intelligence, Analysis for OLAP, or even well-designed dashboards. However, the use case that justifies Excel is one in which local data on sheet 7 is merged with data from BW, then pivoted on sheet 2, with Visual Basic for Applications (VBA) logic thrown in for kicks. Even this use case can be nearly eliminated by using the Analysis Process Designer (APD) via BW transaction code RSANWB.

The APD targets this advanced analysis with a robust upload of data and features to merge and manipulate data. In addition, the APD joins with the data-mining workbench for extremely advanced analysis capabilities. Even better, when running BW on HANA, the newer version of the APD (called an SAP HANA Analysis Process), executes most of this complex logic on the HANA database, not in the application server layer, resulting in super-fast performance. Logic says these methods are better than using Excel. Why download 1,000,000 BW records to Excel and merge them with 10,000 local records, versus doing the opposite and uploading the 10,000 records with access to the power of APD or the newer HANA version? As I have learned, logic, non-Excel tools, and FI guys don’t mix. Excel always seems to win, at least for now.

Formula Mode: A Use Case for Excel

Although, as you can see I am not a great proponent of extensive reliance on the SAP Excel-based tools (BEx Analyzer and BusinessObjects Analysis), there is one important technique that justifies their continued use: the formula mode feature.

One area where Excel shines is linking local spreadsheet data with data originating in a BEx Query, all in the same workbook. Problems with linking data are caused by the default way data in SAP BW is exposed in the Excel spreadsheet. As a default, the data is downloaded as a pure set of characters and numbers and, when the analysis grid of data in Excel is refreshed, new numbers and their attributes are downloaded as a set. This is illustrated in Figures 1 and 2. In Figure 1 the highlighted calculation in cell F4 ([B4–D4] = ABC SuperMarkets sales – Returns), works fine as the sales data from SAP BW for customer ABC SuperMarkets is in B4 and their returns are in D4.

Figure 1

Formula referencing cells containing SAP BW data (B4 – D4)

Figure 2

The data from SAP BW is refreshed, creating a problem with the calculation in Excel

However, the problem with this default method of presenting BW data becomes evident in Figure 2, when the data from SAP BW is refreshed. All the calculations in Figure 1 become incorrect when SAP BW provides refreshed data that includes a new record, AAA Supermarkets. Since this customer is not in the local data columns (in Figure 1), the inserted row makes the F4 cell calculation wrong in Figure 2. Instead of AAA SuperMarkets sales – Returns it stays ABC SuperMarkets sales – Returns. The SAP BW default mode for presenting data in Excel causes errors when new data is added and the cells change meaning. Formula mode (and a similar method, convert to formula mode), is different. It presents data to Excel in a different way, and solves this problem. It downloads the SAP BW data into Excel with a separate formula for each cell. Therefore, each cell’s formula links to the data in SAP BW, returning exactly what the formula specifies.

Formula Mode with BusinessObjects Analysis

Now let’s see this in action. My first example uses BusinessObjects Analysis for Excel. Figure 3 shows a screenprint of the data after a BW Query has been executed in the Excel Workbook in the normal way. Randomly select a cell in the analysis grid and look at the formula line for it. No formula appears—just the number in the cell. This is not the way formula mode works, as I show.

Figure 3

The normal presentation of SAP BW data in cells (there is no exact cell formula)

Figure 3 The normal presentation of SAP BW data in cells (there is no exact cell formula) Converting to formula mode is easy. In the Analysis tab, in Tools, click the Convert to Formula icon (Figure 4). This switches the spreadsheet to formula mode. At first glance, the numbers and attributes look the same, but upon closer inspection, the formula for each cell ties the cell independently to the SAP BW Query output, not as a collective grid of numbers and characters as it was before. In Figure 3, the highlighted formula might not look right, but the long cryptic string is the technical ID for the Sales Volume EUR key figure. Knowing this, the formula is easy to interpret. Simply stated, the formula returns for DS = 1 (my formula mode query), the total sales for all customers in the United Arab Emirates (AE).

Figure 4

Data after converting to formula mode

To illustrate the independent nature of these formulas, Figure 5 shows the formula randomly placed in another cell on the spread sheet. It still returns the data per its formula logic.

Figure 5

The cell formula outside the context of the other cells

The formula’s logic is based on custom Excel functions that are installed on the SAP system as part of the BusinessObjects Analysis add-in or, similarly, BEx Analyzer (discussed in the next section).

To get help using this formula, select formula help in Excel as you would normally (the small fx icon circled in Figure 6). This opens up the pop-up on the right of the figure where you can see a list of all of the SAP-delivered functions in a list along with other Excel standard functions. These SAP functions work with BW data (each begins with SAP). These functions perform tasks such as returning variable values selected by the user or returning the background filters assigned in the query. (To find out more about these functions, look at the topics in the Contents tab or use the Search tab in the pop-up screen as you would to find out information about other non-SAP functions, shown at the bottom of Figure 6).

Figure 6

A list of available functions in the SAP system

Once you know about these SAP-specific functions and how to use them, the world is your oyster. With this new-found knowledge, now you can easily solve the issues presented in Figures 1 and 2.

To be clear, as long as the Worksheet has an embedded query behind the scenes, there is actually no need to display the SAP-supplied grid of data produced by the query when using formula mode. To illustrate this, Figure 7 shows an Excel Workbook that gets its data from the same query as the one shown in Figure 5, but the SAP-generated default presentation of the data that used to be on sheet one (in Figure 5) is not shown at all. The data shown here (Figure 7) are the data of the formulas directly linking to SAP BW via the imbedded (but hidden) query.

Figure 7

Formula mode with the cell formulas linking SAP BW data with cells that contain local data, not SAP-delivered data

In this same Worksheet (Figure 7) with the same embedded formula mode query (Data source1), I entered in a bunch of cells manually. Make special note of the Sale Report for: DE cell in D4 and the technical names of the customers in C14 – C22. Now let’s focus on the formula.

Although nearly the same as the prior example, in this case I replaced the hard-coded DE data and each customer name with a reference cell. For DE you can see the & $D$4 syntax (circled in the figure), and for the customer you can see the exact reference to cell C14. The customer that shows up in cell C14 will be used, regardless of whether it’s customer number 1000, or customer name ABC Supermarkets, or AAA supermarkets; the system does not care. This fixes the problem shown in Figure 1, and gives the Excel designer (maybe assisted by VBA code) the ultimate power in designing a super complex Excel Workbook.

Formula Mode with BEx Analyzer

The example discussed above focuses on formula mode within BusinessObjects Analysis. However, the older BEx analyzer tool actually has slightly more functionality related to formula mode. The functionality of converting to formula mode in the BEx Analyzer is nearly the same, except that access is via a right-click context menu on the table of data versus the menu bar icon option in BusinessObjects Analysis. Select a cell on the query-returned matrix of cells that you want to convert to formula mode (in this case, DE), and right-click to open the context menu. Then select the Convert to Formula option (Figure 8). Formula mode is activated just as it was with the button version in BusinessObjects Analysis.

Figure 8

Select the Convert to Formula option from the context menu in BEx Analyzer

Another difference between BusinessObjects Analysis and BEx Analyzer is that, with BEx Analyzer, the older BExGetData syntax is used (versus the SAPGetData syntax with BusinessObjects Analysis). Looking at the differences of each, you notice that the older BExGetData syntax starts with pure cell references, which are a little harder to read. You have to know how the query was designed to know what the formula is referring to. This is shown in the example in Figure 9; in this case, it was just open orders (cell D1) by sold-to-party (1033 cell A9). Other than this difference, the method of using the BExGetData syntax versus the SAPGetData syntax is basically the same.

Figure 9

BEx Analyzer syntax versus BExGetData syntax

Formula Mode with Both BEx Analyzer and BusinessObjects Analysis

What has not been mentioned before is that the convert-to-formula scenario is the same whether using BEx Analyzer or BusinessObjects Analysis. Using either tool, after formula mode is applied, the normal analysis functions’ context menu disappears and is replaced with normal Excel options. However, in the convert-to-formula method for both tools, the BEx Analyzer does offer a design mode option (which BusinessObjects Analysis does not). In this option you can lay out buttons, tables of data, and charts, which makes for a fancier looking Excel Workbook.

When designing a Workbook using BEx Analyzer (out of scope for this article), there is an analysis grid design element that you can use to customize the display of tables of data, as well as customizing buttons and check boxes. For the analysis grid there is a special option you can set in the design process called Use Formulas (Figure 10).

Figure 10

Select the Use Formulas check box when designing your table

When the analysis grid is rendered in the BEx Analyzer workbook, as shown in Figure 11, the data is again displayed as a formula for each cell, without having to use the convert-to-formula option. The slight difference between using the convert to formula mode (instead of formula mode) is that the user can still use the context menu navigation features that come with the BEx Analyzer (such as Select Filter Value…) as well as the features from native Excel.

Figure 11

The Use formulas option keeps the BEx Analyzer context menu navigation options

As a final note, if the Use Formulas option is chosen for the analysis grid, the user gets an instant error message when the Workbook is opened. This is because the formula for each cell requires a link to the server to execute. To solve this problem (and to see the data as it was last retrieved), click the pencil icon next to the Dataprovider field (on the back screen of Figure 12). In the Change Data Provider pop-up window that opens, select the Provide Results Offline check box and click the OK button.

Figure 12

Select the Provide Results Offline check box

Ned Falk

Ned Falk is a senior education consultant at SAP. In prior positions, he implemented many ERP solutions, including SAP R/3. While at SAP, he initially focused on logistics. Now he focuses on SAP HANA, SAP BW (formerly SAP NetWeaver BW), SAP CRM, and the integration of SAP BW and SAP BusinessObjects tools. You can meet him in person when he teaches SAP HANA, SAP BW, or SAP CRM classes from the Atlanta SAP office, or in a virtual training class over the web. If you need an SAP education plan for SAP HANA, SAP BW, BusinessObjects, or SAP CRM, you may contact Ned via email.

You may contact the author at ned.falk@sap.com.

If you have comments about this article or publication, or would like to submit an article idea, please contact the editor.