SAP System Setup Training Course
Reveals How To Setup A Free SAP Developer System.
Learn SAP & Practice For Certification. Developer Tips Included:

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.

Categories

About the Author:

Pete has been working with SAP technologies for over 10 years. He started out as an ABAP consultant and then moved on to BW where he has worked many different clients covering a wide variety of industries. "I love introducing SAP technology (especially BI) to new clients and showing them how they can go from zero to hero within their business in super fast time". Contact me on twitter @PeterMoxon

15 Comments
  1. Hi Guy,

    This sounds like an awful task. I’d query the requirement myself. I’ve never seen a requirement for do so many at once.

    As for actually doing it, I am sure there is some way to reduce the effort involved but I’ve only ever done it manually myself.

  2. Sebastián Vinaya

    Hi Peter, Do you know how i do a text filter on BEx query? I need to filter all the members who have in the text, the word “Issue” (I consider that in the future may appear new members). I understand that I must use a customer exit variable but do not know how! Thanks

    • Peter Moxon

      You should be able to use wildcard searches in a normal selection, but may be I haven’t understood your question correctly.

      • Sebastián Vinaya

        Thanks Peter for your response. I’ll try to explain better. In BEx one can make a search result and then apply it as a filter (being as a static filter). For example, if today the result of that search are 5 records of the characteristic, it will remain those 5 codes filtered in the query. What I need to do is that if tomorrow appears a sixth record (that belong to the condition), it will be filtered automatically (without the need to modify the query). There is possibility to do this?
        Thanks

        • Peter Moxon

          Ah, I understand. Yes it is possible.

          This article shows you how to do it. Read from the sub-title: Replacement Path Variables.

          You basically create QUERY 1 that contains a filter to output the records you are interested in. Make sure you include the characteristic(s) that uniquely identify each record in the query results. For this example let just assume there is just 1 characteristic called MYID.

          Then create QUERY 2. In query 2 create a new Replacement Path variable for Characteristic MYID that uses QUERY 1 results as the filter.

          Every time you run QUERY 2 it will run QUERY 1 in the background to make sure the results of QUERY 1 are past through to QUERY 2 at the time QUERY 2 is executed. this means QUERY 2 will always produce the correct output because the filter criteria is update in real-time.

          • Sebastián Vinaya

            Peter, In the QUERY 1, I need a “like” or match pattern behaivor to filter those records in a characteristic where the text contains the word “Issue” (today can be 5, but tomorrow may be 6 or more). The problem is that the the search can be done for text, but not filter for text. Regards!

          • Sebastián Vinaya

            Only in the search you can use match pattern *Issue* on the text , then the result (the technical name) can be add to the filter, but this is the problem (I just have a static filter). 🙁

          • dogbert69

            Peter, how to prevent all data showing when input selection field is left blank in the selection screen? We are using Analysis auths with values for 4 brands, we have 4 Analysis auths restricting each in say brand 1-4. When the selection screen has a value the correct brand data comes back but when that field is left blank, we get all data returned and we don’t want that.

  3. gorazdf

    Hello, I have a question regarding Query views. We are on sap bw SAP NetWeaver BI 7.0. Most of queries use UserExit variables to show current month or current period. And our users would like to save query views that would not also save these time variables at the time of saving query view. Is this possible? Also the same question applies for broadcasting query views, also would like to have UE variables work wothout saving current values?

    Thank you kindly

    Gorazd

    • Peter Moxon

      A query view is a ‘view’ at a given point in time. As long as you do not refresh the query data you will be able to see the snap-shot (view) of the query when it was run.
      You could just save the workbook on your network somewhere instead of saving on your BW system… as long as this is allowed in your organisation.
      It is best to speak with your BI development team to work out the best solution. Anything is possible, it’s just a case of explaining your requirements and getting them to act.
      (I know… easier said than done, right? 🙂 )

      • Pratyaksh Jeet

        Hi peter,

        In my case user need a default date ( mandatory Value range) in variable screen in which it default show the dates sy-datum-7 to sy-datum. is this possible to do in query level or need to do from customer exits something . please suggest.

        Thanks

        Pratyaksh Jeet