Creating Simple Queries


After completing this lesson, you will be able to:

  • Create a simple query

Defining Queries in SAP BW Modeling Tool in SAP HANA Studio

To analyze a dataset in SAP BW/4HANA, you define queries on top of InfoProviders. By selecting and combining InfoObjects such as characteristics and key figures, or reusable query elements such as restricted and calculated key figures in a query, you define how you want to evaluate the data that comes from the selected InfoProvider.


Although it is possible to build BW Queries directly on top of all types of InfoProviders such as DataStore Object (advanced) and Open ODS Views, it is best practice to build a CompositeProvider on top of, for example, a DataStore Object (advanced) as part provider and then build the query on top of the CompositeProvider.

This is because the InfoProvider on which the query is built cannot be replaced later with another, but it is possible to replace the part providers of the CompositeProvider. So better create the fixed assignment between the query and the CompositeProvider.

A CompositeProvider is a very powerful modeling object. The strength of a CompositeProvider is in preparing data sets for consumption by queries. By comparison, the strength of a DataStore Object (advanced) is in its handling of complex data loading scenarios.

The key parts of a query definition are navigation and filters:

  • For navigation, you choose a default layout assignment for each characteristic and key figure that has been selected from the InfoProvider. This means that you assign each characteristic and key figure to either the rows or columns section of the layout. Characteristics can also be left unassigned which means they belong to the free characteristic section. Those characteristics can moved to the rows or column by the business user when the query is executed and the results appear. The assignment to the rows and columns section for each characteristic and key figure determines the initial view for the query. The business user is able to change these assignments from the query results. Remember, they are only defaults when defined in the query.

  • The filter selection apply to the entire query. When defining the filter, you select values from one or more characteristics to restrict the data. You can also define one key figure as a filter. If you do this, the query can only have characteristics in the rows and columns and not key figures. All the InfoProvider data is aggregated using the filter selection for the query.

You can parameterize queries by using variables for characteristic values, hierarchies, hierarchy nodes, formulas, and texts, or by defining new variables where necessary.

To make the selection of InfoObjects more precise, you can do the following:

  • Restrict characteristics to characteristic values or hierarchy nodes

  • Define selections

  • Define formulas

  • Define exception cells and help cells

  • Use local structures

  • Define conditions

  • Define exceptions

  • Define calculated and restricted key figures as reusable query components

The queries are built in the SAP BW Query Designer, which is a part of the SAP BW Modeling Tools in SAP HANA Studio.

The SAP BW Modeling Tools provide an Eclipse-based integrated modeling environment for the management and maintenance of SAP BW/4HANA metadata objects.

The main objective of the tool is to support SAP BW/4HANA metadata modelers in today’s increasingly complex BI environments by offering flexible, efficient modeling tools. These tools integrate with ABAP Development Tools as well as with SAP HANA modeling tools and the consumption of SAP HANA elements in SAP BW/4HANA metadata objects, such as Open ODS views or CompositeProviders. The SAP BW Modeling Tools have powerful UI capabilities.

Sheet Definition

The Sheet Definition tab is where you choose where the place the characteristics and key figures in the query layout.

The Sheet Definition tab has the following sections:


Place query objects, such as key figures or characteristics, in this section so that they appear in report columns when you execute the query.


Place query objects, such as key figures or characteristics, in this section so that they appear in report rows when you execute the query.


Place characteristics that you would like to be available for a user, in this section. Free characteristics do not appear in the initial view of a query result set. The user can bring these characteristics into the rows or columns using the navigation controls of their chosen reporting tool.


When you select a query object, you can view and edit its properties on the right of the Sheet Definition tab. The properties influence the behavior and display features of each objects.


A filter is an object that describes a multidimensional extract of data from a data set.

You can use filters in reporting, analysis and planning, to restrict data to a specific business area, specific product groups, or specific time periods. By restricting the data set, you can ensure that users only have access to the data that is relevant to them.

On the Filter tab, you define the filters to apply restrictions to the characteristics. This type of filter applies to the entire query. There are other types of filters that restrict only parts of the query. You will learn about those later.

All the InfoProvider data is aggregated using the filter selection of the query.

You can define two different types of filters:

  • Filter: Fixed Values

    The selection scope cannot be enlarged at runtime, it only can be narrowed through navigation by the user.

    To enable navigation for a characteristic that is included in the Filter: Fixed Values section, it must additionally be included in the Rows, Columns, or Free Characteristics section. Otherwise, the filter is applied but further navigation on that characteristic is not possible.

  • Filter: Default Values

    The selection scope can be enlarged, narrowed, or deleted at runtime using navigation.

    All characteristics contained in the Filter: Default Values section are enabled for navigation because they are automatically contained in the Rows, Columns, or Free Characteristics sections, and vice versa.

    Example: You want to analyze all customers in a query. In the query's start state however, you initially only want to see a particular customer.

In a query, you can combine fixed value filters with default value filters.

How to Create a Simple Query

Launch the following demo to learn how to create a simple query.

Reusable Filter

If you create a complex filter with several characteristics for a query, and if you think you will use this filter frequently, you can save the filter as a reusable filter.

A reusable filter belongs to an InfoProvider; therefore, it can be reused in all queries that are based on the InfoProvider.

A reusable filter can consist of fixed filter values, default filter values, and any combinations.

Changes made to the reusable filter affect all queries where it is used.

To make changes in a specific query only, convert the reusable filter into a query-level filter by choosing dereference. Changes made later to query-level filter do not affect the reusable filter or queries that you made with the reusable filter.

General Properties

The General tab is used to define settings that apply to the entire result set of the query. The General tab is divided into several screen areas.

General Settings

In the General screen area of the General tab, you can view and configure the following settings:

Technical NameThis is the technical name of the query and must be unique in the SAP BW/4HANA system. A technical name cannot be changed.
DescriptionA short description of the query. You can change the description at any time. You can also include variables so that some words in the description are dynamic and determined at runtime - for example, dates.
InfoProviderDisplays the technical name of the InfoProvider that the query is based on. Choose the link to open the maintenance screen for the InfoProvider so you can learn more about it.
Key DateEvery query has a key date. For time-dependent master data such as attributes, texts, or hierarchies, the key date determines the date for which time validity the data is selected. The default value for the key date is the date when the query is executed: <today>. You can also enter a fixed date or refer to a variable for a dynamic date.
Use a consistent naming convention for technical names to identify queries easily. A well thought-out naming convention is helpful for authorizations planning.

Display Settings

In the Output Settings, Result Location, Zero Suppression, and Universal Display Hierarchy screen areas of the General tab, you can view and configure the following settings:

Display OptionDescription
Adjust Formatting after RefreshingWhen a query is refreshed, result cells are formatted by default so that they are emphasized. Deselect this option to improve query performance or to use your own format templates.
Suppress Repeated Key ValuesWhen a query is executed, only the first key or description displays by default for each characteristic. Additional identical keys or descriptions are suppressed.
Show Scaling for MeasuresInclude scaling factors in a row or column header, for example *1000 EUR.
Sign FormatChoose how to present negative values.
Zero FormatChoose how to present zero values in the result.
Result LocationYou can choose where to position results in rows and columns. By default, row results are presented below the rows, and column results are presented at the right-hand side.
Zero SuppressionYou can hide the complete row or column, if only the result of the row or column produce a result of zero. Alternatively, you can hide the complete row or column only when all the single values in the row or column are zero.
Universal Display HierarchyTo achieve a better overview, the characteristics in the rows or in the columns can be arranged in a hierarchical display. In the Expand to Level field enter the characteristic up to which the hierarchical display should be expanded initially.

Remote Access

External reporting tools use queries as their data source. You can use this setting to determine whether the query can be accessed by external tools and by which method. Choose carefully so you do not expose the query to tools that should not consume it.

By OLE DB for OLAPIf you want to release this query as the data source for external reporting tools that communicate using interface OLE DB for OLAP, select By OLE DB for OLAP.
By ODataThe integration of SAP BW∕4HANA with SAP Gateway allows you to provide analytic queries of SAP BW∕4HANA as OData queries for mobile scenarios. Queries with OData access are specialized for REST access. To generate the query as an OData query, select By OData.
External SAP HANA ViewIf the query is based on an InfoProvider that supports SAP HANA views, you can use this setting to make the system create an SAP HANA view for the current query.

Variables Order

By changing the sequence of variables, you ensure that they are presented to the user in a logical order.


In the Planning screen area, you can make settings that are relevant if you want to use the query in planning applications or to create short texts.

Start Query in Input Mode: You can define that a query should be started in change mode if it contains at least one input-ready query component.


Under Extended in the General tab, you can make, for example, data integrity settings. These are relevant if your back-end system supports this.

Request StatusIf the InfoProvider is suitable for this, you can specify how recent the displayed InfoProvider data is. In one InfoProvider, data can be read with different request statuses.
Cold Store (Near Line) AccessIf the query is based on an InfoProvider that data has already been stored for in a near-line storage, you can stipulate that the stored data is read and displayed when the query is run.
Document LinksFor metadata, master data, and InfoProvider data for a query, you can display links to documents that you have created for these objects. If documents exist for these objects, the document icon appears next to these objects in the report. You can choose this icon to navigate to where the document is displayed.

How to Extend Simple Query by Filters and Hierarchical Display

Launch the following demo to learn how to extend a simple query by using filters and hierarchical display.

How to Implement Reusable Filters

Launch the following demo to learn how to implement reusable filters.

Query Object Management

For queries and reusable components, SAP BW/4HANA provides tools to perform useful activities such as search, where-used, and to copy and delete them.


Not all, but many of these tools are used during demonstrations and videos in this Learning Journey.

Here you can see the meaning of numerous icons:


The following options are available for data preview:

  • Reporting Preview
  • Analysis for Office
  • BW Cockpit
  • UIBB

To preview the queries, Analysis for Office is mostly used in this course.

The data preview options are configured via the customizing transaction SPRO: SAP BW/4HANA Customizing Implementation GuideSAP BW/4HANAAnalysisSet Query Execution for Query Design Tools.

Many functions can be accessed from the context menu, as shown in the following figure:

In the InfoProvider view, if there are many Calculated Key Figures and Restricted Key Figures, you can create further folders for hierarchical grouping. This feature is not available for Filters and Structures.

Log in to track your progress & complete quizzes