Structuring SAP BW Queries for Re-Use

Structures

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.

clip_image002[4]

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

clip_image004[4]

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.

clip_image006[4]

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.

clip_image008[4]

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.

clip_image010[4]

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.

clip_image012[4]

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.

clip_image014[4]

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.

clip_image016[4]

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

clip_image018[4]

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.

clip_image020[4]

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.

clip_image022[4]

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.

clip_image024[4]

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

clip_image026[4]

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

clip_image028[4]

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.

clip_image030[4]

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!

clip_image032[4]

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.

Learn The SAP BW BEX Analyser

Introduction

When I was first putting this book together, I was wondering what the best sequence would be to teach you about BW reporting. I say that because there are two elements involved: creating queries and running these queries in order to analyse your data.

First of all, this article will introduce the BEX Analyser and have a quick look at how reports are run, followed by a look at some of the basic functionality within it. Then I will swap over to Designing Queries using the Query design tools. After I have completed this run through, I will then come back into the BEX Analyser, run some of the queries we have created, and also show how to build a Workbook application that combines multiple reports together and makes it possible to build a unique user interface for your reports.

The BEx Analyser

Let’s begin by introducing the main functions of the BEx Analyzer and show you how to get started running queries.

To open the BEX Analyser, from the Start button click All Programs and then select Business Explorer followed by the first option, ‘Analyzer’. This will launch Microsoft Excel and the BEx Analyzer add-in.

clip_image002

Click on the Add-in tab to find the BEx Analyzer toolbar. On the left, you can see the Menu Commands, which brings up a drop-down menu with various options. These can also be found under the Custom Toolbars section on the right as icons.

Before I go into detail about what these options do, the next section will show how a report runs, so you can see what we are going to be working with straight away.

Logging In

Before you can open a Query, you need to log in to the system. To do this, you have two options.

clip_image004 The Connection icon brings up the Login box and allows you to log in, but will not give you an option to open a query.

clip_image006The Open icon will allow you to log in and it will also give you a dialog box where you can choose a query to execute. The Open Query menu command will also do this. (Of the two options, I recommend this one.)

When clicking either of these icons, the Login dialog box will appear.

clip_image008

Enter your client number (which your system administrator will have given to you), username and password. The language box is optional. Finally, press OK.

Selecting and Executing Queries with the Open dialog

In this section, I will go through how to select your first query. You do this using the Open dialog box, so let’s have a look at this in a bit more detail.

There are five ways to search for a query: Find, History, Favourites, Roles and Info Areas.

History

clip_image010

As soon as the dialog opens, it shows a History of some of the reports that I’ve selected previously, as shown in the screenshot above.

Favourites

clip_image012

Specific reports can be added as Favourites so that they can be referred back to, perhaps on a regular basis (for instance if you need to run a report every month.) As you can see I have got a few listed as favourites already.

Roles

clip_image014

Then we have Roles. In this example, I have not been assigned to any Roles but a BW User is usually assigned to a certain number of BW reporting Roles, which are set up in relation to the tasks that the user carries out. For instance, a sales manager would belong to a sales reporting Role and would see a number of reports listed here that he could run.

Users can be assigned to multiple Roles. A sales manager who has employees that work for them would potentially be assigned to both a sales reporting and HR Roles, for example.

Info Areas

clip_image016

Then we have Info Areas, which allows you to see the entire BW System. Because of this, some users may not see this option. In this section you can click on the system’s InfoProviders and from there it is possible to select all types of reports on any type of data that is set up in your system.

The screenshot above shows the initial screen when you click on the Info Area’s icon. Think of each Info Area as a folder containing sub-folders, which contain other sub-folders for the different sections of an organisation, for example. It could be HR, finance data, procurement data or anything like that.

Find

clip_image018

Finally, there is the Find option. Because there can be many reports and folders within a BW System, it can be confusing trying to find reports that you’d like to use. With this in mind, SAP have built in a find function for your queries, views and workbooks.

When you click Find, you will get a search box. Type in the text you wish to search and click Find. The system will then return a list of matching queries and views in the lower section of the search. From there, double click the required entry and your report will be executed.

Another way to search is through the Info Areas section. Here, the Name input box also acts as a search function. For instance, if you type ‘employee’ into the Name input field and press Enter, a list of results will be displayed. If there is just one result it will execute the query straight away.

This is a useful shortcut and I tend to always key my search phrases in here because normally you always get presented with more than one result.

Executing the Query

@clip_image020

In this example above, I have searched for ‘employee’ in the Find section and selected the query I want to execute.

Once you have found the query you wish to execute, double click on it (or single click and select ‘Open’) and the report will be executed.

clip_image022

Here, we can see a report that has finished executing and has been transferred to Excel, where we can do with it as we please.

The Default Query Output

clip_image024

The above example shows the default Table display. There are two columns: one for ‘Organisational Unit’ and another for ‘Number of Employees’.

A query’s default output is initially very simple, and it is at first; you can customise the look of the report like any other Excel worksheet. There are several functions available within the report: Chart, Filter and Information.

The Chart button converts the table into a chart, which while basic in functionality is useful for getting a quick visual representation of the data. The chart can be customised like any generated in Excel.

clip_image026

If the report contains a large number of records and you only want a Chart of a select few, then you can use the Filter button.

The Filter function brings up another table which contains free characteristics. Any field that does not belong to the body of the report resides here. In BW, if a field contains a numeric value then it is a key figure, whereas anything that can be ‘drilled down’ is a characteristic. The fields in the Filter table are free characteristics because they have not been added to the report, although they can be dragged and dropped into the report at any time.

clip_image028

To filter a report, right click on a field, select ‘Select Filter Value…’ and a dialog box will appear. Select the values you wish to filter from the history list, and the report will be filtered accordingly.

The third button is Information, which displays extra rows within the Worksheet that contain technical and descriptive information about the report.

clip_image030

This table gives us information about the author, current user and which user last modified the data, with the query’s technical name description on the left column. The right column gives timestamps which show the last time the data was refreshed, as well as the key date for the query (which will be explained later in this book).

This information is useful because it can help with troubleshooting any problems with your data. For example, if the data for a query is refreshed every day, or during the night, and a user notices that something is wrong, the Last Refreshed entry can help determine whether the data just needs to be refreshed, or if there is another problem, for instance with the back-end SAP system.

The BEX Analyser Toolbar

clip_image032

There are two rows of icons that make up the Custom Toolbars, as seen above.

The first row is used for designing analysis applications, for when the user wants to create a unique interface for their reports. These will be focused on later in the book when detailing how to build a custom analysis application.

The bottom row is where queries, workbooks and query views are opened and saved, as well as the functions for connecting the system and refreshing the queries, and it is this row that this section will focus on.

clip_image034

Open Folder allows you to open a Query that has already been defined. It also allows us to open an Excel Workbook. (Workbooks can be saved into BW, which allows it to be used by other users, as well as being another place to back-up a Workbook.)

clip_image036

Save allows you to save a query View or a Workbook. Saving the query view doesn’t save the entire workbook, but instead saves the query as the View that the user has customised (for instance, if they have specified a certain part of the data to drill down into). When the query is re-opened, it will open the View as previously saved.

clip_image038Refresh refreshes all the data in the Workbook. By default, this option is enabled, signifying that the Automatic Refresh is turned on. This means that whenever a user drills down into a query, the data is updated straight away. If clicked, and ‘turned off’, then drilling down into a field will not cause a refresh and you will have to refresh the data manually.

One thing to remember is that when working with multiple Queries in one Workbook, this icon will force a refresh of every single report at once.

clip_image040Change Variable Values allows you to define an interface for the end-user to preselect data, when it isn’t necessary to bring back all the data in a report, instead retrieving data for only the selected values. A selection screen needs to be set up for this to function, which will be explained later in the book.

clip_image042

Tools offers a series of tools to choose from. We can create a new query, and access other software. There is also an option to Copy Sheet, which duplicates sheets in the same manner as a non-BEX workbook.

· BEX Broadcaster is a separate piece of software that sends reports to staff in an organisation, as long as it’s configured properly and the necessary SAP licences have been purchased. Planning Monitor is used to save data inside a Cube, instead of reporting on them. Both are separate and aren’t covered in this book.

· BEX Report Designer is for those who wish to create pixel-perfect layout designs for reports, if you require them to be print-ready.

· clip_image044
The BEX Web Analyser option links to the web interface version of the Analyser. When part of an active Query selected, an Edit option appears in the Tools menu. Clicking this will open the BEX Query Designer tool.

clip_image046Global Settings: clicking this will open up a dialog box which contains all manner of settings, divided into four tabs (Behaviour, Select Default Workbook, Trace and Statistics) which you can edit to your own personal preference. For instance, there are preferences that control whether you want the BEX Analyser to open whenever you open Excel. I recommend that you play around with the settings and see which suits your way of working.

clip_image048System Information is where you connect and disconnect from the System. Clicking it while connected provides information about the connection (the client, username and language, as well as the Application Server), as well as the option to Disconnect without quitting the BEX Analyser. Clicking Connect brings up the Connect dialog box.

clip_image050Help opens Application Help in your web browser.

Data Analysis Functions

This section will explain how to use some of the BEx Analyzer’s powerful data analysis tools, focusing on Filters and Navigation options.

Using Filters

clip_image052

We have already touched upon the Filter button previously in this article, so you should be familiar that it produces a list of free characteristics, which are called as such because they are not being used within the Query. (The above example shows one characteristic being used, ‘Organisational Unit’. Every other characteristic is free to be added into the query as required.)

At the bottom of the Filter table, there is a field named ‘Structure’. Any key figures, numeric values in a query, are contained within a structure. This will be covered in detail when we begin designing Queries, but the thing to remember now is that when ‘Structure’ appears here; it will contain all the fields that are key figures within the Query.

Drilldowns and Navigations

We will now look into how fields can be used, and added to a Query.

Double click on a field in the Filter table to refresh the Query and add that field to the table.

clip_image054

In this example, the ‘Age in Years’ field was added. This was a Hierarchical Field, which means that different age bands have been split into different hierarchies, allowing us to breakdown all the employees in the ‘Hamburg Production Plant’ into age categories.

clip_image056

Double clicking on a field’s arrow will then expand the hierarchy to a lower level, showing the individual groupings of employees by their age.

clip_image058

If you want to remove a drilldown column from the table, there are several options.

· Right click on the field you wish to remove, and select Remove Drilldown from the context menu, which will clear the column.

· There are also Back and Back to Start options when you click on any field in the Query. Clicking Back will take the Query back one step, like clicking Back in a web browser, whereas Back to Start will take the Query to the beginning, taking away all filters and changes.

Adding Fields

Now, we are going to have a look at adding fields to the Table, but using different techniques.

As well as double clicking on a field, we can drag and drop fields into the table. Click on the field you wish to add, and hold the left mouse button down while dragging across.

clip_image059clip_image061 clip_image063

A cursor with a ‘no-entry’ sign will appear as you begin dragging, which will change to a square with an arrow pointing downwards, which means Drill Down. Releasing the mouse button over the first column of the table will add the field and drill down the report.

clip_image065

Dragging a field to the header of a column will change the cursor to one with a ‘refresh’ symbol. This means that if you drop the field here, it will replace that field with the one you are adding. Dragging the other way around, or dragging a header to a field, will replace the field in the same way.

clip_image067

In addition, dragging a field over key figures will change the cursor to a square with the arrow facing to the right, which means Drill Across. Dropping the field here will split the field into two columns.

clip_image069

The above example shows the result of dragging a ‘Gender’ field into the ‘Number of Employees’ column, giving a different type of layout to the report: there is now one column for male and another for female, whereas drilling down would have given two rows for each ‘Organisational Unit’.

We don’t just end there: we can click on a field within the Table and drag it to another part of the Table, and change the report view. For instance, dragging ‘Male’ to the ‘Hamburg Prod’ field changes the view like this:

clip_image071

If you are familiar with Microsoft Excel, think of this as acting in a similar way to the Pivot Table or Transpose Copy/Paste functions (in that it takes all the records going down, and Transpose Paste makes them go across).

clip_image073

To remove a field from the Query altogether, drag the header row to an area outside any tables until the cursor shows the ‘square-within-a-square’ icon above. Drop, and the field will be removed from the Result Set.

Once users become familiar with the similarities with Excel, it can really turn light bulbs on as to how much time can be saved using BW’s BEx functionality!

In BW, and SAP in general, there is always more than one way to do things. For instance, right clicking on a field and choosing Drill Down or Drill Across from the context menu does the same as dragging and dropping, and right clicking within the Table’s header row shows options to Exchange and Add Drilldowns According To, which again act in exactly the same way as before.

clip_image075 clip_image077

Filtering Data

There are a variety of different options for filtering data in a Query, using the Select Values dialog box. To access it, right-click on a field in the Filter table and select ‘Select Filter Value’.

When there are no filters in place, it will only show one window with Recent Selections. Click ‘More >>’ to expand the dialog to give us a two-window view, which will show us what has been selected.

clip_image079

Select the drop-down menu at the top of the window, and it will initially show the History, a list of fields that have been used as filters in the past.

clip_image081

Filters can also be saved as Favourites. These can be added by clicking on a Filter, moving them across to Selection (either by using the right-hand arrow button or just double clicking it) and clicking the floppy disk ‘Save’ icon. A dialog box will appear; type a name for the selection and click OK. The filters will then appear under Favourites as a folder icon, which you can highlight and select all at once, or click on the ‘+’ icon to view and individually select filters within.

clip_image083

Single Values allows us to go back into the underlying database and bring back all the records we have in our InfoProvider. Individual values can be selected to the selection in this way, again either by clicking the Move to Selection button (the blue, right-facing arrow) or just double clicking the value itself. A highlighted range of values can also be added at once.

clip_image085

Direct Input can also be used to find values, once you are familiar with the underlying Keys of records in a SAP system. In the screenshot above, we can see that each record is given a text value, but it is also possible to show them as Keys, which display each Unit as a numerical Key. Selecting “Text [Key]” displays both.

clip_image087

Entering the Key number into the Direct Input field and clicking on the Move to Selection button adds it to the Selection, and the system will recognise the value from the code as a record, pick up its associated text and brings that across as well.

clip_image089

There is also a Search option, which provides options for searching through the master data of your system and saves you from scrolling through a large list of values. You can search both by Text and by Key, as well as different Attributes which are associated with the field we are filtering. There are three search boxes; the second and third allow you to select from the History, Favourites or Value Range sections, giving you as many ways as possible to find Master Data to add to the Filter list. Access this by clicking the ‘two squares’ icon to the right of the second and third search fields.

clip_image091

Finally, Value Ranges is similar to many other search options that exist within SAP systems. Before, we have selected individual values, but this allows us to specify a range. The screenshot above shows the range set between Keys 1,000 to 2,000. There are several logical operators you can use: between, less equal, greater equal, less than and greater than. Again, there is the Select Values dialog available, to search through Favourites, History and so on.

Clicking the Move to Selection button adds the Value Range to the bottom of the Selection list.

clip_image093

All of the values that have been chosen for the Filter are collected in the Selection side of the window. This contains various options of its own.

Everything that has been selected in this screenshot is a ‘positive selection’, which means that they are values we wish to include. If you wish to exclude a value or range from inclusion, then right click the value and select Exclude Selections from the context menu. The icon will change from a green to a red background, denoting its exclusion from the Selection range.

Remember that once you have compiled a list of values, you can save them to the Favourites section.

Once you have compiled all the values you wish to filter the report with, click OK and the Query will contain the reports that match the Selections made.

Filtering Data in a Table Group

Sometimes, it is necessary to apply filters using the data in the Table view. For example, in an HR ‘Gender’ field, we may have entries for Male, Female, and ‘Unknown’ for employees that there is no data for this field, and we want to filter out the Unknown data.

clip_image095

To select the fields, hold Shift and click on the fields to select a range. (If the rows are not next to each other, hold Ctrl and click on each value until all are selected.) Right click, and select Keep Filter Value from the context menu, and the filter will be applied.

clip_image097

Here, you can see that the Table has been filtered, and the Filter grid shows that ‘Gender’ has been filtered on Male and Female. At the same time, it removed the ‘Gender’ field from the Table view, which may not be the desired action. Selecting Keep Filter Value on Axis from the context menu instead will filter the values but leaves the field in place. Select Filter Value is also available, and brings up the same dialog box as before.

clip_image099

Below these options, there is Filter and Drill Down. This tries to do two steps in one. Where we had selected a field previously to drill down a report with, we would drag or double click to apply it to the report. This option, on the other hand, allows us to apply a filter (such as ‘only Male and Female’) and then choose another filter to add to the report.

clip_image101

Here, we have selected ‘Nationality’ from the list, which has filtered the report by Male and Female and then drilled down into different Nationalities.

As well as using the filter options in the context menu, it is possible to filter using one individual value simply by double clicking on it, which is a fast way to filter data in a report.

After filtering in this way, you can continue working however you want, including changing the filter values as described in the previous section. You can also Drill Across using one of the existing columns; you don’t have to use a field from the Filter table.

The Remove Drilldown option works in the same way as dragging a field outside of the Table area. Swap Axes changes a field so that it Drills Across with the fields, instead of Drilling Down, changing a vertical column into a row with the key values underneath.

clip_image103

This Report is the result of using the Swap Axes function on the ‘Nationality’ field, drilling it across so that there is only one row underneath, showing the ‘Number of Employees’. If there are too many columns it can become somewhat unwieldy.

clip_image105

This Report, on the other hand, shows the ‘Number of employees’ first, and then the data is drilled down by ‘Nationality’, and then across by ‘Gender’.

These demonstrate that it is possible to quickly swap over axes on a table so that the report changes view, so in certain situations it can look a lot better.

What these examples show is that you can modify the layout of your report, and try lots of different settings; the tool is designed for you to play around and analyse your data however you like, and really find the useful information by looking at it in different ways. If your report starts to look messy or you make a mistake, the Back and Back to Start functions allow you to return to a previous state and try something else.

clip_image107

The Sort menu allows you to sort the fields by their Text value, and also their underlying Key value. Every field within a SAP system has a Text value and a Key value.

Characteristic Properties

clip_image109

Clicking on Properties brings up a dialog box which allows you to choose the Presentation of a particular field, and if you change this to Key, the report will re-run and the Key values will be displayed instead of Text values (so, ‘1’ instead of ‘Male’). You can also set it to show both at once.

clip_image111

Sometimes, when displaying fields as Keys, some may display as a # symbol. This means that no values exist for these records, hence the Text value ‘unknown’.

Knowing Key values may be more useful for some characteristics than others. For instance, ‘Gender’ would only have 2 values (for Male and Female), but ‘Organisational Unit’ may have hundreds. From my own personal experience, users start to remember the Key codes for characteristics, sometimes knowing the code before they can remember the Text.

clip_image113

The above Table shows both Text and Key values, and should be familiar with those who use the SAP back-end system. If you are familiar with the HR system, you will be very familiar with the above codes, having used them on a daily basis.

clip_image115

Presentation options are remembered for each characteristic. In the example above, the system has remembered that ‘Gender’s presentation option was Key and Text and has built the report accordingly.

clip_image117

It is also possible to sort from the Properties dialog box. Select whether to sort by Text, Key or Selection, choose the field we want to sort by, and then choose the direction.

Another setting in Properties is Suppress Result Rows. Normally when you build a BEx Query, the characteristics that you use in your Query nearly always generate a subtotal for you. What this means, is do you want to remove the subtotal for this field. Usually this is set to ‘always’.

clip_image119

When this is changed to ‘never’, an extra Result, or subtotal, row appears for every field. The above report shows subtotals for Gender (drilled across) and Organisational Unit (drilled down). With larger reports, you may see that subtotals can become a pain, and you may start to turn them off for specific fields.

clip_image121

There is also a ‘conditional’ option, which displays subtotals for all records apart from those which only have one unique value, in which case a subtotal would not be displayed. In the above report, because there is only one row of data for the Austrian and Australian Nationalities, there is no subtotal, unlike Canadian where the data has been broken down into two rows (Male and Female).

Attributes

Attributes are a list of related fields that can be added into a Report. These are in addition to the list of characteristics in the Filter table, and can also be added into the Report, but they are linked to each individual characteristic.

Some characteristics have Attributes, some don’t. For instance, in our HR example, ‘Nationality’ has no additional attributes, whereas ‘Employee’ has several, including ‘Organisational Unit’, ‘Personnel Area/Subarea’ and so on. These fields may already exist as free characteristics, but attributes help to describe a characteristic. In the back-end of BW, where the relationships between Fields are set up, the ‘Employee’ characteristic would have been given a range of attribute fields to help give additional information for the ‘Employee’ field, even when it is not explicitly added as a characteristic.

All will become clear.

clip_image123

When a characteristic has additional Attributes, an extra tab will appear in the Properties dialog box. Clicking on this tab reveals a list of fields that are associated with the characteristic (‘Employee’ in this example) within the SAP system. Some will match the characteristic fields in the Filter table, some won’t, but either way, every field in the Available Attributes box is there to provide additional information. If a characteristic is available, then always use that instead, as the Queries will be faster and the drilling down functions will be available as well (Attributes do not have this capability, and exist for display purposes only).

To add Attributes, transfer them over to the Selected Attributes area by selecting them and clicking the blue right-facing arrow button.

Double clicking on an Attribute will open a Display Properties window. There are only a couple of properties that can be set for Attributes.

· Text determines whether the Default, Short or Middle Text is displayed. (For those new to SAP, fields can have various Text descriptions in addition to their Text and Key. There may be Long, Medium and Short Text, or no alternatives at all.)

· Presentation sets whether to display the Text, Key or both, as in the Characteristic Properties

clip_image125

Clicking OK will close the window and add the Attributes to the report. They will be inserted into the report, right next to the characteristic field, causing a lot more information to appear that can be used to reference and use as you wish.

clip_image127

In the above screenshot, we have ‘Employee Group’ as both an Attribute (to the left), and a Characteristic (to the right) within the same report. The characteristic has the additional functionality of being able to display subtotals (and can also be drilled down or across), but otherwise the data is the same.

Do not worry if the idea of having the same data as an Attribute and a Characteristic doesn’t make sense; it all relates to how the BW system is built in the back-end. Again, if a Characteristic is available then always use that over an Attribute, but if you do not have one available look into a Characteristic’s Properties and see if the Attribute is available there to add to the report.

Structure

Before looking at the Query Properties, we will look at Structure, which represents the key figures, the numeric values in the report. Right-click on Structure and choose Select Filter Value.

clip_image129

Instead of individual values, this is representing the key figure fields we have available. In this example, there is only one figure field available (‘Number of employees’). Removing this figure field from the Selection window removes the column from the report; adding it back will restore the column.

clip_image131

Double clicking on Structure brings up the Local Formula window. This option allows the creation of brand new fields based on Key Figure Fields that already exist in the report. Formulas are created by choosing a Field, and creating a mathematical formula around it. This will create a new field in the Query with the formula applied to each record. There are also some basic mathematical functions (EXP, LOG, NODIM, and SQRT) that can be added into the formula.

If the formula is incorrect, the text in the Use Formulas box will turn red which signifies that the system does not recognise the formula.

Clicking OK will cause the report to run and the field will be output to the right hand side of the existing fields.

Key Figure Properties

clip_image133

To access the Key Figure Properties, right-click the header row and select Properties from the context menu. Because we are only talking about numbers, the options are a little different.

The Number Format tab contains simple options to determine the scaling factor, amount of decimal places, and whether the field should be highlighted.

The Calculate tab contains options to Calculate Result (for when there is a Result for a characteristic) and Calculate Single Values (for individual values), which can be used to determine how to calculate – count all values, show an average, the minimum or maximum, and many more. You’re not just stuck with summation. The Cumulated check-box can be turned on to cumulatively add figures down the field and it can be determined where the cumulative function calculates (along the Columns or the Rows).

The Sorting tab contains an option to either leave the sorting alone, or to sort by ascending or descending values.

As ever, play around with the settings to see how they work.

clip_image135

Fields that have been created with the Local Formula window have slightly different options in the context menu. Add Local Formula adds a new one to the Query, and Change brings up the Local Formula window so that the formula can be edited. There is also a Delete option.

Convert to Formula appears in both types of Key Figures fields. I don’t want to cover this right now; it is a very powerful option that allows you to create an Excel formula, but once this is clicked a Query cannot be converted back to a standard Query.

An example of why this feature would be used: when designing a fixed layout for a report, with for instance ‘Calendar Year’ on the left hand side, and a sum of annual salaries broken down by month along the top row, and this setup would never change, you could Convert to Formula and then apply your own formula calculation, retrieve data from the BW system to be displayed in the report, and execute your own formula which will not be overridden every time the Query is refreshed.

By ‘a static report that never changes’, this is because the OLAF functionality (drilling down, etc.) will not work and the Query will only remember the drilled down setup in place as you click the Convert command.

While this function is very useful, it is not used very often, and so I will not go into detail in this point. Later in the book, when covering Designing Queries, there will see Cell Definitions, an option where formulas can be embedded into cell definitions, that personally I find a better option for this. I would only use Convert to Formula for when Microsoft Excel’s functions are required in a report or if a formula has to be updated on a regular basis using external data (not from an SAP system).

Query Properties

clip_image137

This window can be brought up using any context menu’s Query Properties option; it will always be the same no matter what has been right-clicked.

As can be seen above, there are lots of tabs and quite a lot of functionality. What the window shows first is the current view of the Query. The three boxes under the Navigation State tab show the Columns (which is showing Structure, where Key Figures are kept), Row (in this example, ‘Employee’) and Free Characteristics (those listed in the Filter table) that can be dragged across each box as we please.

Double clicking on a Characteristic in the Columns and Rows sections brings up the Characteristic Properties window, with the same options as when selected in the Report.

The Query Properties window is very handy because Queries can be restructured, for instance when several Fields need to be added, without refreshing each time (which if it takes thirty seconds to refresh the Query, could soon add up with large reports) the Query is altered.

clip_image139

The Data Formatting tab provides options that determine how the Query is presented. By default, reports are presented in Multidimensional View, which means that it is possible to drill down/across to get a different view, and see different dimensions of our data. There is also Tabular View, which takes the multidimensional functionality away and gives a basic listing report. Also, Attributes do not have to be directly next to a Characteristic when a report is structured.

Under the Multidimensional View option, there are two similar options called Display the Columns/Rows Hierarchically and Explain Till. If these are ticked in a Query with multiple Key Figures, then this option is used to choose how much of a local hierarchy for those figures is displayed.

clip_image140

clip_image142clip_image144

The above example shows the user selecting ‘Organisational Unit’ as the only expanded part of the hierarchy, hiding the lower three levels of the report. Clicking on the arrows next to each field in the report expands the report, showing the next level down (‘Employee group’).

clip_image146

The Presentation Options tab provides options for how the results are formatted, including where the results are positioned, and the amount of zeros to be displayed.

clip_image148

Display Options includes options to determine how to display Scaling Factors, Document Links and whether to Suppress Repeated Key Values when the same figure is repeated multiple times in one report.

Documents can be created against Data, Characteristics & Key Figures, and are used to explain parts of a report. They are stored alongside the Objects in a BW system, although I must say they are rarely used, with companies preferring to use Word or other outside software.

The Currency Conversion tab, if there are currency values in a report and currency translations are set up in the BW system, provides a conversion tool (for example, to convert £ into $.)

Zero Suppression contains an option to suppress (hide) zeros in a report. Properties contains general information about the Query, and finally the Conditions tab would show any conditions that have shown up in the Query. When we start to create Queries later in the book, there will be more about Conditions.

Saving Workbooks and Query Views

BEx Queries are embedded into Excel Workbooks, and so we have the option of saving the workbook wherever we like such as the desktop, a documents folder, or a network drive. Saving a workbook with a BEx Query is identical to saving any other Excel file.

One thing to note is that when reopening a workbook, it is disconnected from the BW system. Right-clicking while disconnected will only bring a ‘Refresh’ option. Clicking this will bring up a window asking for your username and password, which will connect the workbook to the BW system, refreshing the data and making all options available once again.

Saving Workbooks on a BW System

clip_image150

To save a workbook onto the BW system, click on the Save icon in the toolbar, and select Save Workbook, which will prompt you to enter a name for the workbook and whether it should be saved within a Role for other staff and/or departments to access.

There is also the option to Save View. A View is the current view of the Query as it is embedded in the workbook, and this gives the option of saving the layout without also saving the Query’s data or workbook. Other sheets with other views would not be saved with this option.

clip_image152

Clicking Save View brings up the above window. Type a description and a technical name for the view, and click Save. The view will then appear when selecting Open, and this will bring up the Query report with all customisations intact.

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]

Never Stop Learning SAP at Udemy

Never Stop Learning SAP at UdemyThis month, Udemy launched Never Stop Learning to tell the stories of students who made life changes on account of taking Udemy courses.

In full support of the campaign, I’m offering all my courses for 1 low price: $49 to encourage more folks to #NeverStopLearning.

This means you get:

Know someone who wants to learn how to ? Feel free to share the direct link with them to join my course for these great discounts off the price while spots remain.

ABAP :

https://www.udemy.com/sap-abap-programming-for-beginners/?couponCode=NeverStopLearning

SAP BEGINNERS:

https://www.udemy.com/learn-sap/?couponCode=NeverStopLearning

BW BEx ANALYZER:

https://www.udemy.com/learn-sap-bw-bex-analyzer-online-video-training-course/?couponCode=NeverStopLearning

I really appreciate you taking the time to check out my course and share it.

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]