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.
At least one InfoProvider is available in the BW system.
You have called BEx Query Designer using one of the following options:
You have chosen Tools → BEx Query Designer in the Web Application Designer menu bar.
You are in an empty cell and have chosen Tools → Define New Query in the analysis toolbar.
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.
Overview
You define queries by:
The steps that are not in bold are optional. You do not have to use these functions.
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.
If you want to show the technical name of the InfoProvider, choose the Technical Name On/Off icon.
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
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:
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).
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.
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.
The query has been saved and can now be:
You can analyze the query data in Business Explorer.
More Information:
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