Enhance Our First BEx Query

This article is about how to enhance your first SAP BEx query by adding more data fields resulting in more meaningful data.

Adding more fields to your query

One way to enhance and improve your query is to include as many data objects as you wish from the InfoProviders section in the BEx Query Designer. As you already know, in the BEx Query Designer you can drag and drop the data fields from the InfoProvider section. It lets you choose objects from the structure, key figure and dimensions folders. You can include key figures in column section, or include characteristics selected from dimensions to the rows section. Similarly you can also include characteristics in the Free Characteristics section or anywhere in the Query Designer you want.

Characteristics and Key Figures

The dimensions folder, which includes the characteristics, is selected more frequently than the other two folders. The Standard Key Figures include numeric data objects to include such as number of employees, number of full time workers, ages of employees etc. The Calculated Key Figures include fields that are made up of formulas on standard key figures, such as performing calculation on Standard Key Figures.

Status Messages and Properties

As you include more data fields in different section of the Query Designer you will notice the changes in the Properties section that displays the different details of the object that you add to the Query Designer. At the very bottom of the Query Designer the Messages section checks the objects after every update and tells you of any errors if your object contains. If no errors exist then it shows a OK message.

[frame linking=”lightbox” align=”center” type=”paper-stack”]Query Enhance[/frame]

 

Understanding the query representation in the BEx Analyzer

Once you run your query in the BEx Analyzer after adding enough key figures, and characteristics you must understand how your query is represented and what it means in the BEx Analyzer.

[frame linking=”lightbox” align=”center” type=”paper-stack”]BEx Analyzer Query Table [/frame]

Now lets go through some important elements of your queries that are used in the BEx Analyzer . The Filter option in the BEx Analyzer shows the characteristics and the key figures that you added to your query. Through the Filter button in the BEx Analyzer the user can customize the view of the query that is running in the Analyzer. You can filter out different organizational units and can replace the characteristics one with another. For Example If you want to replace the Gender column with the Employee Subgroup, you can right click on the Gender Column and select Exchange Gender With option, followed by selecting the Employer Subgroup field.

[frame linking=”lightbox” align=”center” type=”paper-stack”]BEx Analyzer Query Filter [/frame]

You can remove the data fields that you do not want to be presented in your query table too, by right clicking on the Filter menu and the Select Value Display dialog box appears, then click on the field that you want to remove from the table.

[frame linking=”lightbox” align=”center” type=”paper-stack”]BEX Analyzer Filter Display [/frame]

Complete A to Z Run Through Of The SAP BEx Analyzer Toolbar

As you are no doubt aware by looking at some of my ads on the website, I have produced various SAP related training course; one of which is my new BEx Analyzer training course.

Below I am sharing one of the videos from the course titled: Learn SAP BEx Analyzer The video covers a complete run through of the SAP BEx Analyzer Toolbar that you find inside MS Excel.

If you like what you see then take a look at the full BEx course here.

Enjoy!

[frame type="projector-screen"]

[/frame]

Creating Our First SAP BW BEx Query

[frame link=”http://www.saptraininghq.com/bexpagecourse” linking=”new-window” align=”right” type=”paper-stack”]SAPBWBExAnalyzerCourseSaleDiscount[/frame]If you have not created a query in the SAP BEx Query Designer before, then this article is for you; here you will learn step by step how to create your first query in your BW system that you can run in the BEx Analyzer.

[button link=”http://www.saptraininghq.com/bexpagecourse” linking=”new-window” size=”small” type=”simple” title=”SAP BEx Analyzer Course”]Full Course – Click Here[/button]

How to create a query?

To create a new query, click on the create New Query button from either the Query menu or from the toolbar buttons. A dialog box will then appear where you have to select an InfoProvider to base your query on. The InfoAreas button will display the InfoProvider hierarchy for navigating and finding out the information that your BW system contains. The InfoAreas contains different types of infoProviders such as InfoCubes, DSOs etc. Select the one that you want. In this example I will choose the Headcount and Personnel Actions (0PA_C01) infocube.

Tip: Turn on the Technical Names setting to help differentiate between InfoProviders that have the same description.

[frame linking=”lightbox” align=”center” type=”paper-stack”]New Query Dialog Box[/frame]

InfoProvider inside the Query Designer

Once the query designer loads the InfoProvider structure into the BEx query designer  as shown below, you will see it contains three folders. These are structures, key figures and the dimensions folders. The dimension folder is the name that is given to high level folders in the InfoProvider. It contains the characteristics of an InfoProvider. The contents of the Dimensions and key figures structures are what we use to build our reports.

[frame linking=”lightbox” align=”center” type=”paper-stack”]New Query InfoProvider Laoded[/frame]

 

To add a characteristic to your report drag the characteristic from the InfoProvider and drop it to the rows/columns section. The preview section shows you how the query will look in the BEx Analyzer once it is saved and the properties section shows the description and the technical name of the characteristic.

[frame linking=”lightbox” align=”center” type=”paper-stack”]New Query Rows Columns[/frame]

Saving the Query

To save the query click on the Save As button on the toolbar in the Query Designer application. As you are about to save your first query a Save Query As  dialog box appears and requires you to fill in the Description and the Technical Name fields to save the query.

Tip: You can use spaces in the query description field but not in the query technical name field.

When you hit the save button your report will be saved.

Running the saved query in the BEx Analyzer

You can run the query that you saved in the BEx Query Designer to see what it looks like in the BEx Analyzer. Just click the execute button.

[frame linking=”lightbox” align=”center” type=”paper-stack”]New Query Running in BEx Analyzer[/frame]

Making changes to your query

Once you have run your query for the first time you will most likely want to edit the design to add more fields or adjust some specific field and query properties.

You have the option of using the BEx Query Designer application again, alternatively call the query designer from the BEx Analyzer toolbar.

[frame linking=”lightbox” align=”center” type=”paper-stack”]New Query Edit in BEx Analyzer [/frame]

Once you click the edit query option, it opens up the Query Designer and automatically loads to query that you had open in the BEx Analyzer. One draw back of using this way of editing you queries is you can not switch to Analyzer unless you close the Query Designer first. A bit of a pain!

[frame linking=”lightbox” align=”center” type=”paper-stack”]New Query Edit in BEx Analyzer
[/frame]

SAP BW BEx Query Designer Menus And Toolbar

[frame link=”http://www.saptraininghq.com/bexpagecourse” linking=”new-window” align=”right” type=”paper-stack”]SAPBWBExAnalyzerCourseSaleDiscount[/frame]This article covers the BEx Query Designer Menus and Toolbar and their functions.

[button link=”http://www.saptraininghq.com/bexpagecourse” linking=”new-window” size=”small” type=”simple” title=”SAP BEx Analyzer Course”]Full Course – Click Here[/button]
The BEx Query Designer contains the following Menu items: Query ~ Edit ~ View ~ Tools ~ Help

BEx Query Designer Menus

The Query Menu

The BEx Query Designer’s Query menu is like a File menu in MS Excel or Word. You can create New queries, Open queries, Check queries, Save queries and so on.

The Publish option in the lets you publish your queries to a specific role that has been set up for your users You also have the option to use the BEx broadcaster to distribute your reports either by email or other Broadcast mechanism that you have set up for publishing your reports.

[frame linking=”lightbox” align=”center” type=”paper-stack”]BEx Query Designer Query Menu
[/frame]

 

The Edit Menu

The Edit menu is very simple. You have the option to Cut, Copy, Paste, or Remove your queries using the Edit menu options. You can also change or display your queries by the Display/Change option.

[frame linking=”lightbox” align=”center” type=”paper-stack”]Query Designer Edit Menu[/frame]

The View Menu

The View menu in the BEx Query Designer allows you to customize the look of your Query Designer’s interface. You can select the Standard View or SAP BW 3.5 View through the Predefined option. Technical Names opens up a sub menu to let you choose to show the technical names of characteristics and key figures in our queries. For example the characteristic “employee” has the same description as its characteristic name but by choosing the see the technical name “PERNR”, we can differentiate the field easily.

The View menu can also be used as way of turning window elements of the screen on or off.

[frame linking=”lightbox” align=”center” type=”paper-stack”]Query Designer View Menu [/frame]

 

The Tools Menu

The Tools menu is a small menu, it allows you save every object that you have added within your query by clicking Save All option.

[frame linking=”lightbox” align=”center” type=”paper-stack”]Query Designer Tools Menu
[/frame]

 

The Help Menu

Through the help menu option you can learn everything that you may need to know about the BEx Query Designer. You can access SAP help articles there and online support as well.

BEx Query Designer Toolbar

The BEx Query Designer’s toolbar is an alternative way of selecting the various options from the Menus. It includes every function that the Menu has except the Cell Definitions option. You can perform the same menu functions with the toolbar options such as creating a new query, opening a query, saving the query, and all the other option that are described above.

[frame linking=”lightbox” align=”center” type=”paper-stack”]Query Designer Toolbar
[/frame]

 

Starting The SAP BW BEx Query Designer And Screen Layout

[frame link=”http://www.saptraininghq.com/bexpagecourse” linking=”new-window” align=”left” type=”paper-stack”]SAPBWBExAnalyzerCourseSaleDiscount[/frame]You probably know how to start the BEx Query Designer, but having a short explanation of it and its layout, can help you better understand the BEx Query Designer’s functional areas and the other details that you need to know before working on the BEx Query Designer.
[button link=”http://www.saptraininghq.com/bexpagecourse” linking=”new-window” size=”small” type=”simple” title=”SAP BEx Analyzer Course”]Full Course – Click Here[/button]

How to start the BEx Query Designer?

To start the BEx Query Designer, click on the start menu button on your computer screen, click All Programs and go to Business Explorer menu and select Query Designer. A log in box appears for logging into your BW system; type your username and password then press the okay button to log in. That’s it! The BEx Query Designer opens up.

To get quick access to the BEx Query Designer you can add it as a shortcut on your desktop screen or task bar menu or pin it to start menu.

[frame linking=”lightbox” align=”center” type=”paper-stack”]Starting BEx Query Designer [/frame]

[frame linking=”lightbox” align=”center” type=”paper-stack”]SAP Logon box[/frame]

BEx Query Designer Screen Layout

When you log on to the BEx Query Designer,  you are presented with an interface like this:

[frame linking=”lightbox” align=”center” type=”paper-stack”]Query Designer Screen Layout[/frame]

You can see there are seven sections in the screenshot above, which make up the components of the BEx Query Designer screen layout. These seven sections are as follows:

  1. InfoProvider Section
  2. Characteristic Restrictions (Filter Section)
  3. Default Values (Filter Section)
  4. Columns (Rows/Column Section)
  5. Rows (Rows/Column Section)
  6. Properties/Tasks Section
  7. Messages Section

Here is the brief overview of these sections:

InfoProvider Section

The InfoProvider area of the screen is the first section in the BEx Query Designer. It lists all the available fields that can be added to our query, such as characteristics, key figures, attributes and calculated figures and so on.

Filter Section

Characteristic Restrictions

The Characteristic Restrictions section is part of the filter section in the BEx Query Designer interface. It is used whenever you want to restrict your query to a certain number of records based on specific characteristic values. All you do is drag and drop your characteristics into this are and then add a filter.

Default Values

The Default Values section lists the characteristics and their associated default values that you can assign.

Rows/Columns Section

The Rows/Column section includes the following areas:

Free Characteristics

This area is a container in which you add fields ‘addition’ fields that a user can choose to add into their query at run-time.

Columns

This area contains fields selected from the InfoProvider section that you want to add to your query. Fields added here will display as Columns in your query output.

Rows

This area is similar to the Columns area in that you add fields selected from the InfoProvider section that you want to add to your query, but it differs in the way the data is presented when the query is executed. Fields added to this section will display as Columns in your query output.

Preview

This area just shows a representation how your query will look when it runs in the BEx Analyzer.

[frame linking=”lightbox” align=”center” type=”paper-stack”]Query Designer Screen Layout [/frame]

 

Properties Section

Properties

Every component that you add to your query, contains properties. In the properties section you can make specific changes such as descriptions, display settings, characteristic aggregation, show/hide result rows etc.

Tasks

Tasks is a tab in the properties section. This areas shows changes (depending on the query objects) that are highlighted in the Filter or the Rows/Columns section. It highlights the errors in your query and suggest different actions to fix those errors.

Messages Section

This final area of the screen is used to provide feedback to us while we are creating our queries. It checks our query on the fly and tells us if we have any errors in our queries as well as displaying warning messages. It can show messages about any error that your query contains.

There is also a ‘Where Used List‘ tab which is a very handy referencing tool. When you select an object in your query the Where Used List can show you where that object is being used or reference in you query.