SAP BW Formula Variable

Advertisement

SAP BW Formula VariableAs 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:

Query Definition
Query Definition

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:

Query Definition - Filters
Query Definition – Filters

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:

Query Definition – Variable Properties
Query Definition – 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:

Creating a New Formula (1)
Creating a New Formula (1)

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

Creating a New Formula (2)
Creating a New Formula (2)

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

Creating a New Formula (3)
Creating a New Formula (3)

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

Creating a New Formula (4)
Creating a New Formula (4)

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.

Advertisement

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’.

Creating a New SAP BW Formula Variable (1)
Creating a New SAP BW Formula Variable (1)

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

Creating a New SAP BW Formula Variable (2)
Creating a New SAP BW Formula Variable (2)

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

Creating a New SAP BW Formula Variable (3)
Creating a New SAP BW Formula Variable (3)

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

Creating a New SAP BW Formula Variable (4)
Creating a New SAP BW Formula Variable (4)

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

SAP BW Formula Variable Properties (1)
SAP BW Formula Variable Properties (1)

Enter technical name and description as per the following screen:

SAP BW Formula Variable Properties (2)
SAP BW Formula Variable Properties (2)

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:

SAP BW Formula Variable Properties (3)
SAP BW Formula Variable Properties (3)

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

SAP BW Formula Variable Properties (4)
SAP BW Formula Variable Properties (4)

Choose ‘Replace Variable With’ as Variable.

SAP BW Formula Variable Properties (5)
SAP BW Formula Variable Properties (5)

The following screen appears:

SAP BW Formula Variable Properties (6)
SAP BW Formula Variable Properties (6)

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:

SAP BW Formula Variable Properties (7)
SAP BW Formula Variable Properties (7)

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

SAP BW Formula Variable Properties (8)
SAP BW Formula Variable Properties (8)

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

Saving the Formula Variable (1)
Saving the Formula Variable (1)

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

Saving the Formula Variable (2)
Saving the Formula Variable (2)

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

Formula Editor
Formula Editor

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:

Choosing the Formula Variable
Choosing the Formula Variable

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

Formula Properties
Formula Properties

‘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.

Advertisement

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.

Formula Editor
Formula Editor

Saving the Query

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

Saving the Query
Saving the Query

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

Saved Query
Saved Query

Executing the Query

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

Variable Screen
Variable Screen

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

Select Values for Variables in BEx Analyzer (1)
Select Values for Variables in BEx Analyzer (1)

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

Select Values for Variables in BEx Analyzer (2)
Select Values for Variables in BEx Analyzer (2)

The report output is given below:

Report Output
Report Output

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.

Advertisement

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.

Navigation Links

Go to next lesson: SAP BW Business Content Variables

Go to previous lesson: SAP BW Hierarchy Variable

Advertisement

Go to overview of the course: SAP BW Training

2 thoughts on “SAP BW Formula Variable”

Leave a Reply

Do you have a question and want it to be answered ASAP? Post it on our FORUM here --> SAP FORUM!

Your email address will not be published. Required fields are marked *