How to Use Formula Mode for Complex Analysis

  • by Ned Falk, Senior Education Consultant, SAP
  • September 26, 2014
Learn how to use an often-overlooked feature of the Excel-based reporting toolbox: formula mode. Learn why formula mode makes SAP BW’s BEx Analyzer and BusinessObjects Analysis the preferred tools for heavy analysis users.
Learning Objectives

Reading this article, you will learn:

  • How to activate the formula mode feature in both the SAP BW BEx Analyzer and BusinessObjects Analysis tools
  • How to use the formula mode feature to integrate BW Query and local Excel data into powerful mashups
  • Why this feature propels Excel as one of the reporting tools in the suite of reporting tools that should be implemented at a company initially
Key Concept

Formula mode, a feature in BW’s BEx Analyzer and BusinessObjects Analysis for Excel, takes an SAP BW data stream and populates Excel Workbooks with individual cells, each with a formula that fetches (or writes) data from  SAP BW. It allows the use of complex logic that ties local data on a worksheet with data from a BW Query. This special feature positions these tools for use in complex analysis tasks better than any other BusinessObjects or BEx tool set.

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.

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.

See more by this author


10/9/2014 3:33:17 PM
Ned Falk

I'm sorry, I don't know why that's happening. Maybe this link will help:
Good luck.
10/9/2014 2:20:38 PM
Frank Sorrentino

Realized I did not leave an email address if needed.
10/9/2014 1:42:57 PM
Frank Sorrentino

In general I follow and have been using the AO formulas as depicted in this article. The concept does infer a very powerful use case and alternative to the traditional Excel formulas (Sumif, Vlookup, etc....). However, are you aware of any technical reasons why renaming the file (which kicks off a recalculation of the Workbook) causes the AO formulas to no longer know their relationship to the source (DS). To clarify, this does not happen if you are connected to the backend (BW) but the real use case is sharing a file with a non AO Users just trying to open the file in Excel (after the name change). Any insight would be appreciated. Using version

Please log in to post a comment.

To learn more about subscription access to premium content, click here.