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.
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.
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.
The 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.
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
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
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
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
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
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
@
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.
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
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.
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.
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.
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
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.
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.)
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.
Refresh 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.
Change 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.
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.
· 
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.
Global 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.
System 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.
Help 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
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.
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.
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.
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.
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.
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.
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.
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:
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).
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.
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.
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.
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.
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.
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.
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.
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.
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.
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.
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.
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.
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.
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.
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.
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.
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
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.
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.
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.
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.
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’.
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.
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.
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
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.
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.
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.
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
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.
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
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.
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.
 |
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’).
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.
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
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.
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.