SAP BW Formula Variable

As mentioned in SAP BW Variables tutorial, SAP BW Formula Variable represents numerical values. You can use these variables in formulas for computing conditions and exceptions. Unlike a value variable, you cannot have any default value for SAP BW formula variable. When you run a report that has formula variables, the system automatically computes values for these variables. As mentioned in SAP BW Variables Tutorial, the Processing Type governs the way in which a variable gets filled with values during runtime of the query. SAP BW Formula Variables cannot have all the processing types. The following are the allowed processing types for SAP BW Formula Variables:

• User Entry/Default Value
• Replacement Path
• Customer Exit
• SAP Exit

Let’s have a look at the following Deliveries query. The Query definition looks as follows:

As can be seen from the above screen, the Query has Division and Ship-To Party in the rows, whereas it has Delivered Quantity in the columns. The Filters section look as follows:

There are Division and Delivery Date filters currently available. As can be seen from the above screen, Delivery Date is a restriction on Calendar Day. It is a Mandatory, Interval variable. The following screen shows the variable properties:

The requirement is to compute the number of days from the ‘Delivery Date’ that users enter during query run time. For instance, if a user enters Delivery Date range as 01/01/2016 to 30/01/2016, the number of days should be computed as 30 and shown in the report output for all the line items in the Query. This tutorial is divided into the following five sections:

• Creating a Formula
• Creating SAP BW Formula Variable
• Editing the Formula
• Saving the Query
• Executing the Query

Creating a Formula

Right click on ‘Key Figures’, you get the following screen:

Click on ‘New Formula” highlighted in the above screen to create a new formula. The following screen appears:

Double click on ‘Formula 1’ highlighted in the above screen. The following screen appears:

Enter description as ‘Number of Days’. The screenshot is given below:

This formula is just a dummy formula. The purpose of creating this dummy formula is for creating a new formula variable. We should have a formula to create the formula variable. After completing the creation of formula variable, we will edit this formula to have the formula variable.

Creating SAP BW Formula Variable

Let us start to create SAP BW Formula Variable. As can be seen from the following screen, there is a folder named ‘Formula Variable’.

Right click on the ‘Formula Variable’ folder to get the following screen:

Select “New Variable” highlighted in the above screen to create a new SAP BW formula variable.

Right click on “New Variable…” highlighted in the above screen:

Let’s now click on “Edit…” highlighted in the above screen.

Enter technical name and description as per the following screen:

Select Processing Type as ‘Replacement Path’ and Reference Characteristic as ‘Delivery Date’ characteristic. For SAP BW Formula Variable of Processing Type ‘Replacement Path’, values derived from either the key or the attribute key of an InfoObject can be used to fill the variable. The screenshot is given below:

Now, go to ‘Replacement Path’ tab. The following screen appears:

Choose ‘Replace Variable With’ as Variable.

The following screen appears:

Now, enter the technical name of the ‘Delivery Date’ variable (ZI_CALDAY) under replacing variable with. Make sure that you select Use Interval as ‘Difference’, as we are trying to compute the days between user entered from and to dates of the ‘Delivery Date’ interval variable. The screenshot is given below:

Now, go to ‘Currency/Unit’ tab. Here, make sure that the dimension is ‘Number’. The following screen appears:

Let us save this SAP BW formula variable by clicking on the save button highlighted in the above screen. The following screen appears:

Click on ‘OK’ button highlighted in the above screen. The following screen appears:

Let’s click on ‘OK’ button highlighted in the above screen to proceed.

Editing the Formula

Let us now drag and drop the newly created formula variable to the formula ‘Number of Days’. The screenshot is given below:

You can also observe that there is ‘Aggregation’ tab available in the formula. Go to ‘Aggregation’ tab, the following screen appears:

‘Aggregation’ function is only available for calculated key figures, formulas, and formula cells. Basically, there are two types of Aggregation. They are Standard Aggregation and Exception aggregation.

Standard Aggregation: in this type of aggregation, the data gets aggregated first to the display level, and later the formula is computed.

Exception Aggregation: in this type of aggregation, the formula gets computed before aggregation by making use of a reference characteristic. You have many options such as Standard Aggregation, Standard Deviation (STD), and Variance (VAR) for Aggregation.

Let us retain the ‘Standard Aggregation’ setting for the ‘Number of Days’ formula. Now, click on ‘OK’ button highlighted in the below screen to proceed.

Saving the Query

Let us now save the query by clicking on the save button highlighted in the below screen.

The query is saved successfully. The screenshot is given below:

Executing the Query

Let us now execute the query using BEx Analyzer. This is how the variable screen looks like:

Select values for Division and Delivery Date variables as per the following screen:

Now, execute the query using the ‘OK’ button highlighted in the below screen.

The report output is given below:

With the help of SAP BW Formula Variables, you can create various new types of reports. Apart from the example illustrated as part of this tutorial, there are numerous other possibilities of using formula variables in reports. Try at least couple of other ways of using SAP BW Formula Variables to get a perspective on how effective these variables are in SAP BW.

—

Did you like this tutorial? Have any questions or comments? We would love to hear your feedback in the comments section below. It’d be a big help for us, and hopefully it’s something we can address for you in improvement of our free SAP BW tutorials.