SAP Filters and Queries

Advertisement

SAP Filters and QueriesWelcome 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.

SAP BPC Architecture
SAP BPC Architecture

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.

Transaction RSPLAN – Initial Screen
Transaction RSPLAN – Initial Screen

Enter the name and description of the filter, as well as the Aggregation level on which the filter will be built, for example:

Create SAP Filter
Create SAP Filter

To track our objects easily, we will use the convention of naming our filters sequentially, starting with F01, after the Aggregation Level.

Advertisement

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.

An Empty SAP Filter
An Empty SAP Filter

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.

Selection Button in SAP Filter
Selection Button in SAP Filter

You will be presented with the Input Help box. Under the view drop down field, select Variables.

Enter the Variables View
Enter the Variables View

Next, click on the Create Variable button.

Create Variable Button
Create Variable Button
Create Variables: Specify Properties (1)
Create Variables: Specify Properties (1)

Enter details of the variables as follows:

Create Variables: Specify Properties (2)
Create Variables: Specify Properties (2)
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.

Add Variable to SAP Filter
Add Variable to SAP Filter

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.

Replacement Variable for Fiscal Year Variant
Replacement Variable for Fiscal Year Variant

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.

Advertisement

The completed filter should look as follows:

Completed SAP Filter
Completed SAP Filter

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.

Create Query on Aggregation Level
Create Query on Aggregation Level

Once selected, select the filter and drag it into the Characteristic Restrictions column:

Filter in Characteristic Restrictions
Filter in Characteristic Restrictions

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.

Advertisement

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.

Characteristic Properties (Material)
Characteristic Properties (Material)

Drag 0UNIT to rows as well and set the Access Type to Master Data.

Advertisement

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

Key Figure Properties (Quantity)
Key Figure Properties (Quantity)

The completed query should look like this:

SAP Query Rows and Columns
SAP Query Rows and Columns

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:

Transaction RSRT – Variable Selections
Transaction RSRT – Variable Selections

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.

Input Ready Query
Input Ready Query

Troubleshooting

If the query returns the following:

No Application Data Displayed in Query
No Application Data Displayed in Query

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.

Navigation Links

Go to next lesson:

Go to previous lesson: SAP Real-Time InfoCubes and Aggregation Levels

Go to overview of the course: SAP BPC Training

3 thoughts on “SAP Filters and Queries”

  1. Hi Lambertus,

    Very interesting work on BPC and yes I am also eagerly waiting for the next lesson to complete the query.

    Best Regards,
    Ashish

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 *