About Advanced Filters for Reports

Updated by Cheryl Wallace

The Advanced Filters tab is used to

  • build and apply a filter to a report
  • save a filter to be reused on a report
  • retrieve a saved filter to apply to a report

Advanced Filters can range from a condition on a single field to a set of compound filters on multiple fields using connectors to define how those filters work together.

Saved Filters

Select from previously saved advanced filters to display in the Filter: section below. Once a saved filter is loaded, it can be edited, copied, or deleted.

Saved filters are especially useful in saving time when the same report is run regularly and has one or more conditions such as an Issues report where the Employer is REPAIR.

Advanced Filter toolbar

Save the conditions entered in the Filter section below and assign a descriptive name for later retrieval to use on reports in future.

Create a duplicate of the conditions entered in the Filter section below and assign a different descriptive name for later retrieval to use on reports in future.

Clear the conditions entered in the Filter section below.

Delete the saved filter entered in the Filter section below. This saved filter is permanently deleted.

Filter:

This area is used to build new filters and display or edit saved filters. Constructing an advanced filter is similar to writing a sentence. As each building block of the filter criteria is added in the columns and text boxes below, the "sentence" created appears to the right of Filter:

  • The first column contains a field name against which to filter.
  • The second column contains the comparison operator describing how the field name will be filtered. If comparing against some other value either the the third column or fourth column is required. When this is not a True / False, Is Null / Is Not Null, nothing further is required.
  • The third column is the comparison value against which the field is being compared.
  • The fourth column is a comparison field name against the first column is being compare.

Understanding Comparison Operators

Comparison operators are used to define how to filter or compare the selected column or field. When using comparison operators requiring a comparison value, use either a value (alphanumeric or number) or a comparison field, not both.

Relative dates ('this year', 'today', 'last week', etc) are supported for most date comparisons. Use Advanced Filters to ensure the dates are in relation to the execution of the report, not the date the report was created, when scheduling reports.

<

Represents "less than", applies to text or numbers. Comparison values can be a value entered in the third column.

<=

Represents "less than or equal to", applies to text or numbers. Comparison values can be a value entered in the third column.

<>

Represents "does not equal", applies to text or numbers. Comparison values can be a value entered in the third column or a field in the fourth column.

=

Represents an exact match for text or numbers. Comparison values can be a value entered in the third column or a field in the fourth column.

>

Represents "greater than", applies to text or numbers. Comparison values can be a value entered in the third column.

>=

Represents "greater than or equal to", applies to text or numbers. Comparison values can be a value entered in the third column.

IN

Represents "in a list of values". Comparison values can only be multiple text or numeric values separated by commas entered in the third column. Text values must be surrounded by single quotes.

IS NOT NULL

Looks for fields that are not blank or otherwise not 0. No additional parameters to the filter are required.

IS NULL

Looks for blank or non-initialized values (not necessarily 0). No additional parameters to the filter are required.

CONTAINS

Looks for a partial or exact match of the text entered. Comparison values can only be text or numeric values entered in the third column.

NOT IN

Represents "not found in a list of values". Comparison values can only be multiple text or numeric values separated by commas entered in the third column. Text values must be surrounded by single quotes.

DOES NOT CONTAIN

Looks for fields where the entered text is not found. Comparison values can only be text or numeric values entered in the third column.

Building a Filter Condition

Constructing an advanced filter is similar to writing a sentence. Below are examples of possible advanced filters if they were written as a sentence and how they are created as advanced filters.

The Employer contains ACME.

Column 1: Employer Employer

Column 2: CONTAINS

Column 3: ACME

The Category is either PPE or SAFETY.

Column 1: Category Category

Column 2: IN

Column 3: 'PPE','SAFTETY'

The Item's Unit Cost is greater than its Revenue to Date

Column 1: InventoryItem Cost

Column 2: >

Column 3: [do not use]

Column 4: InventoryItem Revenue to Date

Part Number is a kit.

Column 1: Inventory Kit

Column 2: IS TRUE

Using multiple Filter Conditions together

Two connectors are available to join multiple conditions:

  • AND requires that both filter conditions be true to include the data on the report.
  • OR requires that either filter condition be true to include the data on the report. Use an OR connector when the filter conditions are mutually exclusive. Example: The manufacturer is either Hilti or Mitsubishi. It cannot be both.

Any filters entered on this tab work in conjunction with any quick Filters.
See this video for an overview difference between Quick and Advanced Filters and walkthroughs of multiple examples.

See also:

​Copyright © 2020-2023 ToolHound Inc. All Rights Reserved.

How did we do?