Filtering ad hoc reports

Several filtering options are available for you to customize the data that you see in an ad hoc report:

• You can define simple or advanced filters using one or several columns

• You can filter a data grid according to several values of a single criterion (column)

Whichever filter you apply to a report, your preference is retained for the grid.

In this article you'll find information about:

• Available filter operators in ad hoc reports

• Filtering an ad hoc report according to one or several criteria

• Filtering an ad hoc report according to multiple values

• Removing individual filters from an ad hoc report

Available filter operators in ad hoc reports

When working in an ad hoc report, you can add filters and use different operators to sort its content. The available operators vary depending on the type of fields configured in the report.

An operator is an inclusive or exclusive condition (text or mathematical symbol) allowing to precisely target the filtering process.

This is a warning message.

When several filters are applied, the report will contain only the records that match all of the defined filters.

The following table details the data types with the operators available for them:

Data Type	Operator	Description
String	contains	Returns the records containing the entered character string in the selected criterion.
	does not contain	Returns the records not containing the entered character string in the selected criterion.
	is equal to	Returns the records containing the exact entered word or letter in the chosen criterion.
	is not equal to	Returns the records except the ones containing the exact entered character string in the selected criterion.
	starting with	Returns the records starting with the entered character string in the selected criterion.
	not starting with	Returns the records not starting with the entered character string in the selected criterion.
	ending with	Returns the records ending with the specified character string in the selected criterion.
	not ending with	Returns the records not ending with the specified character string in the selected criterion.
	in	This operator enables you to filter the grid according to several values of the same criterion. The values need to be entered exactly as they are in the grid and separated by a comma (,). When the "in" operator is used, the search will return all the records containing the entered values.
	not in	This operator enables you to filter the grid according to several values of the same criterion. The values need to be entered exactly as they are in the grid and separated by a comma (,). When the "not in" operator is used, the search will return all the records not containing the entered values.
	is empty	Returns the records not containing character string value in the selected criterion.
	is not empty	Returns the records containing at least one character string value in the selected criterion.
Number	=	Returns the records containing the specified number value in the selected criterion.
	≠	Returns the records except the ones containing the entered number value in the selected criterion.
	<	Returns the records with values smaller than the entered value in the selected criterion.
	>	Returns the records with values greater than the entered value in the selected criterion.
	≤	Returns the records with values smaller or equal to the entered value in the selected criterion.
	≥	Returns the records with values greater or equal to the entered value in the selected criterion.
Boolean	is checked	Returns the records for which the value is checked in the selected criterion.
	is not checked	Returns the records for which the value is not checked in the selected criterion.
Date Datetime Time	on or after	Returns the records for which the date is on or after the one entered in the selected criterion.
	on or before	Returns the records for which the date is on or before the one entered in the selected criterion.
	after	Returns the records for which the date is after the one entered in the selected criterion.
	before	Returns the records for which the date is before the one entered in the selected criterion.
	is equal to	Returns the records for which the date is the same as the one entered in the selected criterion.
	is not equal to	Returns the records except the ones including the date entered in the selected criterion.
	is empty	Returns the records that do not contain any value for the selected criterion.
	is not empty	Returns the records containing at least one value in the selected criterion.
	selection	Returns the records containing the selected dates among the values of the drop-down list.

Filtering an ad hoc report according to one or several criteria

The ad hoc report offers a filtering feature that enables you to filter the grid according to the value of one or several columns (criteria). You can use numeric, string, date and list columns define a filter.

To define a filter on an ad hoc report, proceed as follows:

1. Open the report with which you want to work (see Viewing an ad hoc report).

2. Click the Filter button above the report. The filtering tool is opened, as illustrated in the following figure:

Filter tool

3. In the first drop-down list, on the left, select the column on which you want to apply the filter.

4. In the second drop-down list, in the middle, select the operator that you want to use for the filter. Please note that the list of available operators depends on the type of column (date, text, number, etc.).

5. In the third field, enter or select the value that you want to apply to the filter.

6. Click Apply. The filter is applied to the grid and an indicator badge is applied in the grid toolbar, as illustrated in the following figure:

Filter badge

You can add as many filters as you want by clicking the Add Filter button and repeating the procedure above. However, please keep in mind that the "AND" operator is applied by default. In other words, when the filters are applied, the grid will display only the records that match all of the defined filters. Defined filters are retained for the grid when you exit the screen.

This is a tip message.

To remove a filter, see Removing Individual filters from an ad hoc report and Removing all filters from an ad hoc report.

Filtering an ad hoc report according to multiple values

The v10 application offers two possibilities to filter an ad hoc report according to multiple values of a single column (criterion):

• Filtering using the "in" and "not in" operators (string-type fields only; see Filtering an ad hoc report according to several values of a single criterion ("in"/"not in"))

• Filtering using the multiple value filtering (see Filtering an ad hoc report using multiple value filtering)

Filtering an ad hoc report according to several values of a single criterion ("in"/"not in")

You can filter the data of an ad hoc report according to several values of a single criterion using the filter tool of the report.

This is a warning message.

It is only possible to define several filter values for string-type criteria.

To define multiple values for a filter criterion, proceed as follows:

1. Click Filter to open the filter tool of the report (see Filtering an ad hoc report according to one or several criteria).

2. In the first drop-down list, select the criterion (column) according top which you want to filter the report.

3. In the second drop-down list, select one of the following operators: "in" (only entered values will be included in the result) or "not in" (entered values will be excluded from the result).

4. In the value field (third field), enter the exact values, separated by a comma, as illustrated in the following figure:

Defining multiple values for a criterion

5. If required, add additional criteria. You can use multiple values in several criteria simultaneously.

6. Click Apply. The grid is filtered according to the filter definition and an indicator badge is displayed next to the Filter button in the report toolbar.

Filtering an ad hoc report using multiple value filtering

You can filter an ad hoc report according to several values of the same criterion by using several filtering conditions in the filter tool.

To do so, proceed as follows:

1. Open the report in which you want to work and then click Filter in the grid toolbar. The filter tool opens.

2. In the first drop-down list, on the left, select the column on which you want to apply the filter.

3. In the second drop-down list, in the middle, select the operator that you want to use for the filter. Please note that the list of available operators depends on the type of column (date, text, number, etc.).

4. In the third field, enter or select the value that you want to apply to the filter.

5. Click Add Filter.

6. Select the same column in the first drop down, and then repeat steps 3 and 4.

7. Click Apply. Both filter conditions are applied to the report.

This is a warning message.

When using multiple filtering values, the application applies the "AND" operator. While there is technically no limitation on the number of values you can use, please keep in mind that the "AND" operator is restrictive and the results displayed in the report will have to match ALL defined criteria.

Removing individual filters from an ad hoc report

To remove individual filters from an ad hoc report, proceed as follows:

1. Click Filter in the report toolbar. The filter tool is opened.

2. In the list of defined filters, locate the filter that you want to remove.

3. Click the " X " icon at the right of the filter, as illustrated in the following figure:

Individual filter removal

4. Click Apply in the lower-right corner of the filter tool. The tool is closed and the filter is removed.

This is a tip message.

You can remove all the filters applied to ad hoc report by repeating step 3 until all filters are deleted and then clicking Apply. To remove all filters in one click, see Removing all filters from an ad hoc report.

Removing all filters from an ad hoc report

To remove all filters from an ad hoc report, proceed as follows:

• In the report toolbar, click the " X " icon located at the right of the filter indicator badge, as illustrated in the following figure:

Complete filter removal

This is a tip message.

You can remove individual filters by following the procedure described in Removing individual filters from an ad hoc report.