Using SAP formula functions

Objectives
After completing this lesson, you will be able to:

After completing this lesson, you will be able to:

  • Create and use SAP functions in the Excel formula editor
  • Use Analysis-specific VBA

Convert to Formula

You can convert all the cells of a crosstab into formulas in one step. This conversion deletes the crosstab object and defines every row in the table as a Microsoft Excel formula. The result values called from the server with the formula are still displayed in the table. The formula of the selected cell is displayed in the formula bar. In formula mode, you can edit the analysis table using Microsoft Excel formatting and formula functions and make further calculations using the existing data.

Note
Having all formulas for the crosstab instead of a single crosstab object will probably decrease the performance of the entire workbook since every cell represents a call to the data source system. However, you will have much more control over the layout and calculation of your data. You need to decide if the trade-off is worthwhile to you and your organization.

A few advantages to converting the crosstab to all formulas include:

  • You can create a different table layout than the standard Rows/Columns layout.
  • You can insert spaces to make the display easier to read.
  • You can copy parts of the table or individual cells to another position in the workbook in order to compare particular values.
  • You can re-use cells.
  • You can overwrite a members with another one, or add one in order to call data that you need from the Business Intelligence (BI) server. If member "3.2024" is used to read the sales revenue for March 2024, for example, you can replace the 3 with a 4, thus using member "4.2024" to obtain the sales revenue for April 2024.
  • You can calculate additional subtotals.

You must, however, keep in mind these restrictions:

  • Navigation using drag and drop or the Design Panel is not possible.
  • The context menu is not available.
  • Workbook performance may be slower since more calls to the data source must be made.

To convert a crosstab to formulas, choose Convert to Formula in the Tools area of the Analysis Design ribbon.

Use SAP Formula Functions

In this exercise you will use SAP formula functions to add information and functionality directly to the Analysis worksheet without using Analysis ribbon options.

Task 1: Create a Control Panel to Display Information about the Query

Task 2: Display Filter Information Using SAP Functions

Formula Optimized Mode

If you know you need to create a formula-based table for your data analysis, you can open data sources in a formula-optimized mode. The prerequisite for using data sources in the formula-optimized mode is using SAP BW/4HANA SP9 or SAP BW 7.50 SP13 as BW system.

To open a data source in formula-optimized mode, celect a cell in the sheet and choose Insert Data SourceSelect Data Source for Defining Formulas . . . and choose a data source as you normally would.

On the Components tab in the Design Panel, you can use the property Use Data Source Formula-Optimized to switch between the formula-optimized mode and the analysis mode. In the recently used list, different icons are used for data sources opened for analysis and data source opened in formula-optimized mode. You can open the data source directly from the list in the desired mode.

VBA Macros

As we have seen, the Analysis Add-in contains a set of functions that you can use to build formulas. You can also use these functions in VBA (Visual Basic for Applications) macros. Analysis contains Application Programming Interface (API) methods that can also be used in VBA macros. The Visual Basic Editor can be used to write and edit a macro that is attached to a Microsoft Office Excel macro enabled workbook. The macros can be connected to UI elements that are available on the Developer tab in the menu.

Note
Microsoft Office documentation provides information about creating and using VBA macros. For more information regarding the specific Analysis API methods, please see the Analysis Plug-in User Guide

Log in to track your progress & complete quizzes