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.

How To Build Dynamic Selections And Filters In SAP BW BEx Queries

In this article, we will dig deeper and start having a look at Dynamic Filters. These allow us to present various options to the users to dynamically create selections using a whole range of variable types and variable processing types built into SAP BW.

The following will be covered:

  • OLAP, Text, Hierarchy, Hierarchy Node, Formula, Replacement Path and Authorisation Variables;
  • Processing Types for Variables;
  • Customer Exits and SAP Exits.

By the end of this article you will see just how powerful dynamic selections are to build reports that can be used many different users, and make it so that those using the reports have to understand very little about using BEx Reports – because you will be presenting filter interfaces which will make data selection simple.

OLAP Variables

In a previous article, we saw how to select data in a Query by selecting Filter criteria so an output is reduced to only the records that meet our Filter criteria. In order to make reports selections dynamic, OLAP Variables are used, for Characteristics, Texts, Hierarchies and so on. By using them Queries become a whole lot more flexible.

It is important to keep in mind that OLAP Variables are reusable objects. This means that any variable created on any Characteristic can be used in any other report on any other Infoprovider that also includes that Characteristic. So if an OLAP variable was created for ‘Employee’, any other Infoprovider and query that uses this field would also be able to access the OLAP Variable, making it very flexible and helps to cut down the work required when making Variables – creating it once and making it available everywhere else. Because of this, a SAP BW System can contain hundreds if not thousands of Variables that can be made use of.

clip_image002

The screenshot above shows a Characteristic Relation with five ‘Personnel Areas’ fixed in the query. I want to make it so the user can select their preferred ‘Personnel Area’ at the time they run the query. Therefore, they need to be replaced.

Select and remove the pre-determined values, and then right click on the Characteristic. Selecting “Restrict” brings up the Select Values for the Personnel Area field. Select “Variables” from the Show drop-down menu. The system will refresh and show any existing Variables that have been set up for this field. To create a new Variable, click the Create New Variable icon clip_image004 and a window appears which contains various fields which are used to define the Variable.

clip_image006

Even though there are many tabs, only a few options are important at this stage. First, give the Variable a Description and a Technical Name. The Global Setting section includes a drop-down to change the Processing nature of the Variable; the default is Manual Input/Default Value, but there are others which will be covered later. The Reference Characteristic should be set to the one you wish to base the Variable on.

clip_image008

The Details tab is where we specify how the selection option appears. Variable Represents gives several options of how the user can interface with the Variable (the choices are Single Value, Multiple Single Values, Interval, Selection Option and Precalculated Value Set).

The most flexible of these is the Selection Option; if you are familiar with SAP selections which give ‘From’ and ‘To’ boxes, with the option to add more values including Single Values and Inclusions/Exclusions, then you will recognise this option. This section will focus on the Single Value option to show what effect making a different selection type has on the Variable.

There is also an option to determine whether using the Variable Is mandatory or not, forcing the user to fill it in. (There is also an option to make the Variable Mandatory, Initial Value Not Allowed, but this is rare.) To make sure the user can use the Variable, always ensure the Variable Is Ready For Input checkbox is checked; it can sometimes be disabled when creating different types of Variables, such as Customer Exits.

These are the two most important tabs here. As for the others: Personalisation gives the option to make a user’s personalised data, if Personalisation’s are turned on in the System, appear in the Variable. Default Value gives the option to add a pre-existing value as the default value, and there are also tabs for Currency Unit and Extended options.

Clicking OK brings a summary window; click OK again and the new Variable will appear. Move to the right hand window (clicking the blue rightward arrow), click OK once more and the Variable will appear under the Characteristic.

clip_image010

Running the Query produces a selection window, generated from the OLAP Variable created in the previous steps. There is the option of selecting a Variant, which a user can save for the Query, and a Common Variable which the user is required to fill out (this is shown by the ‘(*)’ next to the Description). The drop-down menu will produce recently used values from the History, or a value can be typed in.

clip_image012

When a Variable is set to Single Values, clicking the selection button brings the Select Values window to select a value from either the History or the Single Values list. When a value is selected, it will enter the Key into the selection field; clicking the Check button brings up that Key’s Description.

clip_image014

If the Variable has been set to Multiple Single Values, the Selection icon brings up a different window which allows the dragging and dropping of multiple values. These are represented as multiple Keys divided by semi-colons.

clip_image016

A Variable set to Selection Option allows the selection of Value Ranges as well as Single and Multiple Single Values, as well as the ability to Exclude values.

Once the required value has been selected, clicking OK executes the report.

There are limits to what changes can be made to an existing Variable. The Description can be altered, but the Technical Name is fixed, as is the Variable Represents option under the Details tab. Instead, it may be necessary to create a new Variable altogether.

Some types of Variables cannot co-exist within the same field. For instance, if a Selection Option Variable and a Single Value Variable are both in the same report, it will not execute. On the other hand, Variables can be added for any other field in a Query.

clip_image018

As well as dragging a Characteristic into the Restrictions section and creating a Variable that way, it is possible to search for OLAP Variables within the Infoprovider list, under the Characteristic’s Value Variables folder. Dragging and dropping this instead automatically adds both the Variable and its field.

If a Variable is created by dragging a Characteristic into the Restrictions area, it will not be automatically included either as a Free Characteristic or in the report, and needs to be manually added.

clip_image020

clip_image022

Here is an example of two OLAP Variables within a report. The first Variable, “STHQ_Pers_Area_3” is set to Selection Option, and so several values have been selected. The second, “Nationality” is set to Single Value, and so only the ‘German’ value has been selected. The resulting report is shown below.

I’m sure you’ll agree that the Variables that can be added to Characteristics add another dimension to reports, allowing the user to use dynamic selections instead of hard-coded values, making reports a lot more flexible and easier to use.

Hierarchy Variables

These are used to select hierarchies that can then be used for certain Characteristics.

The hierarchies detailed in one of the previous articles were predetermined by a hierarchy structure which had been setup in BW, taking data from an SAP system. (A Characteristic’s hierarchy is configured in the back-end of a SAP ERP system, and then transferred to BW so it can be reported.)

However, we are not just restricted to one view of the structure of a hierarchy: for instance, with an ‘Organisational Unit’ there would be the choice of the existing view, the view from the year before, a Plan, and so on. We can choose which version of the structure to use in a report by setting up a Hierarchy Variable.

clip_image024

To do this, when choosing a Hierarchy from the tab in the Properties section of a Characteristic, select Hierarchy Variables instead of Name. Click on the button to the right of the drop-down to bring up a window to select a Variable. If there are none available, click Create New, and fill in the fields as when creating an OLAP Variable.

clip_image026

The Default Value can be used here if required; clicking on Change Standard Values allows the choosing of a Hierarchy Name and the version (if multiple versions exist).

clip_image028

Once selected, the Variable’s Technical Name will be visible in the Properties pane (this example: “Z_ORG_HIER”).

clip_image030

After saving and running the Query, the Variable will appear in the Select Values screen (here, as “Org Structure Hierarchy”, with a mandatory * and a default value.) and the desired Hierarchy can be selected from the drop-down menu.

Hierarchy Node Variables

Each level of a hierarchy is called a node. A Variable can be created so that the user can select an ‘Organisational Unit’ using a particular node of the hierarchy.

To do this, after selecting a static Hierarchy Name for the Characteristic you are working on, right click on the field in Characteristic Restrictions and click Restrict. The Select Values window will open. From the Show drop-down, select Variables.

clip_image032

The default view with a normal list of variables should appear; change this by selecting the Type drop-down menu and selecting Hierarchy Node Variables.

You may see one existing Variable in the list, ‘Organisational Unit for a Report User’. This is a standard SAP Variable which uses technical settings (a SAP Exit) and introduces coding aspects of selection variables (which this book does not cover). Instead, click the Create New Variable icon. Give the Variable a Description and Technical Name in the General tab.

clip_image034

Hierarchy Node Variables can represent single values or multiple single values, which allows the selection of multiple levels/nodes of the hierarchy at once.

Once the Variable has been created, click OK and transfer to the Selection section of the Select Values window. Clicking OK will add it to the Query.

clip_image036

When running the Query, clicking the Selection button next to the Variable’s field brings up a Select Values screen with a structural view. (For those working within an SAP system this Organisation structure will be familiar, as it represents the company you are working in.) Either select a level or drill down to find specific values to add, drag them to the Selection side of the window, and click OK. The values will appear in the Variable field as before; clicking OK again refreshes the report.

This type of Variable is a good way of making use of hierarchies within a selection. The user doesn’t need to remember the individual ‘Organisational Units’ when making a selection in a Query – they can make use of the hierarchy, and just by selecting one of the nodes, they can make use of everything underneath.

Replacement Path Variables

This section will discuss Replacement Path processing types for Variables, as well as looking at an example of how to use them within a Query.

A Replacement Path Variable is a simple but powerful feature within the BEx Analyzer and Query Designer. Instead of the user entering a value at run-time to filter the data, these allow automated functionality which replaces values in a selection from a Query with values that are taken from another Query.

Also, values from certain Characteristics and Attributes can be used as Replacements. This is useful for changing the name of a column of data to include a Characteristic or Attribute value, for example: if there was the requirement within a report for one column per calendar month, one for January, one for February and so on. Instead of creating individual columns with fixed names, there is the option to add ‘Number of Employees for January’ followed by some text of the Characteristic as part of the column headings

Let’s look at a scenario where a Replacement Path processing type for a Variable can be used in a query.

clip_image038

The first Query is designed to show the ‘Number of Employees’ organised by ‘Personnel Area’ and then by month. A simple Query, but I want to restrict the number of records to the ‘Personnel Areas’ that have the top 3 hiring numbers. To do this, I have created a second Query which will show the top three ‘Personnel Areas’ with the most amount of employees being hired in the whole year.

So, the way this works: Query 1 is executed, and then because it sees the Replacement Path processing type Variable for ‘Personnel Area’ Characteristic, it executes Query 2 first, to get the values necessary to complete Query 1. Query 2 will bring the record showing the top 3 ‘Personnel Areas’ and from there passes these records to Query 1 which then continues running, presenting the data to the user.

clip_image040

To show how this works, we will start by looking at the two Queries.

clip_image042 clip_image044

Query 1’s Rows and Columns are arranged as above: with Rows for each ‘Personnel Area’ and ‘Subarea’, and then Columns that show the Key Figures for ‘Number of Employees’, divided by ‘Calendar Month’.

Under the Filter tab, we can see Restrictions in force. ’Personnel Subarea’ will exclude any records that are equal to ‘#’ (this will remove any blank records from the report), ‘Calendar Year’ will only include 2004’s records, and ‘Personnel Area’ has a new Variable which uses the Replacement Path Processing type.

clip_image046

Removing the Replacement Path Variable and running the Query produces a report that shows ‘Personnel Areas’ divided by ‘Subareas’, and the ‘Number of Employees’ for each calendar month in the columns, with a Subtotal for each Area which contains more than one Subarea. As it currently stands, the report runs quite long and produces every single Personnel Area – instead, what we want is to filter out all but the top 3 ‘Personnel Areas’ according to the number of employees hired.

In order to create a Variable that uses a Replacement Path Processing type, the object that will be used as the filter needs to already exist – therefore, Query 2 needs to be created before a Replacement Path variable can be created in Query 1.

clip_image048 clip_image050

Query 2 is very simple: there is only ‘Personnel Area’ and ‘Number of Starters’ (which is ‘Number of Employees’, but renamed in the Properties pane) in the Rows and Columns section. The Restrictions for ‘Personnel Subarea’ and ‘Calendar Year’ are identical to Query 1, along with another exclusion to remove blank entries from ‘Personnel Area’.

‘Action Types’, in this case, define the Employee new starters in this Query, using HR terms which restrict the data to when the hiring action has taken place.

clip_image052

Running this Query produces a report with the top three ‘Personnel Areas’ ordered by Number of Starters’. We do not have to pay attention to how this Query looks; the end-user will never see it as it is being used in tandem with Query 1.

clip_image054

This Query was restricted to show only the top three ‘Personnel Areas’ by creating a Condition , which tells the Query to only show the records that have the Top 3 values.

clip_image056

Right clicking on the Condition and selecting Edit from the context menu brings up the Change Condition window, where the parameters are set. These will be explored in more detail later in the book.
Now, having shown Query 1 without a Replacement Path Variable, and ensuring Query 2 is ready, it is time to add the Variable which links the two.

clip_image058

After dragging ‘Personnel Area’ to the Characteristic Restrictions section, and selecting Restrict from the context menu, the Select Values windows will appear. Like the previous Variable types, a new one is created by selecting Variables from the Show drop-down and clicking Create New Variable. Type a Description and Technical Name, and then under Processing By, select Replacement Path.

clip_image060

Then, under the Replacement Path tab, there is an option that specifies that we want to replace the Variable with another Query. Click on the icon next to the Query field and select the required Query. This is all that is needed; none of the other tabs need to be altered. Click OK to finish creating, and drag it to the Selection area of the Select Values window.

clip_image062

With the Replacement Path Variable in place, Query 1 now runs Query 2 to find the Top 3 ‘Personnel Area’s. This data is then passed back to Query 1, which restricts the results accordingly.

Text Variables

These use the Replacement Path processing type again, and are used to apply flexibility to descriptions in field names and report titles. For example, we can change the Report title to include Dynamic Text, or change the headings of columns.

One use for Text Variables is to change the title of a Query, for when you want to replace the default title (which may appear as ‘STHQ_Pers_Area’ and you would prefer something more descriptive) and include some dynamic Variable text which comes from a filter.

The following steps will create a title which will dynamically change according to the ‘Calendar’ year filter in the Query.

clip_image064

clip_image066

Clicking on the Query Properties button in the toolbar brings up the option to change the title in the Properties section. Here, you can change the title and add a Variable. Click the button to the right of the Description field to either Select a Value, or create a new Variable, which brings up the Change Variable screen.

clip_image068

In the General tab, after entering a Description and Technical Name, change Processing By to Replacement Path and select the Characteristic to reference (in this case, ‘Calendar Year’).

clip_image070

For this example, the Replacement Path tab doesn’t need to be altered. The Replace With option would be changed if using an ‘Organisational Unit’ where the Label would be more useful than the Key. Numerical values won’t have these, so leaving as Key will be fine. There is also an option for Offset Start and Length, which determines positioning of the text its formatting. All the other tabs can be left as they are.

Choose OK, confirm and select from the Select Values list. Click OK and the value will be inserted into the Description field, with the Technical Name surrounded by ‘&’ characters. “Personnel Type for &Z_YEAR&” will then be shown as “Personnel Type for [the year]” when running the Query.

One thing to keep in mind at this point is that Text Variables can only work correctly with unique values. With the title example above, the ‘Calendar’ value was being filtered on only one value; if there were more than one, the Variable would not know which value to choose, and the Variable title will appear instead indicating that the filter isn’t unique enough to be used as a specific name.

clip_image072

Another use for Text Variables is to change columns in the same manner. The process for adding a Text Variable to a column is very similar to a title: when a Column Key Figure is selected, there will be a Description field in the Properties section, with the same Variable Selection icon.

A good thing to remember is that wherever this icon is visible, it indicates that Text Variables can be inserted into the field it is next to, throughout the BEx Query Designer.

clip_image074

This Table shows the result of adding a Text Variable to the ‘Number of Employees’ column, here adding the year as filtered in ‘Calendar’ (2004).

clip_image076

Text Variables can also be added to Restricted Key Figures (these will be covered in a separate section). In this case, the Variable can be added within the Change Selection window when creating a Restricted Key Figure.

If a Variable is added in this way, and it contains mixed data, then it will not display the data in the report, just the Variable’s Technical Name. For example, a Variable for ‘Calendar Month’ is added, and a specific month is not filtered, then it will just show the Technical Name instead of ‘January’.

One more thing to note is that drilling across will not bring unique values; the BEx Query Designer and Analyzer cannot dynamically determine a title with restrictions or drilldowns because they don’t form part of the Restricted Key Figure itself.

clip_image078

To work, the Characteristic would have to be within the Restricted Key Figure, which means it will need its own filter that will only be applied to that specific Figure, by Restricting within the Change Selection window. The above window shows filters not only for the ‘Number of Employees’ Key Figure, but also for the ‘Calendar month’, restricting the data to January. This will ensure that the Text Attribute will work correctly.

This has the side-effect of removing the drilldown functionality for monthly data; this would be solved by creating a Restricted Key Figure for each month. Copy and paste, and just change the month for each one.

Formula Variables

These are very similar to Text Variables and can be used wherever there is a numeric input in the Query design, such as a calculated Key Figure, Conditions or Exceptions.

To demonstrate, this section will show a Formula Variable used in a Calculated Key Figure, which itself will be explained further later in the book.

clip_image080

To create a Formula Variable, double-click on a Formula to enter the Change Formula window, and right-click on the Formula Variable folder within the Available Operands section. A new Variable will appear; right-click that and select Edit. As before, enter a Description and Technical Name, and then change Processing By to Replacement Path Variable.

clip_image082

Then, select a Characteristic that has a numerical value. Here, we are choosing ‘Calendar Year/Month’, as this will list months and years as numerical values instead of using text. (Also, this will factor the number of days in a leap year.)

clip_image084

Under the Replacement Path tab, be sure to change the value of Replace With to ‘Attrib Value’. This will allow you to select from the following list of the Characteristic’s Attributes in the field immediately below.

clip_image086

After one has been selected (we will use ‘Number of Days’ here), click OK, and drag the new Formula Variable into the Detail View, where the formula is built. Once happy with the formula, click OK to close the window.

clip_image088

Here, we can see the column with the Formula Variable in action, drilled across by ‘Calendar Month’ – the first month has 31 days, second by 29 and so on. We can then use these figures in a calculation by bringing in another Key Figure, such as ‘Number of Actions’ and then dividing them by the Variable, which could produce an ‘Average Actions per Day’ column.

Authorisation Exit Variables

Suppose you work in a company, and you have responsibility for managing a team of employees in your finance department. There can be many BEx reports to help carry out management work, but because of issues like HR security, permission would be given to see data for only the specific employees each section manages, locking out other departments’ data.

This is where Authorisation Variables come in; when added to a Query the system will carry out authorisation checks on the data the Query is trying to access, displaying only the data that the user has permission to see. Normally, a company’s back-end security team would set these up, and in an SAP implementation there will be security specialists who will set up security rules which will determine who can access what data.

BW makes use of these rules by using Authorisation Exits. To implement these into a Query, the Restrict menu option is used; instead of selecting an individual Value, the Variables section is used. A new Authorisation Variable is created in a similar way to other Variables, except Processing By is set to Authorisation in the General tab.

The other setting to determine is Variable Represents under the Details tab. If there is just one security code, then Single Values is sufficient, although many ‘Organisational Units’ have multiple sections, and so there is also the option for Multiple Single Values or Selection Options.

The SAP security team will have already set up the security rules that relate to the Characteristic that is being referenced by the Variable; usually they are set up once for every user and so creating these is somewhat rare. This also means that creating an Authorisation Variable on its own is not sufficient; the security rules need to be in place for it to be effective.

SAP Exit Variables

These are processing types for Variables that have been delivered by SAP, and have non-standard functionality. These are defined in the Variable list by having ‘(SAP Exit)’ in the name. An example would be a Variable which would automatically select a period of six months and present them as a dynamic selection in the Query, using ABAP code which would determine the selection values, passing along to the Query a filter value for the required time period as a selection.

These can be used just like any other type of Variable, and there are many SAP Exit Variables available, particularly in financial applications.

Customer Exit Variables

Unlike SAP Exits, which cannot be modified and are developed by SAP, Customer Exits give the facility to write custom ABAP code to make complicated selections without input from the user. So, a Customer Exit selection Variable could be created for a ‘Calendar Year/Month’ Characteristic, which would only include this month and last month in the selection range. To do this, a Customer Exit would have to be created.

Creating a Customer Exit is similar to other Variables; under Processing By, select Customer Exit and ensure the right Characteristic is referenced, as well as the preferred type of Variable Representation. Choosing ‘Ready for Input’ gives the opportunity to code some initial selections that will be presented to the user on the screen before the Query runs, so that they can amend the selection made by the code.

clip_image090

After creating the Customer Exit in the BEx Query Designer, it would be accessible to developers using ABAP, and the code would be put into place, filling a structure which would be passed through to the Variable and used for dynamic selection in the Query.

This is an advanced technique that would usually only be used by developers, but it is good to know that the functionality exists, so that Customer Exits could be requested for certain tasks.

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.

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.

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]Amazon.com Link: US Kindle Edition – SAP BEx Analyzer And Query Designer

Amazon.co.uk 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.

Enjoy!