Defining New Queries 
Use
You need queries as data providers for data analysis in the BEx Analyzer or in BEx Web Applications. Queries as data providers are also used to create reports. To do this, in the BEx Query Designer, you define new queries based on InfoProviders.
Prerequisites
At least one InfoProvider must be available in the BI system.
You have called the BEx Query Designer using one of the following options:
● Standalone, by choosing Start ® Programs ® Business Explorer ® Query Designer.
● BEx Web Application Designer:
In the menu bar of the Web Application Designer, you have chosen Tools ® BEx Query Designer.
● 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.
Finally, you chose 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
The steps that are not in bold are optional. You do not have to use these functions.
Defining Queries
The selection screen is displayed for all InfoProviders for which you can define a new query.
...
1. From your history, or from all available InfoProviders under InfoAreas, select the InfoProvider that you want to base your query on.
If you want to show the technical name of the InfoProvider, choose the symbol Technical Name On/Off.
2. The available objects for the InfoProvider you selected are displayed as a directory tree in the InfoProvider screen area.
The Filter screen area contains empty directories for the filter selection; the screen area Rows/Columns contains empty directions 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 still empty.
For more information about the process of creating a query, see Working with the BEx Query Designer in the section 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. From the directory tree of the InfoProvider, select the characteristics or the key figure on which the query is to be based.
○ Characteristics
Expand the required dimension directory and first select one or more characteristics that you wish 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 and drop, drag:
■ one or more single values in the Characteristic Restrictions area of the filter.
one or more characteristics in the Characteristic Restrictions area of the filter. Using the secondary mouse button on a characteristic, choose Restrict in the context menu. Then select a characteristic value interval or a hierarchy node (see also: Restricting Characteristics).
○ Key Figure
Decide whether all of the query data is to refer to the numerical values of a key figure or whether the query should be based on several key figures. If the query refers to the numerical values of one key figure, select the corresponding key figure for the InfoProvider tree and drag it to the Filter area using drag and drop. You can also use a restricted or calculated key figure (see also: Defining Restricted Key Figures and Defining Calculated Key Figures).
If required, define default values for the filter by dragging the required filter values into the Default Values area of the filter using drag and drop. For more information, see Filters.
4. Determine which characteristics, key figures, or structures you want to use to navigate through the query data. Using drag and drop, drag the corresponding directories of the characteristics of the InfoProvider tree to the directories Rows, Columns, or Free Characteristics in the Rows/Columns screen area, or drag the structure directories into the rows or columns.
You can use any number of characteristics in the query; however, the number of characteristics in the drilldown is restricted. In the Free Characteristics area, an unlimited number of characteristics is permitted, but the rows and columns may include 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. Using drag and drop, drag a key figure from the Key Figures directory in the InfoProvider tree into one of the directories, Rows or Columns. The Query Designer automatically generates a new structure with the proposed description Key Figures, which includes the selected key figure.
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 choose New Calculated Key Figure in the context menu (using the secondary mouse button).
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 (using the secondary mouse button). A restricted key figure would be “Sales revenue in first quarter”, for example.
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, you can see a preview of the results area of the query in the start view that you selected.
You can change the arrangement of the key figures and characteristics in the rows and columns using drag and drop until the preview shows the results area you want.
5. When you have created the query, you can check the query definition by choosing Query ® Check in the menu bar.
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. See also Query Properties.
6. Save the query. Enter a Technical Name and a Description for the query. The Technical Name must uniquely identify the query; this means the name may appear only once across all InfoProviders of the BI System. The technical name can contain a maximum of 30 characters and must begin with a letter.
Result
The query has been saved and can now:
● Be used in the BEx Analyzer as a data provider for design items
● Be used in the Web Application Designer as a data provider for Web items.
● Be displayed in the BEx Web Analyzer: Choose Query ® Execute.
You can analyze the query data in the Business Explorer.
See also:
Properties of Query Components
Functions of the Menu Bar in the Query Designer
Authorizations for the Query Definition