Structuring SAP BW Queries for Re-Use


When dragging Characteristics from the InfoProviders into the Columns section of the Query Designer, new objects are created which are named Key Figures. The icon next to the Key Figures section represents a Structure. This is how basic structural components of a BEx Query are grouped together.


Key Figure Structures allow the user to combine any number of Key Figures, whether they are regular, Calculated, or Restricted.


This also means that it is possible to save Structures and re-use them in other Queries. After giving the Structure a Description and a Technical Name in the Properties section, right-clicking and selecting Save As will save the Key Figure Structure.


The saved Structure will then reside in the InfoProvider section, under the Structures and With Key Figures folders. This means that even if the fields are removed from the Query, it takes a single drag-and-drop to restore the Structure, and it can also be used in any Query that uses the same InfoProvider.

There are also Characteristic Structures. These differ in that they are not created automatically; they are optional components of a Query. Also, unlike Key Figure Structures which allow the user to group together Key Figures, a Characteristic Structure is principally used to restrict data for the Characteristic.


Right-clicking in the Row section and selecting New Structure from the context menu creates a blank Structure. This will require a Selection, which will allow the adding of Characteristic Selections. Right-click on the new Structure and select New Selection, and then double-click the blank Selection, which will bring up the Select Selection window.


Here, we can drag across a Characteristic to the Selection and select values to filter it by.

When a Query is run, every value in a Characteristic which meets the Query selection criteria is then displayed. What the Selection does is filter to the specified values, but only for that specific row. For example, the above screenshot shows a Selection set-up that will filter the Query’s ‘Personnel Area’ row, showing only the values for the four cities determined in the Selection.

Multiple Characteristics can be added to further refine the Selection.

The resulting Structure will look like a pre-determined Dimension, but instead is a customised Restriction, and any number of these can be created within a Query. This gives a lot of flexibility in a report, which means that individual rows can show different Characteristics.


Characteristic Structures can be saved in the same way as Key Figure Structures, by right-clicking and selecting Save As. Make sure a Description and a Technical Name have been entered (the Save As window will give an opportunity to do this if nothing has been entered in Properties). They will appear under the Structures and Without Key Figures folders in the InfoProvider section.


Running a report with Characteristic Structures show how combining different Characteristics with restrictions is possible, all within the same report. This shows that we are not restricted to dragging and dropping fields into a Query and requiring a new column each time.

Cell Definitions

Using Structures in a Query brings the opportunity to use Cell Definitions. This means that formulas can now be created for individual cells in a report. This function adds another layer of flexibility to your Queries.

A report that is only made up of dragged in Characteristics would show the values that come from the Key Figures, or the Characteristics as displayed in the report. But, with Structures in place, there is the facility to define individual formulas for every cell, just as in Excel.


The above report is the result of two Structures: a Key Figure Structure that includes the Key Figures ‘Age in Years’ and ‘Length of Service’, as well as a Characteristic Structure that filters ‘Personal Data’ by Male, Female (from ‘Gender’) and German, American (from ‘Nationality’). The resulting values are showing the totals.

If we wanted to change the totals for ‘Male’ into averages, then we can create a Formula which will divide ‘Age in Years’ by ‘Number of Employees’.


So that ‘Number of Employees’ can be used for a formula without being visible on the report, it can be made invisible by adding it to the Structure, and selecting “Always Hide” in the Properties section, under the Display tab. The icon will change from green to grey to signify this.


Then, right-clicking on the Structure and highlighting New Formula will create a blank formula. Double-clicking on that will bring up the Change Formula window where a formula that will divide ‘Age In Years’ by ‘Number of Employees’ can be created.


After creating another Formula to create an average ‘Length of Service’, this Structure has invisible fields for both, which will ensure that only the averages will be shown in the report.


The resulting report shows the averages for each row of the Characteristic Structure.


As a result of having two Structures in the Query, the Cells button on the tool bar is no longer greyed out, as seen previously. Clicking on it changes the Query Designers to a Cell view. (This also adds a Cell tab to the window, next to Filter and Rows/Columns.)


This view shows a grid of all the Key Figures and Characteristics with Restrictions that have been defined, and formulas can be defined in each cell. It is important to note that, by default, the Query automatically works out what needs to go into each cell, based on the Selections and Key Figures present. Once a formula is in place in a cell, it will override the default Query action.

One use for this function is to add special factors into certain cells. Right-clicking and selecting New Cell Reference, adds a reference which describes what formula is currently active. Then, selecting New Selection makes the cell editable, so that additional Selections can be added to the cell. Selecting New Formula works in the same way.

Re-using Structures

We have seen how Structures can be re-used by dragging and dropping from the InfoProvider section into Rows and Columns. When creating and re-using Structures, there are however a few things you need to remember.


Firstly, when Structures are created and saved, they are greyed out in the Query view, which means that they become display only. To edit them, the Change icon must be made active in the Properties section of the Structure. Bear in mind that any changes will be applied to every Query that uses the Structure within the InfoProvider, so caution is advised, especially if they are used by other users across the system/company, affecting their Queries. Not to say that they shouldn’t be used, considering the time-saving qualities of saving Restrictions and Formulas within a Structure!


Secondly, if you wish to alter a Structure for a specific Query, then there is the option to Save As a separate Structure. However, it will still be available to all users in the InfoProvider. If you wish to alter the Structure without making it available, then selecting Remove Reference will unlink the Structure from the global Structure reference, which means that any changes made will only apply to the individual Query.

Get My SAP BEx Analyzer And Query Designer Book – FREE

SAP BEx Analyzer BookSome time back I created a book to go with the ABAP Video training course that many have signed up to and continue to do so. Why? Because it is bloody awesome 🙂

I also released another course focused on the SAP BW BEx Analyzer and to go with it I have just published the SAP BEx Analyzer Book through Amazon.

Here’s the best bit… Starting midday on July 3rd you can get it FREE. The promotion will last until July 7th so you have 5 days to grab your copy.

In return for the book all I ask is that you leave a review on Amazon. You don’t have to but I would really appreciate it if you can.

Feel free to pass the download link to anyone you wish. The more people that can benefit from the book the better.

[sociallocker] Link: US Kindle Edition – SAP BEx Analyzer And Query Designer Link: UK Kindle Edition – SAP BEx Analyzer And Query Designer [/sociallocker]

For those that don’t have a Kindle, No Problem – You can get the Kindle app on your Phone, Tablet,PC or Mac. Click this link and choose your preferred app.


Starting The SAP BW BEx Query Designer And Screen Layout

[frame link=”” 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=”” 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.


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.


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.


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


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

Business Analytics With The BEx Analyzer

The term ‘Business Analytics‘ (BA) is used in many different contexts. Be it specific skills, software applications or business practices they all share a common theme which is being able to analyse and explore data in an iterative fashion by using drill down, filters and often measuring against specific key performance indicator (KPI’s).

We are not just talking about static reporting here, BA is the process that we use to really dig deep into a datasets to find new trends by investigating historical information and in some cases applying statistical methods to drive out possible future projections.

It is this type of reporting that help some of the best companies in the world to understand their own market trends and plan strategic goals for the future to enable them to stay ahead of their competitors.

SAP has a rich software tool set that businesses use to carry out their business analytic processes of which the SAP BEx Analyzer is still the most popular. Continue reading

Introduction To The SAP BW Bex Analyzer

[frame link=”” linking=”new-window” align=”left” type=”paper-stack”]SAPBWBExAnalyzerCourseSaleDiscount[/frame]The Business Explorer Analyzer (BEx Analyzer) is the analysis and reporting tool of the Business Explorer that is embedded in Microsoft Excel. You can call up the BEx Query Designer in the BEx Analyzer, in order to define queries.

[button link=”” linking=”new-window” size=”small” type=”simple” title=”SAP BEx Analyzer Course”]Full Course – Click Here[/button]
Subsequently, you can analyze the selected Info Provider data by navigating to the query created in the Query Designer and create different query views of the data. You can add the different query views for a query or add multiple queries to a workbook and save them there.

You can save the workbook in your favorites or in your role on the BW Server. You can also save the workbook locally on your computer. Beyond that you can distribute the workbook by e-mail to recipients.

In this document we will learn how to access and run BEx queries in addition to introducing some reporting functionalities used within BEx.
Access BEx Analyzer

To start the BEx Analyzer, choose StartAll ProgramsBusiness ExplorerAnalyze. This will open Microsoft Excel with the SAP BEx plugins enabled.

Click on the Add-ins tab

Add-Ins Tab

Add-Ins Tab

Choose the Open Icon in the BEX Analysis toolbox

Open Icon In The BEX Analysis Toolbox

Open Icon In The BEX Analysis Toolbox

In the Opened menu click on “Open Query

Open Query

Open Query


A window will pop up asking you to log on to the BW system. Insert your SAP BW username and password?Note that the User for BW client is different from the user in ERP client. Continue reading