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
In BW Query Designer in SAP HANA Studio, choose the Conditions tab.
Right-click in the Conditions screen area to open the context menu, and choose New Condition.
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.
| Operator | Examples |
|---|---|
| Equal to | Invoiced quantity equals 1000 |
| Not equal to | Line item not equal to 5 |
| Less than | Sales revenue less than 100,000 EUR |
| Greater than | Incoming orders greater than 1,000,000 EUR |
| Less than or equal to | Sales revenue less than or equal to 100,000 EUR |
| Greater than or equal to | Incoming orders greater than or equal to 1,000,000 EUR |
| Between | Sales from/to, with the option to define these values using a variable request |
| Not between | Sales 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.
| Operator | Example |
|---|---|
| Top N | Display the sales revenue of the top five business partners. |
| Bottom N | Display 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.
