End-User Capabilities: Filter Editor - Building Filter Criteria

• Jan 23, 2019
• 6 minutes to read

The filter editor allows an end-user to filter data (display those records that meet specific requirements) by visually constructing filter criteria in a straightforward graphical form. Although, an end-user can build both simple and complex filter criteria using the Filter Editor, the main destination of the Filter Editor is creating and representing complex filter criteria, consisting of multiple conditions. Simple filter conditions can easily be created using columns’ filter dropdown lists or the custom filter dialog.

The following sections demonstrate how to construct filter criteria for a grid control using the Filter Editor.

How to Construct a Simple Filter Condition

Filter conditions specify what data to select from a data source and display in a grid control. A typical simple filter condition consists of three parts: the column/field name, operator and value(s). For instance, ‘[Discount] >= 0.05’ is a simple filter condition where ‘[Discount]’ is a field name, ‘>=’ is an operator and ‘0.05’ is a value. When applied to the grid control, this condition will select records that have values in the Discount column greater than or equal to 0.05. Here is how to create this condition via the Filter Editor (it is assumed that the grid control contains the Discount column, otherwise this column will not be accessible in the Filter Editor’s column list):

1. Open the Filter Editor (for instance, by right-clicking any column’s header and selecting the Filter Editor option).

   

2. The Filter Editor will pop up. If no filtering has yet been applied, the Filter Editor will contain a new filter condition referring to the clicked column. If, say, the Filter Editor has been opened by right-clicking a Product Name column, it will look like the image below.

   

3. To filter against the Discount column, click the condition’s link displaying a column name (‘Product Name’). This will display the list of available columns. Select the Discount column in this list.

   

4. To select the ‘>=’ comparison operator, click the condition’s operator link (‘Equals’) to display the list of supported comparison operators and select the required operator.

   

   The comparison operator list displays only those operators that are supported by the current column’s data type. For instance, the Discount column is of the numeric type and the operator list doesn’t display the ‘Begins with’ operator and other operators that are related to the string type.

5. Now, click the value box and enter a comparison value (‘0.05’).

   

6. Click OK or Apply to filter data using the created filter condition. The grid will show the filter panel displaying the current filter criteria.

   

   The filter panel will contain the ‘Edit Filter’ button, which also allows an end-user to invoke the Filter Editor.

How to Construct Complex Filter Criteria With One Logical Operator

Filter criteria typically consist of two or more simple filter conditions combined by logical operators (AND, OR, NOT AND, NOT OR). The following example shows how to construct filter criteria in the Filter Editor that consist of multiple conditions combined by one logical operator. The “[ProductName] = ‘Tofu’ AND [Discount] >= 0.1 AND [Quantity] > 99” filter expression contains three simple filter conditions combined by the AND operator. To construct it, do the following.

1. Invoke the Filter Editor by right-clicking the Product Name column’s header and selecting the Filter Editor option. The Filter Editor will display an unfinished new filter condition referring to the clicked Product Name column.

   

2. Set the condition’s operator to Equals and operand value to ‘Tofu’ (as described in the previous section).

   

3. To add a second condition (Discount] >= 0.1), press the button next to the group’s AND operator.

   

   This will create a new condition below the current one.

   

4. For the second condition, set the column to ‘Discount’, operator to ‘>=’ and operand value to ‘0.1’.

   

5. To add a third condition ([Quantity] > 99) to the same group, click the button again. Set the condition’s column to ‘Quantity’, operator to ‘>’ and operand value to ‘99’. Below is the result.

   

6. Click OK or Apply to apply the created filter criteria.

How to Construct Filter Criteria Consisting of Multiple Different Logical Operators

Some filter criteria contain multiple logical (Boolean) operators combining simple filter conditions. To build such criteria via the Filter Editor, first, you need to identify groups of filter conditions. A filter group is a set of simple filter conditions or other groups combined by the same logical operator. You can think of groups as clauses in a filter expression wrapped by round brackets. Consider the filter criteria:

[UnitPrice] = 10 AND [Product Name] Begins with ‘A’ OR [UnitPrice] = 20 AND [Product Name] Begins with ‘B’ OR [UnitPice] > 100.

In this expression, we’ll identify groups by wrapping them with round brackets as follows:

([UnitPrice] = 10 AND [Product Name] Begins with ‘A’) OR ([UnitPrice] = 20 AND [Product Name] Begins with ‘B’) OR [UnitPice] > 100.

Here you see three groups of filter conditions. Within each group, filter conditions are combined by the same logical operator:

1. ([UnitPrice] = 10 AND [Product Name] Begins with ‘A’)
2. ([UnitPrice] = 20 AND [Product Name] Begins with ‘B’)
3. [UnitPice] > 100. This group consists of a single simple filter condition.

The three groups are combined by the same OR operator. After filter groups have been identified, the filter expression can be easily built using the Filter Editor.

The following example shows how to construct the following filter criteria using the Filter Editor:

“([UnitPrice] < 10 AND [Quantity] < 10) OR ([UnitPrice] > 10 AND [Quantity] > 10)”.

This expression contains two groups of filter conditions combined by the OR operator. In each group, filter conditions are combined by the AND operator.

1. Invoke the Filter Editor by right-clicking a column’s header and selecting the Filter Editor option.

2. Clear existing filter conditions (if any) by clicking the button.

   

3. Change the root logical operator to OR. To do this, click the current AND operator and select OR.

   

4. Add a new filter condition group by clicking the OR operator and selecting Add Group.

   

5. For the created condition, set the column to ‘UnitPrice’, operator to ‘<’ and operand value to ‘10’:

   

6. Click the button to add a new condition to the current group.

   

7. For the new condition, set the column to ‘Quantity’, operator to ‘<’ and operand value to ‘10’.

   

8. Add a new filter condition group. To do this, click the root OR operator and select Add Group.

   

9. For the condition within the created group, set the column to ‘UnitPrice’, operator to ‘>’ and operand value to ‘10’:

   

10. Click the button to add a new condition to the new group.

    

11. For the new condition, set the column to ‘Quantity’, operator to ‘>’ and operand value to ‘10’.

    

12. Click OK or Apply to apply the created filter criteria.