Welcome to the tutorial about using SAP Filters and Queries. This tutorial is part of our free SAP BPC training. After studying this tutorial, you will learn about creating new filters and design of input-ready queries in SAP BPC. While talking about filters we will explain how to create different kinds of variables. We will also provide tips about testing and troubleshooting queries in SAP BEX.
In the previous tutorial, we covered the use of SAP Real-Time InfoCubes and Aggregation Levels. In this tutorial, we will look at the SAP Filters and Queries in more details.
Let’s look at the planning requirements of our user again:
- Sales Quantities are planned on the lowest level, which is material
- We need an input schedule to capture growth percentages
- We will need a planning function to average out sales and apply those percentages
- Prices must be planned on a higher level, Material Group
- The planning sequence that calculates revenue, must execute on save.
Create SAP Filter
In the previous tutorial, we started with the process for quantity planning on aggregation level REV_01. The filter controls the selection of data for the Input Ready Query and can contain the following:
- A characteristic value, for example, it is possible to ‘hardcode’ the selection for a characteristic to a specific value, such as ‘K4’ for Fiscal Variant;
- A variable, for example, a selection range for Fiscal Year/Period. Note that any type of variable available in SAP BEX can be used here and Replacement Path Variables are very useful;
- Leave a characteristic in the filter empty.
Now let’s build the filter in the transaction RSPLAN. In the transaction RSPLAN, select the Filters button.
Enter the name and description of the filter, as well as the Aggregation level on which the filter will be built, for example:
To track our objects easily, we will use the convention of naming our filters sequentially, starting with F01, after the Aggregation Level.
After you press Enter button, you will be presented with a list of all the characteristics in the Aggregation level, as well as all the available Navigational Attributes.
Let’s maintain the various characteristics based on the following rules:
|Processing Type||Variable Represents||Entry Required|
|0FISCPER||Fiscal year / period||ZIP_FISCPER||User Entry/Default Value||Range||Mandatory Variable Entry|
|0FISCVARNT||Fiscal year variant||ZRP_FISCVARNT_SALES0RG||Replacement Path|
|0MATERIAL||Material||ZIM_MATERIAL||User Entry/Default Value||Selection||Optional Variable Entry|
|0SALESORG||Sales Organization||ZP_SALESORG||User Entry/Default Value||Single Value||Mandatory Variable Entry|
|0UNIT||Unit of Measure||ST’|
|0VERSION||Version||ZP_VERSIO||User Entry/Default Value||Single Value||Mandatory Variable Entry|
Let’s look at the process of adding a variable for the InfoObject 0FISCPER. To add a variable for a characteristic in the filter, click on the Selection button for the characteristic as shown on the screenshot below.
You will be presented with the Input Help box. Under the view drop down field, select Variables.
Next, click on the Create Variable button.
Enter details of the variables as follows:
|Variable Name||Technical name of the variable||ZIP_FISCPER|
|Long Description||Text description of the variable||Fiscal Year/Period (Interval, Mandatory)|
|InfoObject||Select the InfoObject. It should default to the InfoObject 0FISCPER||Fiscal year/period|
|Processing Type.||How the system handles the processing of the variable||Range|
|Value Represents||Single Value, Interval or several single values||Single Value|
|Entry Required||Optional or Mandatory||Mandatory, Initial Value not Allowed|
|Variable ready for input||Determines if variable is filled by user or a user exit||Select|
|Add personalization from Variable||Should we add a personalization from another variable||Empty|
|Default value(s)||Should this variable have a default value||Empty|
After you saved the variable, click on the Add button to add the variable to the filter as shown below.
Rest of the Variables
Let’s look at some of the values in the Filter. First, I want to discuss the Fiscal Year Variant. We can either hardcode it, or get the value from the master data. In this case, since we restricted the Sales Organization to a single, mandatory value, we can grab the value from the value selected in the variable, through a replacement path variable. The variable’s configuration is as follows on the screenshot below.
Note that we are hardcoding the unit of measure. This is not ideal, since not all the materials in our selection will use the same unit of measure (UoM). For example, the InfoObject 0MATERIAL may have the following entries with its base unit of measure:
Ideally, we want to use the UoM from the material master. But since the variable selection in this case is a range, we can’t use the same method as we did for the Fiscal Year Variant. But let’s continue with this to illustrate a principle and then later we will create the coding to correct the situation.
The completed filter should look as follows:
We can now save the filter and move to the query.
Create an Input Ready Query
Creating an input ready query for SAP BPC is basically the same as a normal BW BEX query. In the Query Designer, we select the Aggregation level as the InfoProvider.
Once selected, select the filter and drag it into the Characteristic Restrictions column:
It is generally, the best practice is to place as many of the restrictions into the filter, rather as local restrictions in the query, but the requirements of the customer will dictate it.
The following general principles apply in building an input ready-query, and it’s generally something that inexperienced consultants battle with:
- Each Key Figure that is planned, must address a unique intersection of data. Characteristics that contain multiple values or ranges cannot be in the ‘Free Characteristics’ section.
- The selection of ‘Master Data’ versus ‘Posted Values’ has a significant effect on output of the query. You will see either no data, or too much data, or any combination in between. Use the ‘Master Data’ setting with care, especially when you have several characteristics that are restricted on ranges.
Query Rows and Columns
Now let’s use these principles to build the rest of the query:
Since 0MATERIAL’s restriction contains more than one material, we have to place it in rows or columns. We will place it in rows.
Under Extended Settings, we will mark the Access Type as ‘Master Data’. This will return empty, imputable fields for all the materials in the selection, even if there is no values in the cube.
Drag 0UNIT to rows as well and set the Access Type to Master Data.
As 0FISCPER is also a range, do the same with 0FISCPER, but add it to the columns.
The rest of the Characteristics can be added to Free Characteristics, as they are restricted to single values.
Since we are planning quantities, drag the Key Figure 0QUANTITY to the Columns. In the Key Figure’s properties, select the Planning tab and set to ‘Input-Ready (Relevant for Locking).
The completed query should look like this:
Make the Query Input Ready
The last setting is to make the query Input Ready by default. To do this, select the properties of the query and under the Planning Tab, check the ‘Start Query in Change Mode’.
Test the Query
We can quickly test the query in the transaction RSRT. Select your query (I called it REV_01_Q0001) and enter some variables for the query, for example:
The query should be displayed with empty columns that are input ready for all the materials in the selection. You should be able to enter values in the fields.
If the query returns the following:
It means that you have not set all the characteristics to Access Type ‘Master Data’.
If you cannot enter any values in the Key Figure, the cause may be one of the following:
- You have not made the Key Figure 0QUANTITY input ready or
- You have not made the query ready for planning or
- The cube may be in ‘Load-Mode’ or
- The characteristics in the rows/columns do not create a unique intersection on the Key Figure. For example, you may have incorrectly defined version as a range and selected more than one value.
In the next tutorial, we will look at how the data is represented in the cube. We will also build the remaining Aggregation Levels and Queries to complete the Input Schedules.
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 BPC tutorials.