SAP BEX ANALYZER

AND QUERY DESIGNER

THE COMPLETE GUIDE

A COMPREHENSIVE STEP BY STEP GUIDE TO CREATING AND

RUNNING REPORTS USING THE SAP BW BEX ANALYZER AND
QUERY DESIGNER TOOLS

PETER MOXON

PUBLISHED BY:

SAPPROUK Limited

Copyright © 2013 by Peter Moxon. All rights reserved.

http://www.saptraininghq.com
Copyright, Legal Notice and Disclaimer:

All rights reserved.

No part of this publication may be copied, reproduced in any format, by any means,
electronic or otherwise, without prior consent from the copyright owner and publisher of
this book.

This publication is protected under the US Copyright Act of 1976 and all other
applicable international, federal, state and local laws, and all rights are reserved,
including resale rights: you are not allowed to give or sell this Guide to anyone else.
If you received this publication from anyone other than saptraininghq.com, you've
received a pirated copy. Please contact us via e-mail at support at saptraininghq.com
and notify us of the situation.

Although the author and publisher have made every reasonable attempt to achieve
complete accuracy of the content in this Guide, they assume no responsibility for
errors or omissions. Also, you should use this information as you see fit, and at your
own risk. Your particular situation may not be exactly suited to the examples
illustrated here; in fact, it's likely that they won't be the same, and you should adjust
your use of the information and recommendations accordingly.

This book is not affiliated with, sponsored by, or approved by SAP AG. Any
trademarks, service marks, product names or named features are assumed to be the
property of their respective owners, and are used only for reference. There is no
implied endorsement if we use one of these terms.
____________________________________________
Contact the Author

As the reader of this book you are my most important critic and commentator. I would
love to hear from you to let me know what you did and did not like about this book, as
well as to what you think I could do in future books to make them stronger.

E-mail: [email protected]

Please note that although I cannot personally help you learn the SAP BEx Analyzer and
Query Designer software applications, I am available for corporate hire for project
management, technical lead and mentoring programs.

Refer to my website http://www.saptraininghq.com to see all the training material I have

available and to get a good overview of my expertise.

4
5
Introduction
This book has been written with SAP End-User and Consultants in mind.
Whether your current job title is functional consultant, financial reporting
analyst, BW/BI Consultant, business consultant, project manager or
something entirely different, if you are responsible or have an interest in
creating SAP reports, then this book is for you.

Much of the book is written in the "How-To" style and will allow anybody to
follow along and create reports from scratch. It is written in such a way that
each chapter builds on the last so that you become familiar in lots of
different aspects of SAP BEx reporting to enable you to then start creating
your own reports and analysis applications as well as understand how SAP
have created the standard reports delivered with the base system.

The reporting principles and guidelines apply across all SAP modules whether
you're writing reports for HR, FI, SD or one of the many other modules within
SAP.

Over my years of working with SAP systems I have had the great pleasure of
working with some top-notch functional and technical consultants who know
how to document, plan and develop SAP reporting solutions of all types.
Likewise I have had the unpleasant experience of working with lower quality
consultants, who don’t understand the complete SAP reporting solutions and
toolsets resulting in poor quality reporting solutions. This ultimately often
results in project delays and cost overruns.

The aim of this book is to help you understand how SAP BEx Analyzer reports
are put together and used so that you will be able to produce reports on your
own quickly and efficiently resulting in increased work productivity and
giving you answers to questions about your own companies data and
performance.

6
How to Use This Book
There are several ways to go through this book and the best way depends on
your situation.

If you are new to BW BEx reporting then I suggest starting at the very
beginning and working through each chapter one after another.

If you are familiar with some BW reporting components then you may want
to use the table of contents and jump to the chapter that interests you, but
remember each chapter builds on the previous chapter so some of the
examples shown do require you to have knowledge of the reports we create
in this book.

7
Chapter 2: Creating Queries using the SAP BEx Query
Designer
In this chapter, 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

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 (like
the Characteristics we have seen in Chapter 1).

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

8
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
InfoProvider’s 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.

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.

9
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

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

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.

11
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.

12
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.

13
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.

14
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

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.

15
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.

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.

16
 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

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, referrring to Cell Definitions will covered later in the book.

New, Open, Save/Save All Execute, Check, Cut/Copy/Paste Toggle InfoProvider, Cells Exceptions & Properties Messages, Tech
Properties Filters, Rows & Columns Conditions & Tasks Where Used, Names
Documents

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.

17
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.

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
chapter, so this section will create a Report with an ‘Organisation Unit’, a few
Free Characteristics, and a Key Figure.

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

19
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.

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.

20
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.

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.

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.

21
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.

22
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.

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.

23
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.

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

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.

25
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.

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.

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

26
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.

By dragging ‘Headcount’ onto ‘Number of Employees’, it will be moved

across to the right to show that a hierarchy has been made.

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.

27
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.

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.

28
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.

29
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.

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.

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’.

30
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.

31
32