SAP Filters and Queries

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:

InfoObject	Description	Variable Name/ Default Entry	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

Creating Variables

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:

Input	Description	Value
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:

Material	Base_Uom
D304	H
D403	LE
D404	H
E100	MWH
F1000	ST
F1000-G1	ST

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.

Troubleshooting

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.