Filter Editor - Example and Overview

The Filter Editor displays filter criteria as a tree structure, where nodes represent simple filter conditions. If the filter criteria consist of multiple filter conditions, the Filter Editor contains multiple nodes linked by logical operators into groups.

For instance, the following image shows the Filter Editor representing the [StoreName]Begins with 'Ace' filter expression:

In the example above, there is only one Group.

In the example below, the filter expression contains two groups combined by the logical OR operator.

Note that both conditions have two parts.

• the StoreName begins with Ace OR
• the CustomerName begins with A

 If you’re technically inclined, the actual expression is closer to this:

• (StartsWith([StoreName],'Ace'), OR

• (StartsWith([CustomerName],'A')

The image below shows the Filter Editor, indicating its UI elements.

• Right-click on a column header and select Show Prefilter (for a pivot-report)                                                                                   
  

  or Filter Editor (for a table-report);                                                                                                                                                         

• Or, in the report's Ribbon, click the Prefilter button (for pivot reports)                                                                                              

  or the Filter Editor button (for table reports).                                                                                                                                         

• 

  If you used the right-click method to open the form, the filter condition uses the column you right-clicked (it’s easy to change). If you used the ribbon, no column will be chosen automatically.
  The screen shots below are from a pivot-report. The filter form was opened by right-clicking on the report’s Company column.

• 
  

  It has the following elements:
  
  

  And	A logical operator like OR, NOT, and others
  Company	A field name in the report’s data set
  Begins with	A comparison operator like equals, less-than, contains, and others
  <enter a value>	A value you enter

• If you’re opening the PivotGrid Prefilter (for pivot reports) or the Filter Editor (for table reports) for the first time, it will have a new filter condition for you to complete.
  

  Otherwise: the form will contain the filter expression that you already created, ready for you to modify as desired.

Building a Filter Expression

• Choose a field name. The field name (shown in blue) is chosen from a list. To use a different field, click the name (in this case, Company). This will display the list of available fields from which you can choose.                                                                
  

• Choose a Comparison Operator. The "Begins with" comparison operator is changed the same way as a field-name selection. If you want some other comparison operator ("contains", "is less than", etc) click the "Begins with", and you will see a list of available operators from which you can choose.                                                                                                                                  

  The list you see will vary depending on the field’s data type. For example, if your field is numeric (for example, “Year”) then the list of comparison operators would not include (for example) “contains”.

• Click "<enter a value>" and provide a value for your filter clause. Depending on the field and comparison operator you’re using, you may need to enter the value directly, or you may be presented with a list of choices based on the data in your report.                                                                                                                                                                                                        

• Choose the logical operator. Click the logical operator (in this case, "And") to display a list from which you can choose.

• To add another logical condition, either:

  1. Click the little add button (the "+" sign) next to the logical operator (in this example, next to the And operator); OR

  2. Click the logical operator itself and choose "Add Condition" from the menu.

• To start a new group: click the logical operator (in this example, the "And") and choose "Add Group" from the menu. See the overview above for an example of grouping.

• Repeat the above steps as needed.

• Click Apply at any time to apply your filter and remain in the filter-editor. Click OK to apply your filter and close the filter-edit.

Your filter expression is shown at the bottom of your report. You can directly enable / disable it, choose a different filter-expression that you’ve used (if available), edit, or delete it.

Example: If you apply following filter expression,                                                                                                           

At the bottom of the report you see (far left and far right, respectively):                                                                    

Filtering Using the Filter Row

You can use a special "Filter row" to enter simple criteria in one or more fields (at most one criterion per field). This row is available in Table Reports only.

To show the filter row:

• Click on Filter Row in the Table Report's ribbon.                                                                                                                                   
  

• A new row appears at top of the table report, with a little "filter" icon at the far left.                                                                      

To filter using the filter row:

• Enter simple terms in the column you want to filter within the filter row.                                                                                         
  

• You can enter filters in as many columns as you wish.
• By default, Mercury matches the start of a value. If you want to match anywhere in the value, begin your filter term with a "%" sign.

  

  Hiding the filter row (by clicking on the Filter Row ribbon button again) *does not remove the filter*. To remove the filter, you can either:
  

  
  

  Blank out your filter terms, one-by-one; OR

  See the next tip.

  You can see (and manipulate) the filter in effect at the bottom of your report.
  
  Example: If your report has a "Class" field, and you enter "USA" in the Class column of the filter row, you would see this at the bottom of your report (one piece at the far left and the other at the far right):
  
  Unchecking the box at the left temporarily disables the filter.
  
  Clicking the pencil on the right opens the Filter Editor (see Building an Expression - the Filter Editor above).
  
  Clicking the "x" on the right clears the filter.