Using SAP formula functions

You can use Analysis-specific formulas to enhance the appearance of your workbook. To see a list of the SAP Analysis-specific formula functions you can use, choose the Insert Formula (fx) icon in Microsoft Excel and then select Analysis from the category drop-down. To use the function, double-click it as you would any other Excel formula function.

Similar to some other Excel functions, Analysis functions have different parameters to define.

In the hands-on exercise, you will explore just a few of these functions, to get familiar with the formula interface and some of the options you have for enhancing your analyses and workbooks.

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.

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 Source → Select 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.

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