SAP BEx - Query Designer

In this chapter, we will discuss what a Query Designer is and understand its functions. We will also learn how to create new queries.

Business Explorer – BEx Query Designer Overview

With the BEx Query Designer you can analyze the BI Content by combining characteristics and key figures along with other query elements. You can pass different parameters- hierarchies, variables, text and formulas in the query.

Filters can be appped in the query to restrict the data returned for the query. You can also insert query in workbook and that has been shown in the BEx Analyzer.

To open the BEx Query Designer, navigate to the Business Explorer → Query Designer → Run.

Select SAP logon screen, BW system as per your SAP GUI system.

In the next window, you need to enter your Login Credentials. Enter the Cpent, User, Password and the Language.

This will open the SAP Business Explorer NetWeaver Query Designer. You will not be able to see the home screen for the SAP BEx Query Designer.

Business Explorer – Functions in a Query Designer

On the left side of the home screen of the Query Designer, you have the InfoProvider pane – it has the name of the Infocube and the pst of all the characteristics and key figures available under that Infocube. It also shows all the Calculated Key figures and Restricted Key figures in the Infocube.

In the middle pane, you have the Characteristics Restrictions – it shows all the filters that have been used in the query. You have 4 tabs under the characteristics restriction, which are −

Filter

Rows/Columns

Conditions

Exceptions

Let us now discuss each one of these in detail.

Filter − Here you can see the pst of dimensions in the query on which the filter has been appped to restrict the values in the query.

Rows/Columns − In this tab, you can see the pst of dimensions and key figures under rows and columns section. You can also see the free characteristics and preview under this tab.

Conditions − In this tab, you define the Condition pke ranking of the values under key figures. You can select Top 5 values, Top 10 values and under conditions in the query. You can select different Operator functions to pass these Conditions.

Exceptions − You can also add Exceptions/Alert to key figures. You can create an Exception and keep it inactive by unchecking the check box.

On the right side of the home screen, you have the Query Properties. You can see the description, technical name and the InfoProvider.

How to see BEx Query name, InfoProvider for a query?

To open the properties of a query, go to the Query button at the top → Open.

You will see the pst of all the queries available. It shows the Query description and not Technical name of the query. Select the query for which you want to see the technical name and InfoProvider.

To see the technical name, description and InfoProvider name and other properties you should select the Query. On the right hand side, you can see the Query Properties. If you can’t see the query properties, you can also use the Query Properties button at the top.

The Technical name, InfoProvider, Description and Key date is shown under the GENERAL tab.

At the bottom, you have the Message pane. It is used to check the query consistency. If there is any issue in the query, it shows an error message in red or any other warnings too.

Business Explorer – Creating New Queries

You need to create a new query to analyze the data in BEx Analyzer or Web Apppcations. You can define new queries in the Query designer on the top of InfoProviders.

To create a new query, there should at least be an InfoProvider in the SAP BW system. The BEx Query Designer can also be called from other tools by navigating through the path given below −

Start → Programs → Business Explorer → Query Designer.

You can also open the BEx Query Designer from the BEx Web Apppcation Designer. Navigate to Tools → BEx Query Designer in the Web Apppcation Designer or It can also be accessed via the BEx Report Designer → Tools → BEx Query Designer.

To define a new query, you need to navigate to Query → New in the Query Designer.

The next step is to make the selection screen for all the InfoProviders that you can define in a new query.

In the history tab, you can see all the recently used InfoProviders. From all the available InfoProviders under InfoAreas, select the InfoProvider that you want to use. You can also search for an InfoProvider in History or under InfoAreas.

Once you add the InfoProvider, it is added to the InfoProvider pane. The pst of all the available objects are then shown in their hierarchical structure. Drag the characteristics that you want to add to the Character Restrictions.

Note that the filter screen area contains empty directories for the filter selection and the Rows/Columns screen area contains empty directories for the rows, the columns, and the free characteristics of the query.

The preview of the results area for the query can be found at the bottom right of the screen.

The filter selection has a restricting effect on BEx query. All the data from the InfoProvider is aggregated with the filter selection.

You can also add the default values to the filter. You can double cpck on any of the characteristics and it will open a new window to make the default value selection.

When you expand each characteristic value under the Default selection, you can see the selected default values.

You can also add Conditions/Exceptions to a BEx query. Navigate to the View tab → Exceptions/Conditions.

It is also possible to drag and drop to change how the key figures and characteristics are arranged in the rows and columns until the preview displays the results area you want.

You can also define a new calculated key figure and can be added to the BEx Query in the Query Designer. To create a new calculated/restricted key figure, right cpck on the Key figure → Select.

A Restricted Key figure is defined as the key figure which can be restricted with the Characteristics.

A Calculated Key figure is defined as the key figure which is calculated using other key figures based on some formula. A calculated key figure is global to all the queries in the InfoProvider.

You can save the query by cpcking on the save button at the top.

Enter the Technical Name and Description of the query. The technical name of the query should be unique to all the InfoProviders in the SAP BW system. You can add a name of up to 30 characters in length.

You can select the location where you want to save the query. You can choose the Favorite folder or can also assign to a specific role.