X

SAP BW Offset Variables

SAP BW Offset Variables are used when you have key figures that have a fixed time relationship amongst one another. In the Query Definition, you can simply use the same variable multiple times to restrict the key figures and compute the difference from the input value. Variable Offset can also be used to select an interval as a restriction, where lower and upper limits refer to just the same variable.

SAP BW Offset Variables

There are various contexts in which you can use SAP BW Offset Variables. For instance, you can compare product revenue from the current fiscal year of a particular period with that of the previous fiscal year of the same period. Similarly, you can compare sales quantity from the current calendar year with that of the previous calendar year.

Let us take the following Deliveries Report. The Query definition looks as follows:

Deliveries Report: Query Definition (1)

It can be observed from the above screen that the report has Division and Ship-To Party in the rows, whereas it has Delivered quantity in the columns. The Filters section of the report can be seen in the following screen. There is just one characteristic restriction, i.e., on Division. Basically, it’s a global filter.

Deliveries Report: Query Definition (2)

Example of Requirement

To demonstrate functionality of SAP BW Offset Variables, let us consider a hypothetical business requirement for a report. The requirement is to show the Delivered quantity for the current calendar year and previous calendar year respectively (based on user chosen year during Query runtime) for Division and Ship-To Party. For instance, if a user chooses 2016 in the selection screen, the report should display the Delivered quantity values for the years 2016 and 2015 respectively.

This tutorial is divided into the following six sections:

  • Creating Delivered quantity key figures for Current Year and Previous Year
  • Creating a variable to restrict both the Current Year and Previous Year Delivered quantity key figures
  • Restricting the Current Year Delivered quantity key figure
  • Restricting the Previous Year Delivered quantity key figure
  • Saving the Query
  • Executing the Query

1. Create Delivered Quantity Key Figures for Current Year and Previous Year

Let us first create placeholders for the Current Year Delivered quantity and Previous Year Delivered quantity. To achieve the same, let us duplicate Delivered quantity key figure as follows:

Creating Delivered Quantity Key Figures

Rename the first key figure as ‘CY Delivered quantity’. The screenshot is given below:

Creating Placeholder for Current and Previous Year Delivered Quantity Key Figures (1)

Similarly, rename the second key figure as ‘PY Delivered quantity’. The screenshot given below shows the Query Definition after you complete the renaming process.

Creating Placeholder for Current and Previous Year Delivered Quantity Key Figures (2)

2. Create Variable to Restrict Current Year and Previous Year Delivered Quantity Key Figures

Now, create a variable ‘Choose Year’ so that you can use it to restrict the CY Delivered quantity and PY Delivered quantity key figures. This single variable can be used to restrict both the key figures; however, you have to use variable offset on the second key figure to get appropriate (user selected year – 1) values.

The variable properties of ‘Choose Year’ are given below:

Variable Properties (1)

The variable is created as single value and mandatory variable. The screenshot is given below:

Variable Properties (2)

3. Restrict Current Year Delivered Quantity Key Figure

Let us now begin to restrict the CY Delivered quantity key figure by right clicking on it. The screenshot is given below:

Restricting the CY Delivered Quantity Key Figure (1)

Let us now click on ‘Edit…’ button highlighted in the above screen. The following screen appears:

Restricting the CY Delivered Quantity Key Figure (2)

Now, drag and drop ‘Choose Year’ variable created on Calendar Year. The following screen appears:

Restricting the CY Delivered Quantity Key Figure

Now, click on ‘OK’ button highlighted in the below screen to proceed.

Restricting the CY Delivered Quantity Key Figure (3)

4. Restricting Previous Year Delivered Quantity Key Figure

Let us now begin to restrict the PY Delivered quantity key figure by right clicking on it. The screenshot is given below:

Restricting the PY Delivered Quantity Key Figure (1)

Let us now click on ‘Edit…’ button highlighted in the above screen. The following screen appears:

Restricting the PY Delivered Quantity Key Figure (2)

Now, drag and drop ‘Choose Year’ variable created on Calendar Year. The following screen appears:

Restricting the PY Delivered Quantity Key Figure (3)

We have to offset this variable for the PY Delivered quantity key figure to get user selected year – 1 values. To achieve it, let’s right click on the variable to get the following screen.

Setting Variable Offset (1)

Now, select ‘Restrict…’ highlighted in the above screen. The following screen appears:

Setting Variable Offset (2)

Now, select the variable as per the following screen:

Setting Variable Offset (3)

Now, click on highlighted in the above screen to offset the variable. The screenshot is given below:

Setting Variable Offset (4)

Select Variable Offset Value as ‘-1’. The screenshot is given below:

Selecting Variable Offset Value (1)

Now, click on ‘OK’ button highlighted in the below screen:

Selecting Variable Offset Value (2)

The following screen appears:

Setting Variable Offset (5)

As can be seen from the above screen, the offset of -1 got applied on the variable ‘Choose Year’. Click on ‘OK’ button highlighted in the below screen to proceed.

Setting Variable Offset (6)

The following screen appears:

Restricting the PY Delivered Quantity Key Figure (1)

Now, click on ‘OK’ button highlighted in the below screen to proceed.

Restricting the PY Delivered Quantity Key Figure (2)

5. Save the Query

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

Saving the Query

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

Saved Query

6. Execute the Query

Let us now execute the query using BEx Analyzer and see SAP BW Offset Variables that we created in action. This is how the variable screen looks like:

Variable Screen

Select values for Division and Choose Year variables as per the following screen:

Select Values for Variables in BEx Analyzer

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

Report Output

SAP BW Offset Variables play a crucial role for analyzing key figures that have fixed-time relationship. You can try couple of more scenarios to get a more thorough understanding of Variable Offset.

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 BEx Exceptions

Go to previous lesson: SAP BW Business Content Variables

Go to overview of the course: SAP BW Training