Business Information Warehouse for SAP (Prima Techs SAP Book Series)

Before defining a query, outline what you want to inquire about. Which InfoCube do you need to write a query against? What data elements do you need, and how do you want to filter, navigate (drill down), or slice and dice data? Select only data elements that you need by limiting your dimensions or applying filters to restrict the query view.

Queries in SAP BW consist of four major sections: Lines, Columns, Free characteristics, and Filter, as shown in Figure 11-4.

Figure 11-4: Defining a New Query in the BEX Analyzer.

1. Lines. This is the area where you place characteristics that will appear as rows of the query result.

2. Columns. This is the area where you place the selected key figures. If you want to define new calculated key figures local to your query, this is where you define the calculated field.

3. Free characteristics. In this area you place characteristics to filter the query result or for drill-down analysis. These filters are used to restrict data for display but without displaying restricted elements in the row.

   For example, if you want to list products sold and revenues by all distribution channels except Direct Sales, you set the distribution channel filter to Direct Sales.

4. Filter. You use the Filter area to place characteristics for restricting query data; you do not use this area for navigation. For example, you use this area if you want to list only the filter on the Distribution channel but never want to navigate or drill down using the Distribution channel.

Figure 11-4 shows the steps to define a new query. First click the Open icon in the BEX Analyzer menu bar. This opens the dialog box to define the type of query and the InfoCube data source. Click the Queries button, select an InfoCube, and click the New button. This displays the query definition window, which shows all dimensions and key figures defined in the selected InfoCube.

To show Material on Lines and Amount in Columns, simply drag Material characteristics from the InfoCube tree to area 1, the Lines area. Similarly, you drag and drop other elements, as shown in Figure 11-5. Notice that as you drag lines and key figures in respective query sections, the bottom-right section, section 6, displays the data presentation layout.

Figure 11-5: Selecting Data Elements of a BEX Query.

Click the Save icon to save the current view of the query.

Click the Check icon to execute a query. This launches the query in Microsoft Excel and displays the results, as shown in Figure 11-6.

Figure 11-6: The Default Query Results Based on the Global Query Setting. You Can Change the Look and Feel of the Query by Changing its Properties.

Customizing the Query Data Display Layout

The BEX Analyzer provides several options to customize the data presentation in your report.

For example, you can display descriptions of all characteristics in the query or only a few.

As shown in Figure 11-6, the Material values seem to be duplicated. The reason is that by default, the query will display a characteristic's key value and its description. Here, the Material master key and descriptions are the same in the database. Suppose you want to display only Material descriptions on each line. This can be done for all characteristics in the query or by an individual characteristic. Select the characteristics for which you want to set the display attributes-in this example, Material, as shown in Figure 11-7. Right-click Material. Navigate the menu as shown in Figure 11-7. Note that by default Key and description was checked. This was the reason that you saw duplicate material values in the query results. Instead, select Description. The query will restructure itself and display Material descriptions only.

Figure 11-7: Selecting Characteristic Display Options.