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 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, using the menu path Start ® Programs ® Business Explorer ® Query Designer:
· BEx Web Application Designer:
In the menu bar of the Web Application Designer, you have chosen Tools ® 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 ® Query Designer.
Then you have chosen Query ® Save Query 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 in the InfoProvider you selected are displayed as a directory tree in the InfoProvider screen area.
The Filter screen area includes empty directories for the filter selection; the screen area Rows/Columns includes 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, meaning that all of the InfoProvider data is aggregated with the filter selection. Select the characteristics or the key figure on which the query is based from the directory tree of the InfoProvider.
¡ 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 drag it using Drag&Drop
§ 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, select Restrict from the context menu. Now 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 of the InfoProvider tree and drag it to the Filter area using Drag&Drop. You can also use a restricted or calculated key figure (see also: Defining Restricted Key Figures and Defining Calculated Key Figures ).
4. If required, define default values for the filter by dragging the required filter values into the Default Values area of the filter using Drag&Drop. For more information, see Filter.
5. Now determine which characteristics, key figures or structures you want to use to navigate through the query data. Using Drag&Drop, drag the corresponding directories of the characteristics of the InfoProvider tree to the directories rows, columns or free characteristics and into 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 total of 50 - <Number of Structures> characteristics.
If the query does not contain a structure, in total, 50 characteristics are permitted in the rows and columns. When you use one structure, 49 are allowed and with two structures, a total of 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&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 that 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” that is calculated using “Sales revenue” divided by “Quantity”. Highlight the directory Key Figures in the InfoProvider tree and choose New Calculated Key Figure from the context menu (secondary mouse click).
You can restrict key figures to characteristic values, characteristic value intervals or hierarchy nodes by highlighting the key figure and choosing Edit from the context menu (secondary mouse click). A restricted key figure would be “Sales revenue in 1st 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&Drop until the preview shows the results area you want.
6. When you have created the query, you can check the query definition by choosing Query ® Check Query from the menu bar.
7. From the menu toolbar, choose Query ® Select the symbol for Query Properties from the toolbar if you want to change the Description, settings for Result Position, Display Options and Number Display, or Key Date for the query. See 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 is allowed to appear only once across all InfoProviders of the BI System. The technical name can be up to 30 characters long 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 Display Query on the Web
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