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.

The figure shows Analysis function in the Excel formula editor.

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

The figure shows an example of the SAPGetData function and its parameters.

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.

Converting the crosstab to formulas offers several advantages, including:

• Create a different table layout than the standard Rows/Columns layout.
• Insert spaces to make the display easier to read.
• Copy parts of the table or individual cells to another position in the workbook in order to compare particular values.
• Re-use cells.
• 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.
• 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.

Watch the video to see how to use the Analysis formula function.

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, select 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.

Let's summarize:

• SAP Formula Functions: Enhance workbooks with Analysis-specific formulas, accessible via the Insert Formula icon in Excel.
• Convert to Formula: Transform crosstabs into Excel formulas for increased layout control, but may impact performance.