Show TOC

Defining New QueriesLocate this document in the navigation structure

Use

To analyze data in BEx Analyzer or in BEx Web applications, you require a query as a data provider. Queries also act as data providers when you create reports. To do this, you define new queries in BEx Query Designer based on InfoProviders.

Prerequisites

At least one InfoProvider is available in the BW system.

You have called BEx Query Designer using one of the following options:

  • Standalone, choosing Start → Programs → Business Explorer → Query Designer.
  • BEx Web Application Designer:

    You have chosen Tools → BEx Query Designer in the Web Application Designer menu bar.

  • BEx Analyzer:

    You are in an empty cell and have chosen Tools → Define New Query in the analysis toolbar.

  • BEx Report Designer:

    In the menu bar of the Report Designer, you have chosen Tools → BEx Query Designer...

You have then chosen Query → New in the Query Designer menu bar.

Procedure

Overview

You define queries by:

  • Selecting an InfoProvider for which the query is defined
  • Selecting reusable structures that already contain characteristic or key figure combinations (for example, contribution margin schema)
  • Selecting characteristics from the InfoProvider
  • Restricting selected characteristics to characteristic values, characteristic value intervals, or hierarchy nodes
  • Using variables for characteristic values, hierarchies, hierarchy nodes, formulas, and texts or defining new variables where necessary
  • Selecting key figures from the InfoProvider
  • Formulating calculated key figures
  • Restricting key figures by combining them with characteristics
  • Defining Exception Cells
  • Arranging the characteristics and key figures in rows or columns and thereby establishing an initial view for the query analysis
    Note

    The steps that are not in bold are optional. You do not have to use these functions.

    Note

    With an InfoCube as an InfoProvider, you can display and sort the key figures in a hierarchy. The key figures are then displayed in the InfoProvider screen area. More information:Creating InfoCubes.

Defining Queries

The selection screen is displayed for all InfoProviders that you can define a new query for.

  1. From your history, or from all available InfoProviders under InfoAreas, select the InfoProvider that you want to base your query on.
    Note

    If you want to show the technical name of the InfoProvider, choose the Technical Name On/Off icon.

  2. The available objects for the InfoProvider you selected are displayed as a directory tree in the InfoProvider screen area.
    Note

    You can sort the objects of the InfoProvider alphabetically (either in descending or ascending order) by clicking the corresponding arrows in the title bar. If you click three times, the system displays the data in the default order again (from top to bottom: Structures, Filter, Key Figures, and Dimensions).

    The Filter screen area contains empty directories for the filter selection; the Rows/Columnsscreen 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. This area is also empty initially.

    For more information about the process for creating a query, seeWorking with BEx Query Designer, under Proceed as follows to create a query

  3. Make the selections for the filter of the query. Note that the filter selection has a restricting effect on the entire query, which means that all the InfoProvider data is aggregated with the filter selection. In the InfoProvider directory tree, select the characteristics or the key figure that you want to base the query on.
    • Characteristics

      Expand the required dimension directory and first select one or more characteristics that you want to restrict to one or more values, a variable, a value interval, or a hierarchy node from the InfoProvider. Expand this characteristic and using drag:

      • One or more single values to the filter's Characteristic Restrictions area
      • One or more characteristics to the filter's Characteristic Restrictions area. Click the right mouse button on a characteristic to display the context menu and choose Restrict. Now select a characteristic value interval or a hierarchy node (more information:Restricting Characteristics).
    • Key figure

      Decide whether to base all the query data on the numerical values of just one key figure or to base it on multiple key figures. If you want to base the query on the numerical values of just one key figure, select the relevant key figure for the InfoProvider tree and drag it to the Filter area. You can also use a restricted or calculated key figure (more information:Defining Restricted Key Figures andDefining Calculated Key Figures).

  4. If required, define default values for the filter by dragging the required filter values into the filter's Default Values area. More information:Filters.
  5. Specify which characteristics, key figures, or structures you want to be available for navigating through the query data. Drag the corresponding directories of the characteristics in the InfoProvider tree to the directories Rows, Columns, orFree Characteristics in the Rows/Columns screen area, or drag the structure directories into the rows or columns.
    Note

    If a characteristic has a compound characteristic, the compound characteristic is automatically added during the drag and drop action. This also applies to copying and pasting.

    The compound characteristic is always added to the characteristic restrictions of the filter. For example, characteristic Country is compounded to characteristic Region. If you drag the Region characteristic to the Free Characteristics area, the characteristic Country appears automatically in the Characteristic Restrictions area of the filter.

    You can use any number of characteristics in the query. Note however that the number of characteristics in the drilldown is restricted. An unlimited number of characteristics is permitted in the Free Characteristics area. Note, however, that the rows and columns can contain a maximum of 50 - <number of structures> characteristics only.

    If the query does not contain a structure, a total of 50 characteristics are permitted in the rows and columns. If you use one structure, 49 characteristics are permitted, and with two structures, 48 characteristics are permitted in the rows and columns.

    If you have not selected any key figures for the filter, define one or more key figures either in the rows or columns of the query. Drag a key figure from the Key Figuresdirectory in the InfoProvider tree to either the Rowsor Columns directory. Query Designer automatically generates a new structure that contains the selected key figure; the structure has the default description Key Figures.

    You can also define a new calculated key figure and use it for the query. For example, instead of "Sales revenue", define the key figure "Price", which is calculated by dividing "Sales revenue" by "Quantity". Select the Key Figures directory in the InfoProvider tree and in the context menu, choose New Calculated Key Figure.

    You can restrict key figures to characteristic values, characteristic value intervals, or hierarchy nodes by selecting the key figure and choosing Edit in the context menu. "Sales revenue in first quarter" is an example of a restricted key figure.

    By placing the characteristics, key figures, or structures either in the rows or columns directory, you configure the first navigational state of the query (start view). In the lower right area of the screen, you can see a preview of the results area of the query. This is displayed in the start view that you selected.

    Note

    You can use 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.

  6. Once you have created the query, you can check the query definition by choosing Query → Check in the menu bar.
  7. Choose Query → Properties in the menu bar if you want to change the Description,settings for Result Position, Display Options and Number Display, or Key Date for the query. More information:Query Properties
  8. Save the query. Enter a Technical Name and a Description for the query. The Technical Name must uniquely identify the query; meaning that the name may appear only once across all InfoProviders in the BW system. The technical name can be up to 30 characters in length and must begin with a letter.
Result

The query has been saved and can now be:

  • Used in BEx Analyzer as a data provider for design items
  • Used in Web Application Designer as a data provider for Web items
  • Displayed in BEx Web Analyzer: Choose Query → Execute…

You can analyze the query data in Business Explorer.

More Information:

Creating Reusable Structures

Properties of Query Components

Menu Bar Functions in Query Designer

Authorizations for Query Definition and Information Broadcasting

More information about displaying and sorting key figures in a hierarchy:Creating InfoCubes

More information about using non-cumulative InfoCubes as InfoProviders:Performance Tips