Creating a Query and Including Conditions

Objectives

After completing this lesson, you will be able to:

  • Create a query and include conditions

Conditions

By implementing conditions you can filter the query results based on the values of key figures. For example, you could display only the top five customers for the region, or only products that sold between 100 and 150 units in a week.

By using conditions in your query, you can take advantage of the following features in your report:

  • Analyze combinations of characteristics using ranked lists, for example, by displaying your ten best business partners by sales revenue.

  • Define value limits, so that you can display all key figure values above or below a certain value and view the restricted results in your Analysis report.

  • Define multiple conditions for a query in SAP HANA Studio, and activate or deactivate the conditions in the Analysis report to create different views of the data.

A condition is an object that restricts the view of the data in a query by hiding numbers that do not meet the defined condition or conditions.

By applying conditions to a query, you do not change any figures. You simply hide figures that are not relevant for you. Conditions therefore have no effect on the values displayed in the result rows. The results row of a query with an active condition corresponds to the results row of the query without this condition.

You can define multiple conditions for a query. Conditions are evaluated independently of each other. The result set is therefore independent of the evaluation sequence. The result is the intersection of the individual conditions. Multiple conditions are linked logically with AND. A characteristic value is only displayed if it meets all (active) conditions in the query.

Using conditions, you can restrict how you view query data as follows:

  • Threshold Values: An entry is filtered independently of the other entries if its reference value has a specific relationship to a comparison value. An entry is not displayed for example if its reference value exceeds or is less than a specific threshold value.

  • Ranked List: All entries for the displayed list or all entries for a logical section of the list (with multiple characteristics in the drilldown) are considered. Their relationship to each another determines whether the entry is displayed. For ranked lists, the sorting is switched on automatically when the condition is activated.

Creating a Condition

  1. In BW Query Designer in SAP HANA Studio, choose the Conditions tab.

  2. Right-click in the Conditions screen area to open the context menu, and choose New Condition.

  3. On the Condition Properties screen, create the condition definition.

General Properties

On the General tab, enter a description. Select Condition is Active to use the condition in the report as soon as you execute the query. Alternatively, you can activate the condition in the Analysis report.

Note
The Condition Parameters section of the General tab displays a key figure field (Members on 1st Structure), an operator field, and a value for the operator field. For queries with two structures, there is an additional field (Member on 2nd Structure) for defining a structural component.

Threshold Value Conditions

The following table shows the operators available for threshold value conditions and some examples of their usage.

OperatorExamples
Equal toInvoiced quantity equals 1000
Not equal toLine item not equal to 5
Less thanSales revenue less than 100,000 EUR
Greater thanIncoming orders greater than 1,000,000 EUR
Less than or equal toSales revenue less than or equal to 100,000 EUR
Greater than or equal toIncoming orders greater than or equal to 1,000,000 EUR
BetweenSales from/to, with the option to define these values using a variable request
Not betweenSales from/to excluded from display, with the option of defining these values flexibly at runtime

Ranked List Functions

The following table shows the available operators for ranked list functions and some examples of their usage.

OperatorExample
Top NDisplay the sales revenue of the top five business partners.
Bottom NDisplay the bottom three (lowest) incoming orders.
Top %Restriction to 20% of the best revenue, in order.
Bottom %List of 15% of business partners with the lowest sales revenue, in order.
Top Sum

List the products with the highest sales revenues where the total sales revenue makes up 20,000 EUR. All sales revenues are sorted in descending order and then the sum is calculated until the threshold value of 20,000 EUR is exceeded. All products, including the product that takes the net order amount over the 20,000 EUR mark, are listed.

Bottom Sum

Similar to Top Sum, but a ranked list is sorted in ascending order of products with the weakest sales revenue until a sum greater than or equal to 20,000 EUR is reached. All products, including the product that takes the sum over the 20,000 EUR mark are listed.

Characteristic Assignments

On the Assignments tab, define how the condition will work with the characteristics in your query. If the query contains many drilldown characteristics, the condition may be out of context and produce invalid results.

For the characteristic assignments, you have the following options:

All Characteristics in Drilldown Independent

This setting allows you to apply a condition in a general way. Depending on which characteristics you use in the drilldown, you obtain different results. This option is optimized for ranked list conditions, but can also be used for threshold value conditions with relative values.

Note
Characteristics that are already used in an active condition for characteristic combinations are not included.
Most Detailed Along Rows

This option is optimized for threshold value conditions. The condition is applied to the most detailed characteristic of the rows.

Most Detailed Along Columns

This option is optimized for threshold value conditions. The condition is applied to the most detailed characteristic of the columns.

Individual: Select Individual Characteristics

This setting allows you to evaluate the condition for characteristics or characteristic combinations defined for specific drilldowns only. You can select any characteristic (of the characteristics used in the query in rows, columns, or in the free characteristics) or a characteristic combination.

If you only want to evaluate the condition when a certain characteristic is present in the drilldown, select this characteristic.

If you want to evaluate the condition for characteristic combinations, select multiple characteristics.

Multiple Conditions in a Report

If you are using multiple conditions in one report, conflicts may arise that lead to automatic deactivation.

There are two reasons why conditions cannot be activated in a report:

  • Not applicable

    The condition relates to a characteristic or characteristic combination that is not included in the drilldown.

  • Conflict

    The condition relates to a characteristic that is drilled down using an active display hierarchy.

    The condition relates to a characteristic that is included through the activation of another condition, leading to a collision.

Define Conditions in Queries

In the following demonstration, you learn how to define Conditions in order to filter the report with the help of threshold values.

SAP Analysis for Microsoft Office: Filter by Measure and BEx Conditions

Note
Conditions created in BW Query Designer in SAP HANA Studio are called BEx Conditions in SAP Analysis for Microsoft Office.

In SAP Analysis for Microsoft Office, you can:

  • Define Filter by Measure at workbook level
  • Activate and deactivate BEx Conditions, but they cannot be edited.
Note
Only the values which fulfill both the Filter by Measure condition and all activated BEx Conditions are displayed in the report.

Log in to track your progress & complete quizzes