SAP System Setup Training Course
Reveals How To Setup A Free SAP Developer System.
Learn SAP & Practice For Certification. Developer Tips Included:

4 Easy Steps To Creating Reports Using The SAP BEx Query Designer

In this article, we’re going to switch attention away from the BEx Analyzer and focus on the BEx Query Designer. First, I will give an overview of where the BEx Query Designer fits into the SAP BW landscape, taking a look at the System Architecture to understand how reports are created and where they can be used.

Overview of the SAP BW Tools Landscape

clip_image002

First of all, let’s go into a bit of detail about InfoProviders. These are the objects, similar to a database, that hold data that reports are built upon. Instead of being called tables, they are given the specific name because an InfoProvider can be an InfoCube (a multi-dimensional set of tables), a Data Store Object (referred to as a DSO), or Info Objects within a BW System.

InfoProviders can also be classed as Logical InfoProviders, which instead of holding data, they hold the mapping rules to where the data resides. For example, a Multi-Provider combines the mapping rules to multiple InfoCubes or DSO objects in one place which then can be used in the Query Designer as though it were just one InfoProvider.

There are also Virtual InfoProviders, which map data connection rules to allow connections to remote data sources. Think of a normal SAP ERP System, instead of bringing the data into a BW System, Mapping Rules can be setup via a Virtual InfoProvider to go straight through to the source system where the data sits and gets updated in real-time, enabling direct reporting from that system.

Once we have the InfoProvider that the Query is going to be built upon, this is where the BEx Query Designer steps in, where the Query is built using the InfoProviders data. Once the Query is defined using drag-and-drop techniques, setting up calculations and so on, the Query can then be used by multiple applications.

This book focuses on the BEx Analyzer, as it is the most used application, but these Queries can also be used in the BEx Web Application Designer (where applications are built using Queries), BEx Web Analyzer (a web-based version of the Excel powered Analyzer) and the BEx Report Designer (for creating printed reports). With these tools that analyse the BEx Queries, we have the choice of either Web (including the SAP Portal) or Excel platforms to carry out reporting activates, and once either of these is set up, users are free to use them to their heart’s content.

clip_image004

One important note: the BEx Report Designer is no longer actively developed by SAP. After purchasing Business Objects a few years ago, Business Objects Tools have become the preferred tool for web reporting. However, the Web Application Designer and Web Analyzer will continue to be supported for quite some time.

Another thing to keep in mind is that although Business Objects is being implemented in many companies, the majority of Business Objects reports that use SAP BW data actually use Queries as a data source, which means the BEx Query Designer and BEx Analyzer continue to be key fundamental tools within SAP’s Business Intelligence offering, and will be supported for a long time to come.

Starting the BEx Query Designer

To start the Query Designer, click on the Start button, select All Programs, and then navigate to Business Explorer. Click on Query Designer option.

You will be presented with a login window. Log into your BW system and the Query Designer will open.

Screen Layout

clip_image006

When the program opens, you are presented with a screen consisting of seven sections.

clip_image007

The first section on the left is the InfoProvider section. Every Query must be built using an InfoProvider, and this is the section in which they appear, listing all the available fields that can be added to a Query. This includes all the Characteristics, Key Figures, Attributes and everything else such as calculated key figures.

clip_image008

The next section is Filters, which covers two window areas for Characteristic Restrictions and Default Values.

Whenever a Query has to be restricted to a certain number of records (for instance, if it was required to restrict to four Organisational Units), the Characteristic Restrictions window is where fields would be dragged and the restrictions applied.

The Default Values window is where default values for Characteristics are entered, which are the first set of filters to be applied to a Query. The user will be given the opportunity to override or remove these filters as and when they wish.

The Rows/Columns section can be accessed by clicking the tab underneath the Filter windows, and replaces them with three further sections.

clip_image010

This section is where a Query is built. For instance, to include five fields into a Query they would be dragged from the InfoProvider window into either the Free Characteristics, Rows or Columns sections depending on where the user wants them. The Preview section shows a representation of how the Query would look in the BEx Analyzer. The fields included will then appear by default.

clip_image011 clip_image013

The next section is Properties. Just about every component that can be added into a Query will contain properties, and this is where you can view and edit the properties as you need. The properties include things like field description, display settings, how a Characteristic may be aggregated or totalled and so on.

This section can be switched to the Tasks section (again, by clicking the tab at the bottom). This is an area that changes depending on the Query object that is currently highlighted, displaying different tasks or actions for the different types of Query elements. Any errors in a Query will appear here and tasks will appear to enable you to fix them.

clip_image014

The next section is Messages. When a Query is saved, the BW System checks the Query for completeness and ensures that there are no errors. Any messages relating to this will be shown here.

Toolbars & Menus

The Menu bar

clip_image016

The Query menu acts in a similar way to the File menu in Microsoft Word: New, Open and Save act as you would expect.

Publish is for when a Query needs to be published to a specific Role that has been set up for users to access Queries, or by using the BEx Broadcaster to publish reports via e-mail or another broadcast mechanism that has been setup.

clip_image018

The Edit menu also contains typical menu commands, such as Cut, Copy and Paste. The Display/Change option toggles between the Display mode, where no changes can be made, and Change mode where everything is editable.

clip_image020

Next, there is the View menu. Inside this menu are options to display different screen areas as well as the toolbars you can see. Predefined lets you change between the Standard view and the old edition’s view called SAP BW v3.5.

Technical Names brings up the option to display names as Text, Key or a combination of both. Just like data in the BEx Analyzer, Technical Names have both a Key and a descriptive name (e.g.: ‘Employee’ and the Key, ‘PERNR’).

There are also menu items for the sections of the window detailed in the last section; clicking on these does the same thing as selecting a tab. Exceptions and Conditions are used for advanced reporting and will be detailed later in the book, and at the bottom of the menu there is a Refresh function.

The Tools menu is rather small and just allows the saving of the Object edited within the Query, and Help contains an option for Application Help and the About screen.

The Toolbar

BEx Toolbar Explained

As you can see,  many icons on the toolbar are repeated in the menu, very much like in the BEx Analyzer. People have their own preference; I prefer to use the controls on the actual page.

Cells, referring to Cell Definitions will covered later in the book.

clip_image024

One final part of the Query Designer interface to note is the bottom-right corner of the screen, where there is the Connection icon. Just like the BEx Analyzer, it shows you if the software is connected to the BW System. If the connection is broken, the icon will show this too.

 

Creating Our First BEx Query

Now the interface has been introduced, this section will focus on getting down to business and creating our first BEx Query. To start off, I will show how to create a simple Query, to get a feel of how to use the Query Designer.

Adding an InfoProvider

The first step is to find the InfoProvider that holds the information we want to create a report on. Click New Query and a dialog box will appear, giving the option of choosing whichever InfoProvider we want.

clip_image026

The Select InfoProvider window is similar to those seen before: there is a History of recently seen Objects, a Find section, and also the opportunity to use the system hierarchy of the Info Areas to navigate and find the information we want.

This example Query will keep to the HR theme used in the BEx Analyzer article, so this section will create a Report with an ‘Organisation Unit’, a few Free Characteristics, and a Key Figure.

clip_image028

Clicking on Info Areas causes the system to present all the different Info Areas contained in the BW System.

clip_image030

After navigating through the Info Areas, there will be a number of InfoCubes, signified by the cube icons. (DSOs would be represented with cylinder icons, and MuliProvider would have an icon showing cubes stacked upon each other). In the screenshot above, there are three identically named Cubes, and so the Technical Names need to be visible.

clip_image032

To do this, select the Display Object Name As icon (the wrench), and select from the options available. With the Technical Names visible, it becomes easier to determine which InfoCube is required. Double click to Open it.

The Find tool on the sidebar looks and acts just as in the BEx Analyzer, and allows wildcard searches that search both Descriptions and Technical Names.

clip_image034

Once loaded, the InfoProvider will be loaded into the Query Designer. You can see there are three high level folders: Structures, Key Figures and Dimensions.

Adding a Dimension

Dimensions is the name given to high level folders in InfoProviders. Once opened, a list of Descriptors (with the ‘three triangle’ icon) appears. These are not Fields, but still high-level Containers containing Fields.

clip_image036

For instance, opening up ‘Employee’ in the screenshot above reveals two Fields (‘Employee’ and ‘Person’). Every Field listed under Dimensions is a Characteristic that belongs to the InfoProvider.

clip_image038

As mentioned before, this example Query will contain ‘Organisational Unit’, which in this InfoProvider is listed under Dimensions, and the ‘Organisational Assignment’. To add this Characteristic to the Report, you must first find it. Click on Rows and Columns, and drag the Characteristic across to where you want it. Once the mouse is released, it is added into the Report.

clip_image040

Once added, a small Preview appears showing the layout of the Report as it currently stands. This is all that is required to create a listing of ‘Organisational Units’ and ‘Employees’ inside, and run it.

Clicking Save for the first time will open a window asking for a Description and a Technical Name. For the latter, your technical team should let you know the naming conventions of Queries saved into the System. (Spaces are not allowed in a Technical Name.)

Click Save and you have your first defined BEx Query.

clip_image042

You can then open the BEx Analyzer and see how it displays. In this case, it displays a simple list of ‘Organisational Units’.

When field are added to the Rows/Columns section, they will also appear in the Filters section, giving the opportunity to add Default Values to them if you wish. This will be explained in detail later in the book.

Adding a Key Figure

Next, we will add a Key Figure to this Query.

clip_image044

Expanding Key Figures reveals a list of Base Key Figures as well two folders. A Calculated Key Figure is a Base figure with a calculation or formula applied to it, whereas a Restricted Key Figure is a Base figure with an applied filter. These will be expanded on later on.

To add a Key Figure, find the one you want to include in the list and drag and drop into the Columns window.

clip_image046

The Preview will update, showing a column where the Key Figures will reside.

clip_image048

Adding another Dimension, for instance ‘Gender’, adds further breakdowns of the selected Key Figure. The Preview screenshot shows how this builds up the Query, so that each ‘Organisation Unit’ is then broken down by ‘Gender’, and the ‘Number of Employees’ Key Figure is divided accordingly.

Viewing an Edited Query in the BEx Analyzer

When opening a newly saved Query in the BEx Analyzer, it is important to note that if the report was left open, refreshing the Query will bring back the old results. The system will not recognise that a Query has changed, because when a Query is run it is held in the computer’s memory. This means that the design of the Query will only be refreshed if it detects the user is logging on for the first time, or if the session is closed and reopened.

So, if you have made a change to the Query and want to view it in the BEx Analyzer, always make sure to disconnect from the system, connect up again and then refresh your data.

clip_image050

Here, the above screenshot shows our Query as it looks in the Analyzer: we have each ‘Organisational Unit’s ‘Number of Employees’, broken down by the ‘Gender’ dimension with a subtotal.

Accessing The Query Designer from the BEx Analyzer

If you want to make a change to the Query without opening a separate session, it is possible to access the Query Designer through the BEx Analyzer.

clip_image052

Once you have run a report within the Analyzer, the Query can be edited by selecting the Edit Query menu option, either within the Tools icon on the toolbar or through the menu bar. This will open the Query Designer in a window that is linked to the current BEx Analyzer session.

Once it is opened, it is not possible to go back to the Analyzer without closing the Query Designer. While this can be an occasional pain, it does ensure that every time the Query is run after editing through this option, the whole page is refreshed.

Hierarchies

Hierarchies are a feature that can help structure Queries, add handy features, and alter the look and feel of a report.

Key Figures can be structured to form a hierarchy. An example scenario which would require this could involve a ‘Headcount’ field which was broken down by a ‘Number of Employees’ field.

clip_image054

By dragging ‘Headcount’ onto ‘Number of Employees’, it will be moved across to the right to show that a hierarchy has been made.

clip_image056

To arrange Key Figures within a hierarchy, drag the fields up or down into the order that you require. A horizontal line will appear to show where the field will appear when dropped.

clip_image058

When loaded into the BEx Analyzer, it shows ‘Number of Employees’ along with the other two fields, but notice the different colours of the headings. The colour coding represents that the two right fields belong to the other field’s level. There is also an ‘expanded’ triangle icon visible in the higher level field. Clicking on it hides the lower level Key Figures, changing the ‘expanded’ icon so that it faces right. This can be a useful tool if you want to have columns that break-down a Key Figure further.

Some Characteristics can also have a hierarchy attached to them. In the Query Designer, clicking on a Characteristic and opening the hierarchy tab in the Properties section gives this option.

clip_image060

This screenshot shows there are currently no active hierarchies within the Query. (They may exist within the BW Tables, but the Query might not be able to see it within the report.) Clicking on the button within the Selected Hierarchy section brings up a window where one can be selected.

clip_image062

A hierarchy can be chosen by name by clicking on the drop down menu, which displays a list of active hierarchies. (Within a BW or SAP system, a field can be placed into any number of hierarchies where they can be set up into certain structures.) After clicking OK, the Properties pane will show that the Active Hierarchy Display check-box is checked, and the Characteristic’s icon will also change.

clip_image064

The above table shows the ‘Organisational Unit’ field structured within an ‘Organisational Plan’ hierarchy. This shows that the top level Unit contains two levels, which can be further expanded to reveal even more levels by clicking on the triangle icons next to them.

clip_image066

Right-clicking on a level provides menu options to expand the table. ‘Not Complete’ brings up a menu that allows all levels to be expanded at the same time, saving time clicking – just select the lowest level to expand all the way down.

clip_image068

Each different level of hierarchies shows a subtotal of the lower levels. For instance, in the above table, ‘Human Resources’ has 13 employees, which are divided into 7 in ‘HR North’ and 6 in ‘HR South’.

When a hierarchy is active, it is still possible for the end-user to alter the view themselves. By turning it on while designing the Query, we are just making the hierarchy functionality available to the end-user, and they have the option of turning it off altogether, or altering the order.

Categories

About the Author:

Pete has been working with SAP technologies for over 10 years. He started out as an ABAP consultant and then moved on to BW where he has worked many different clients covering a wide variety of industries. "I love introducing SAP technology (especially BI) to new clients and showing them how they can go from zero to hero within their business in super fast time". Contact me on twitter @PeterMoxon

4 Comments
  1. Jim Howe

    Once I build a query with a selected data source, am I able to go back and modify that source? For example, I used InfoCube ABC the first time and I want to keep the query but modify it to use InfoCube DEF. Any restrictions on the change?

    • Peter Moxon

      Hi Jim,

      Each query is fixed to a specific infoprovider so you can’t change the datasource i’m afraid.

      In the back-end of the BW system you can copy queries from one inforprovider to another one though, as long as they have the same structure.

      • Jim Howe

        So if I’m using BEx Query Designer, my alternative is to create a new query off the old one but with a different data source. But if I do that, can I change anything else in the original query? I guess I can always create a new query from scratch….

        • Peter Moxon

          Using the query designer you can only create a new query based on the old query if you stay with the same data source. To use a new data source you would have to create a new query from scratch.