Concept of a Report
The Business Information Warehouse provides information for analyzing all of the factors that affect a company's business transactions. The data basis of the Business Information Warehouse is divided into self-contained business data areas (InfoCubes).An InfoCube consists of InfoObjects. These InfoObjects are subdivided into characteristics and key figures. Think of an InfoCube as an n-dimensional data cube with a key figure axis: each characteristic corresponds to one dimension of the data cube, the number of values determining the length along that dimension. The quantity of key figures in the InfoCube forms a separate dimension.By means of the reporting functionality of the Business Information Warehouse, the dataset of the InfoCube can be analyzed according to various characteristics and key figures.When you create a query, you must make sure that all the necessary characteristics and key figures exist in the InfoCube.
From the InfoCube to the Worksheet View
A query allows you to analyze all of the data in an InfoCube. When you define a query, you determine how the data will be evaluated and which characteristics and key figures should be examined more closely.If you picture the InfoCube as a multi-dimensional cube, you can imagine that, by selecting characteristics and key figures, a sub-cube (QueryCube) is cut out of the InfoCube. The more detailed the QueryCube is defined, the smaller the sub-cube. This sub-cube does not contain any data initially. When the analysis is carried out, the QueryCube always contains the data from the current and last drilldown.A query always refers to one InfoCube, while any number of queries can be defined for one InfoCube.
Creating a Query
Procedure for creating a new query:
Choose the New icon in the toolbar.
Choose the pushbutton Queries. Choose pushbutton New.
This brings you to the selection screen containing all of the InfoCubes for which you can define a new query. Select the InfoCube on whose data you want the query to be based, by selecting it with the mouse. You can see the technical name of the InfoCube by choosing Technical Name (wrench icon). To create the query, choose New.
Choosing Characteristics and Key Figures
The objects available for the InfoCube you have selected are shown as a tree structure in the left- hand part of the query definition screen. These objects include the key figures and characteristics of the dimensions.The right-hand part contains empty directories for filter selections, rows, columns, and the free characteristics of the query. The bottom right-hand part of the screen shows a preview of the query result area. This area is empty at first.By clicking the plus or minus symbol for the directories, you can expand or compress the directory structure. By expanding the key figure node in the InfoCube tree, for example, you can display a list of all the key figures for the InfoCube. You can drag the characteristics and key figures for the InfoCube into the directories for the query definition (filter, rows, columns, free characteristics). When you have finished defining your query, choose Save Query. Choose Enter (checkmark icon) to start working with the query.
Changing a Query
Choose Change Query to modify the query.There are two options for changing the query:
Choose the mode Change Query (local view) to easily move or restrict the objects that are already in the query definition. The worksheet is refreshed each time you execute the query, but the changes are not saved in the query definition.
Choose Change Query (global definition) to return to the definition screen, where you can use the same functions you could use when you first defined the query. Before executing the changed query, save the changes in the query definition. This applies the changes to the original query definition.
There are two ways of saving the changed query:
You can save the changed query under its previous name. To do this, choose Save Query in the toolbar.You can save the changed query under a new name. To do this, choose Save Query as... in the toolbar. Enter a new technical name and a new description. You can change this description at any time.
Filtering Data in the Query Definition
When you define a query, you can drag individual characteristics to the columns or rows.Free characteristics are not displayed when you insert the query in the workbook. They can, however, be integrated by means of navigation steps.Characteristics in the filter do not appear in the drilldown nor as free characteristics in the analysis. These characteristics are only used to filter the data from the cube. You can restrict (filter) the values of a characteristic in the rows, columns, free characteristics, and in the filter.
Selection Menu for Restricting Characteristics
There are two ways of restricting characteristics:
To get to the selection menu, select a characte ristic from the InfoCube (in the tree structure in the left part of the screen) and double -click on the values icon. It gives you a selection of single values or values from a value area, which you can restrict by hierarchies and their nodes.Use a right mouse-click on a characteristic in the query definition and choose Restrict, to get to the same selection menu with the relevant selection options.Within the selection menu, you can select values from the list by; dragging the selected characteristics from the left-hand window to the right-hand window. tranferring the selected characteristics with the arrow icons. You can selected more than one characteristic. Finally, confirm your selection with OK.
Filtering Data in the Worksheet
When you execute the query, you can also filter data in the worksheet. You can do this by using the characteristic values in the filter area and the results area.Use a right mouse-click on the characteristic you want, to apply the filter by characteristic value in the filter cell area.
Find Filter Value opens the familiar selection menu, which helps you filter the characteristic. It gives you a selection of single values or values from a value area, which you can restrict by hierarchies and their nodes. Choose OK to restrict your query result to the selected values. Choose Remove Filter Value to remove the filter value of the corresponding characteristic.
You also have the option of only displaying one column in your query result.. Double -click on the header of the column to do this. This hides all the other columns. The column title appears in the filter area in the Key Figure row. Choose Remove Filter Value to undo the restriction.
Navigating through Analysis
A query shows views of the data in the InfoCube on which the query is based. You can create the various views of the query data (so-called navigational states) by using the navigation functions from the worksheet. These views are then presented in the result area of the query. Changing between views is referred to as navigating. You can analyze the InfoCube data by navigating in the Business Explorer. The initial data view after the query has been inserted in the workbook is specified when the query is defined.
You can drilldown either across or across on a characteristic in the filter cells. To do this, choose the function Drilldown Down or Across. Undo these functions by choosing Remove Drilldown. You can delete the current drilldown of the characteristic by choosing Remove Drilldown or by double-clicking on the characteristic.You can integrate characteristics in the drilldown with Drilldown Across and Drilldown Down. These characteristics are always inserted at the end of the list. The Drilldown Across navigation function always displays all the characteristic values for each key figure. To display all of the key figures for a characteristic value, choose Change Query.
To swap two characteristics, choose Exchange… with…. In this way, you can swap characteristics between rows and columns in the result area or a characteristic in the drilldown with a free characteristic.Choose Keep as filter value to record a selected characteristic value as a filter value. This characteristic is then removed from the drilldown and set as the filter for the corresponding characteristic value.Choose Filter and Drilldown according to to set a characteristic at a certain value (filter) and, at the same time, drilldown by a different characteristic on the same axis (row or column).
Adding Text Elements
You can display various text elements associated with a query (e.g. creator, last changed by, InfoCube, key date, and so on) in a freely definable cell area. These text elements are also referred to as the "header information" of the query.To integrate query text elements in the worksheet, choose Layout - Display Text Elements. All of the available text elements are then displayed between the filter rows and the result area. You can then delete, copy, or move these text elements using the standard EXCEL functions.In addition, you can display the characteristics you restricted earlier in the query definition, or show variables as text elements.