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:

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.

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:

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

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

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:

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

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:

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

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

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

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:

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

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

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.

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

Now, select the variable as per the following screen:

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

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

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

The following screen appears:

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.

The following screen appears:

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

5. Save the Query

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

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

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:

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

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

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.