Applying Filters

Filters can be applied as fixed values (such as always selecting records of a certain status), or as runtime values, where the user selects the value when scheduling the report to run (such as choosing which types of Audit to include in that particular run of the report.)

The different levels of filtering allows for flexible, efficient, and intuitive report selection criteria to be designed. The filters are applied cumulatively: any Data Source filters AND any Report Design filters AND any Report Schedule filters.

Filters are applied in the Filters page of the Report Data Source, Report Design, and Report Schedule records. Report designs also show any filters that have been applied at the data source level; report schedules show any filters that have been applied at the data source or report design level.

Each row in the table applies a single condition. For example, if the filter is to include Audits with a status of In Progress or Scheduled, the table will contain two rows:

(Status Equals In Progress) OR (Status Equals Scheduled)

To apply the same search for three named suppliers, the table would contain five rows:

((Supplier Equals ABC Supplies) OR (Supplier Equals DEF Supplies) OR (Supplier Equals XYZ Supplies)) AND (Status Equals In Progress) OR (Status Equals Scheduled)

To add a filter to a data source:

1. Open the data source in edit mode and navigate to the Filter page.

   Figure 3-4 Filters Table

   

2. To add a row to the filters table, click Add.

3. Enter the details.

Table 3-2 Filters Table

Details	Description
( ) parenthesis	The parenthesis columns are used to construct simple or complex statements, with and/or logic. Opening and closing pairs must match.
Column	Select from the available columns, based on the data source's query.
Operator	Select from the following options, based on the column type: If the column type is Boolean, the options are: Equals; Is Not Equal To. If the column type is Date, the options are: Blank; Not Blank; Equals; Is Not Equal To; Less Than; Greater Than; Less Than or Equal To; Greater Than or Equal To. If the column type is Entity, the options are: Blank; Not Blank; Equals; Is Not Equal To. If the column type is Number, the options are: Blank; Not Blank; Equals; Is Not Equal To; Less Than; Greater Than Less Than or Equal To; Greater Than or Equal To. If the column type is String, the options are: Contains; Starts With; Ends With; Blank; Not Blank; Equals; Is Not Equal To; List Contains; List Does Not Contain; Does Not Contain; Does Not Start With; Does Not End With. If the column is selected from a glossary. Rather than having to specify a separate filter clause for each glossary item, multiple glossary items can be covered by a single text-based search operator using the Contains Text and Does Not Contain Text operators. They are available for use by the following fields: Activity Name Site Activity Name Supplier Activity Name The operators allow a text string to be applied as a wildcard comparison. For example ‘nut’ to return matches on records that contain the word nut anywhere in their description (such as nut; nuts; walnut; walnuts; chopped walnuts; water chestnut; and so on.) Thus a single clause can be used instead of having to specify one for each possible record. If multiple clauses are specified to search for multiple strings, such as ‘nut’ and ‘milk’ together, they are applied as OR logic, regardless of whether AND or OR has been specified on the clause. For example searching for Contains Text ‘nut’ and Contains Text ‘milk’ will return results that contain either ‘nut’ or ‘milk’. When used for versioned glossaries, the current and all previous versions of the glossary ID will be included. The search is also applied to translations for active locales. For example, if the description ‘milk’ has a French translation of ‘lait’, applying the search with either ‘milk’ or ‘lait’ as the text string will return results. Note: The Editable Text Selector type glossaries, where variable inline values are entered into a predefined statement structure, cannot be used as filters. In this case, the only option for the Operator will be Blank, and the Value will contain the text Not available for this type of glossary. However, they do have corresponding text fields which contain the full constructed statement for reporting and filtering purposes.
Type	Select from the following options: Fixed - the value to be applied to the expression is a fixed value. This is the default value. Relative - this option is available if the selected column is a date. This allows the date value to be applied to the expression relative to the date the report is run, rather than as a fixed date. For examples of usage, see Table 3-3.
Runtime Value	Check the box if the value is to be entered by the user when the report is scheduled. Otherwise, enter a fixed value in the value column. The box is unchecked by default.
Ignore Case	Check the box if the comparison value does not need to be case-sensitive. The box is checked by default; it only shows if the column is a text field.
Value	Enter the value to be applied by the operator. If the column is a lookup to a glossary, or an enumeration, use the picker to select a single value from the available options (see Figure 3-5). Options to filter and search the list appear at the top of the dialog box. Click Search to apply any selections. The paged list of available values shows the item description and code, along with its status in the glossary. For enumerations (lists of keywords), a simple drop-down picker is used. For dates, a date picker is used. When the report is run, the time-zone of the user who scheduled the report is assumed. This field is disabled if the runtime value box is checked; it is hidden if the type is Relative and the unit is Run Date.
Unit	If the type is Relative and is a fixed value (not a runtime value), select from the options: Run Date; Day; Week; Month; Year. This field is hidden unless the type is Relative; if the runtime value box is checked, the field is disabled.

Figure 3-5 Filter Selector

Table 3-3 Examples of Filters

Filter	Column	Operator	Type	Runtime Value	Ignore Case	Value	Unit
Created today	Creation Date	Equals	Relative	N	-	-	Run Date
Created in the last x days	Creation Date	Less Than or Equal To	Relative	Y	-	0	Day
	and Creation Date	Greater Than or Equal To	Relative	Y	-	[runtime value]	Day
Created in the last x	Creation Date	Less Than or Equal To	Relative	Y	-	0	[runtime value]
	and Creation Date	Greater Than or Equal To	Relative	Y	-	[runtime value]	[runtime value]
Not created this year	Creation Date	Not Equals	Relative	N	-	0	Year
Approved in the last week	Approval Date	Less Than or Equal To	Relative	N	-	0	Week
	and Approval Date	Greater Than or Equal To	Relative	N	-	-1	Week
Due in the next 2 months	Due Date	Greater Than or Equal To	Relative	N	-	0	Month
	and Due Date	Less Than or Equal To	Relative	N	-	2	Month
Due last month or next month	Due Date	Greater Than or Equals OR Less Than or Equals	Relative	N	-	1	Month
List of dates includes the run date	List of Dates	Contains	Relative	N	-	-	Run Date