Defining New Queries (SAP Library - Enterprise Reporting, Query, and Analysis)

Defining New Queries

Prerequisites

The Business Information Warehouse must contain at least one InfoProvider.

You have called up the BEx Query Designer from:

· The menu path Start/Programs/Business Explorer/Query Designer

In the Query Designer symbol toolbar, choose This graphic is explained in the accompanying text Open Query.

· BEx Web Application Designer:

In the Web Application Designer, choose Tools ® Query Designer and chose New Query This graphic is explained in the accompanying text from the Query Designer toolbar.

· BEx Analyzer:

In the BEx toolbar, choose Open ® Queries and then, in the BEx Open dialog, choose New This graphic is explained in the accompanying text .

· Crystal Reports:

Call the Query Designer and choose New Query This graphic is explained in the accompanying text from the Query Designer toolbar.

Note

You get to the InfoProvider selection in the Query Designer automatically in the table display. The procedure for defining a query is aligned with the procedure for defining a query in OLAP reporting. Therefore, note the differences for the table display when you are defining a query for use in formatted reporting with Crystal Reports.

Process Flow

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 printed in bold are optional. You do not necessarily have to use these functions.

Procedure

You get to the selection screen 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.

Note

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

2. The objects available in the InfoProvider you selected are displayed as a directory tree in the left part of the Query Designer.

The right screen area contains empty directories for the filter selection, 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 Functions of 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, using drag-and-drop, place a single value in the filter of the Query Designer. Alternatively, using drag-and-drop, drag the characteristic into the Filter area of the Query Designer. Using the right mouse button 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 of the query definition 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. Now determine which characteristics, key figures or structures you want to use for navigating through the query data. Using Drag&Drop, move the corresponding directories of the characteristics of the InfoProvider tree to the directories Rows, Columns or Free Characteristics in the right-hand screen area of the Query Designer or drag the structure directories into the rows or 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. From the Key Figures directory of the InfoProvider tree drag a key figure into a directory of the Query Designer using Drag&Drop (either Row or Column). The key figure is stored in the new directory 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 (right 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 (right mouse click). A restricted key figure would be “Sales revenue in 1^st quarter”, for example.

By placing the characteristics, key figures or structures either in the rows or columns directory of the Query Designer, 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.

Note

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.

5. When you have created the query, you can check the query definition by choosing Check Query This graphic is explained in the accompanying text from the toolbar.

6. Select the symbol for Query Properties This graphic is explained in the accompanying text 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 also Query Properties.

7. Save the query. Enter a Technical Name and a Description for naming 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 Business Information Warehouse. The technical name can be up to 30 characters long and must begin with a letter.

Result

The query has been saved and you can now:

· Add the query to a workbook in the BEx Analyzer: Choose Quit and Use Query

· Display the query in the Web browser in a standard view: Choose Display Query on the Web.

· Use the query in the Web Application Designer as a data provider for Web items.

· Use the query in the table display in Crystal Reports as a data source for formatted reports

You can analyze the query data in the Business Explorer.