Jack Wiebe

Modified on: Tue, 18 Sep, 2018 at 11:41 AM

Filtering a Report

Filtering can reduce how much data is included on a report by selecting specific rows based on user-defined criteria.

A practical example of this would be to filter on the employee date-of-birth with a criteria that the date must be before 1990. When this filtering is applied to the report, only rows from employees born before 1990 will be included in the report.

Filtering removes whole rows from the report, not parts of the rows. What this means is that if the following row was filtered on the date of birth being before 1990, the entire row would be removed from the report, not just the date of birth.

Example row—employee name, date of birth, and social insurance number.
Employee Name	Date of Birth	SIN
John Grey	8/11/1995	* * ***

Within the Report Definition there are three kinds of filtering. Each of these filters reduce the amount of information included on a report in a specific way:

Selection filters: Selection filters select which employees will be included in the report (for example Pay Group, Location, or Employee Number can be used to create selection filters).
Selection filters are the most general type of filtering.
Data filters: Data filters removes records using user-defined filter rules.
Criteria filters: Criteria filters are available only on some data-sources. Criteria allow you to select exactly which data to include. The Earning Code and Deduction Code data-sources both use criteria to determine which codes to include on the report.

Data Filter Example

We'll go over how to apply a data-filter on an employee rate-of-pay report to display only those employees who make more than $50 dollars an hour.

Open the Data Filters tab of the Report Definition.

Open the column Filter Definition by selecting the Column Filter layout-source then pressing Modify.

Layout Sources:

We use the Column Filters layout source because the data we want to filter already exists in a column of the report.

Alternatively, you can create data filters on fields from the Employee Profile (Employee Profile data-fields can be used even if the field is not included as a column in the report).

Figure 1. Filter Definitions can be edited by selecting the Layout Source then pressing Modify.

Tip:

You can also double-click on Column Filters to open the column Filter Definition window.

Filter Definition

Filter definitions are built-up using Filter Lines, and joining each of those Filter Lines together using a conjunction. For example you may want to filter on the employee age and the employee rate of pay. You would achieve this by creating two Filter Lines and joining them with the conjunction And.

Combining Filter Lines

When you use more than one Filter Line you must choose a conjunction to use when combining them.

Conjunctions

Or: Values will be included if they match the first filter, or the second filter, or both filters.
And: Values will be included only if they match both filters. (This can be thought-of as only the overlapping regions below will be included.)

Let's go over the steps to create the filter definition for the employee hourly rate of pay now.

Add a new Filter Line to the Filter Definition by pressing Insert.
Select which column to filter from the Field drop-down. (In this case we want to select the Base Hourly Rate column.)
Select which Condition to use from the drop-down. (We selected the greater-than (>) condition because we only want employees that make more than $50 an hour.)
Enter 50 into the Value field.
Add the Filter Line to the Filter Definition by pressing OK.

At this point you can close the Filter Definition and run the report to test out the filter.

By selecting a Field, Condition, and Value we have created a user-defined test for the Base Hourly Rate that checks if the hourly-rate is greater than $50.00. Only those employees with hourly rates that are greater than $50 will be included on the report now.

Figure 2. Field, Condition, and Value can be thought an expression that is read like this: if Base Employee Rate (Field) is greater-than (Condition) $50 (Value) then include this row in the report.

Saving filters for later use

From the file menu select Save As to save the filter and use it in another Report Definition later. (Save As will create a new Filter Definition, and Save will save any changes to an existing Filter Definition.

Figure 3. Save the Filter Definition by selecting Save As from the file menu.

1	From the File menu select Save As.
2	Enter the Description to save the filter with.
3	Press OK to save the filter.

Loading filters

Later, you can load saved filters from the File menu of the Filter Definition and selecting Open.

Result

Now when the employee rate-of-pay report is run, only the employees who make more than $50 per hour will be displayed.

Figure 4. Report ran without filtering (left), then again with filtering on (right).