Configuring filters in reports

In the Reports app, you can define which data sets are displayed in a report using filters. You can filter on field values, logical expressions, or aggregate values.

How it works

Filter types

There are two types of filters you can define:

Type	Description	Usage	Example
User Prompt	Report readers can specify filter values when they view the report	when you need to share the report with a broad audience that may be interested in different subset of results	You want your report readers to be able to filter issues data by different projects. To do this, you: add the Project Name field to the Filters list. define the filter type as User Prompt.
Define Value	Report Admins and Report Writers can specify filter values during the report creation process so the report is always restricted to the same result subset	when you need to present a specific subset of data	You want your report readers to view issue information pertaining to the IT General Controls project: To do this, you: add the Project Name field to the Filters list. define the filter type as Define Value, and specify the value as IT General Controls.

Filter logic

You can apply any combination of filters to a report to display a specific subset of data.

When you add more than one filter to a report, the report displays data that matches all of the filters that you apply. In other words, filters are automatically joined using AND logic to decide what data is included in a report. You can apply filter logic to specify whether a report displays data that matches all filters or some filters.

Filter formatting

Once you have added a filter to a report, the Filter Formatting option displays in the formatting toolbar of Report Builder. Filter Formatting allows you to configure the display of filters in the report.

For more information, see Formatting filters in reports.

Retrieving data from projects and collections

If you need to retrieve data from a single project or a specific set of projects, you can filter your report by the following fields:

• Project Name
• Project Folder
• Project Type
• Report Opinion

If you need to retrieve data from a single collection or a specific set of collections, you can filter your report by Collection Name.

Generating reports faster

To avoid errors caused by processing too much data at once, and to reduce the likelihood that the report will fail to generate due to containing too much data, see Using filters to generate reports faster for a list of fields you can use to filter the data going into your report. The available fields vary depending on the View you're using.

Permissions

Only Report Admins and Report Writers can configure filters in reports.

Add a filter to a report

1. Open the Reports app.

   The Browse page opens.

2. Open a report and navigate to the Data tab in Report Builder.
3. Locate the field you want to include as a filter in the report by doing any of the following:
   • Enter a keyword in the Search field.
   • Expand the folders to navigate to the appropriate field:
     • Dimension attributes of your data that describe records, such as Likelihood or Severity.
     • Metric values to be measured in the report, such as Cost Impact or Project Budget.
     • Date represent date values, such as Control Created or Control Updated.

       For more information, see Creating reports.

     Tip

     You can Ctrl+click to select multiple non-adjacent fields, or Shift+click to select multiple adjacent fields from the same category.

4. Drag the field from the data fields list to Filters list.
   Note

   You can create filters that combine multiple values, such as using the Project Name filter and the In List logical operator.

   Alternatively, you can also accomplish the same task by setting up multiple filters, such as: Project Name = value1 OR Project Name = value2.

   • If the filter values are found in two different fields, you need to drag both fields into the Filters list.
   • If the filter values are found in the same field, such as in the example above, you need to drag the field into the Filters list twice (once for each value).
5. Optional. To reorder fields, select the field in the Filters list and drag it to a new position in the list.

Apply an aggregation function to a filter

1. Hover your mouse over a field in the Filters list
2. Click the down arrow , select Aggregation, and select the appropriate aggregation function:

   The aggregation function icon displays beside the field, indicating that the aggregation has been applied to the field.

   Note

   Dimension fields are limited to Count and Count Distinct aggregations. Metric fields have the full range of aggregation functions available to them. Date fields are limited to Count, Count Distinct, Max, and Min aggregations.

   Aggregation function	Description
   None	clears the aggregation applied
   Average	returns the average value in the field
   Count	returns the number of values in the field
   Count Distinct	returns a count of unique values in the field
   Maximum	returns the highest value in the field
   Minimum	returns the lowest value in the field
   Sum	returns the addition of values in the field

Apply conditional logic

1. Under the Filters list, click Advanced settings.

   The Configure Filters window opens.

2. For each filter in the list, click the down arrow and select the appropriate logical operator from the dropdown list.

   For more information, see Logical operators.

3. Complete any of the following actions and click the small x to close the Configure Filters window:
   • To apply conditional logic to combine filters, select And or Or from the dropdown lists between a pair of filters.
   • To define nested boolean logic, click the arrows to add brackets around sets of filters.
   • To reorder filters, apply an aggregation function, or delete a filter, hover your mouse over a field name, click the down arrow , and select the appropriate option.
   • To manually define a value for the filter condition, or set the value for the report reader to define, click Define Value beside the appropriate filter.
   • To search for and select values that currently exist in the field, click the filter icon beside the appropriate filter.
     Note

     It may take a few moments for the dialog box to display with the list of available values. To avoid multiple dialog boxes displaying as popups, only click the filter icon once.

   • To add additional fields as filters in the report, drag the field(s) to the Drag filter fields here box.

   The filter logic syntax updates dynamically as you configure the filters.

Define the filter type

1. Hover your mouse over the appropriate field in the Filters list.
2. Click the down arrow  beside the field name, select Value, and select one of the following options:
   • User Prompt prompts the report reader to provide filter values when they view the report
   • Define Value sets the filter value during the report creation process so the report is always restricted to the same result subset
   • Search Values allows you to search and select filter values from a list, which then become defined values
     Note

     The Search Values option only applies to dimension fields.

3. If you selected Define Value, choose one of the following options in the Select Filter Value dialog box, and click Submit:
   • User Prompt prompts the report reader to provide filter values when they view the report
   • Defined Value sets the filter value during the report creation process so the report is always restricted to the same result subset
4. If you selected Search Values, in the Search Filter Values dialog box, search for filter values and select the appropriate filter values from the list, or use the Select All and Sort options to sort filter values alphabetically, select all filter values, or deselect all filter values, and click Submit.

Remove a filter

1. Select the field in the Filters list.
2. Drag the field back to the data fields list.

Logical operators

You can apply a variety of different logical operators to filters. The logical operators available depends on the data type of the field.

General filtering

Logical operator	Result returned if...
Equal to	field value is equal to the filter value
Greater than	field value is greater than the filter value
Greater than or equal to	field value is greater than or equal to the filter value
Less than	field value is less than the filter value
Less than or equal to	field value is less than or equal to the filter value
Different from	field value is not equal to the filter value
Between	field value is within the bounds of the two filter values Note Between is inclusive. For example, defining "between 1 and 3" returns the following values: 1, 2, and 3.
Not Between	Note This logical operator is currently not supported.
In List	field value is equal to any of the filter values (filter values are provided in a list)
Not in List	field value is not equal to any of the filter values (filter values are provided in a list)
Is Null	there is no value in the field Note Some fields may return an empty string, which is different from null. Alphabetical sorting places empty strings before letters, and null values after.
Is Not Null	there is a value in the field

Advanced filtering

Logical operator	Result returned if...
Contains	field value contains the filter value, even if the filter value is embedded within the field value For example, the filter value "ploy" returns records with the field value "Employee".
Does not contain	field value does not contain the filter value, even if the filter value is embedded within the field value For example, the filter value "ploy" excludes records with the field value "Employee".
Starts With	field value begins with the filter value
Does not start with	field value does not begin with the filter value
Ends With	field value ends with the filter value
Does not end with	field value does not end with the filter value
Is Empty String	field value contains an empty string Note Some fields return null values, some return empty strings, and some return both. Alphabetical sorting places empty strings before letters, and null values after.
Is Not Empty String	field value does not contain an empty string
Equals Column	values in two fields are equivalent
Different from Column	values in two fields are different from each other
Greater Than Column	values in one field are greater than the values in another field
Greater Than or Equal to Column	values in one field are greater than or equal to the values in another field
Less Than Column	values in one field are less than the values in another field
Less Than or Equal to Column	values in one field are less than or equal to the values in another field
Link to Filter	the user selects one value on the report page and uses it across multiple filters
Minimum Date	a date value exists (the earliest date value is returned)
Maximum Date	a date value exists (the latest date value is returned)
Like	field value contains the derived filter value. For example, the filter value "emp%" returns records with the field value "employee", "employer", etc.
Not Like	field value does not contain the derived filter value. For example, the filter value "emp%" excludes records with the field value "employee", "employer", etc.