How Query Filters Work

Introduction

Data sources contain thousands of rows of data. To only retrieve the specific data that interests you for the document you are creating, you define filters when you define the query.

The difference between query filters and report filters

You can apply filters at two levels:

the query definition – this is the data defined on the Result Objects pane on the Query tab, which is retrieved from the data source and returned to the document.
the data displayed in a report – this is a subset of the query definition data that you want to display in a specific report table or chart in the document.

Filters you apply to the query definition are called query filters. You use query filters to reduce the amount of data retrieved from the data source. Query filters decrease the time it takes to run the report and ensure that only the data relevant to the report is saved with the document.

Filters you apply to the data displayed in a report are called report filters. You use report filters to restrict the values displayed in the report table or chart. The values hidden by the report filters are still saved with the document. You can modify the report filters to display different values in the report or remove the report filters to display all the values retrieved from the data source. This section tells you how to create and apply query filters.

What makes up a filter?

To create a filter, you need to specify three elements:

a filtered object
an operator
a constant(s)

EXAMPLE

For example, to display data only for a specific Agency, you select:

When you run the query, the operator is applied to the filtered object and retrieves the value(s) from the database that correspond to the constant(s) you specified. You can filter multiple objects in a report. For example, you can create another filter to focus your data to a more specific range of results:

When you run the report again, it will reflect the range of values specified in the filters.

What objects can I filter on a query?

You can define filters on any of the dimension, measure, or detail objects listed on the Universe Objects tab.

Note: Your administrator can prevent objects from being filtered. If you select one of these objects for a filter, the filter options are grayed out on the Filter Editor.

Which operator should I choose?

It is important to understand the effect of the operator you select when you define a report filter. The table below lists the operators available for report filters and provides an example of each operator in the context of a business question:

To obtain data...	for example...	select...	to create the filter...
equal to a value you specify,	retrieve data for the US only,	Equal to	[Country Code] Equal to US.
different from a value you specify,	retrieve data for all countries except US,	Different from	[Country Code] Different from US
greater than a value you specify,	retrieve data for contracts after Jan 1, 2004,	Greater than	[Period of Performance Start Date] Greater than 01/01/2004
greater than or equal to a value you specify,	retrieve data for contracts since Jan 1, 2004,	Greater than or equal to	[Period of Performance Start Date] Greater than or equal to 01/01/2004
lower than a value you specify,	retrieve data for contracts before Jan 1, 2004,	Less than	[Period of Performance Start Date] Less than 01/01/2004
lower than or equal to a value you specify,	retrieve data for contracts on Jan 1, 2004 and earlier,	Less than or equal to	[Period of Performance Start Date] Less than or equal to 01/01/2004
between two values you specify that includes those two values,	dates starting at Jan 1, 2004 and finishing at March 31, 2004,	Between	[Period of Performance Start Date] Between 01/01/2004 and 03/31/2004
outside the range of two values you specify,	all the days of the year, except for February 1 through 14, 2004 (Feb 1 and 14 are not included),	Not between	[Period of Performance Start Date] Not Between 02/01/2004 and 02/14/2004
the same as several values you specify,	you only want to retrieve data for the following states: Alaska, California, and Wisconsin,	In list	[Location State Code] In list ‘AK; CA; WI'
different from the multiple values you specify,	you don’t want to retrieve data for the following states: Alaska, California, and Wisconsin,	Not in list	[Location State Code] Not in list ‘AK; CA; WI'
for which there is no value entered on the database,	customers without children (the children column on the database has no data entry),	Is null	[Children] Is null
for which a value was entered on the database,	customers with children (the children column on the database has a data entry),	Is not Null	[Children] Is not Null
that includes a specific string,	customers whose date of birth is 1972,	Matches pattern	[DOB] Matches pattern, ‘72’
that doesn’t include a specific string,	customers whose date of birth is not 1972,	Different from pattern	[DOB] Different from pattern, ‘72’
that corresponds to two values you specify,	telco customers who have both a fixed telephone and a mobile phone,	Both	[Account Type] Both “fixed” and “mobile”
that corresponds to a single value and does not correspond to another value you specify	telco customers who have a fixed telephone, but don’t have a mobile phone,	Except	[Account Type] “fixed” Except “mobile”

What is a constant?

A constant is a fixed value you specify. The constant you specify for a filter can be a string of numbers or characters, depending on the object you are filtering. For example, if you are filtering a dimension, such as [Agency ID], the constant will be a number representing a figure. If you are filtering a dimension, such as [City], the constant will be a character string representing a city name.

How many filters can I apply to a query?

You can include one or multiple filters on a query.

I filter data without including the corresponding objects in a report?

Query filters limit the data returned to the document from the database. They filter the data definition of the whole document. You can choose to:

include the object you filter among the objects available in the document
or
filter the object without including the object in the document

This illustration shows an example of each:

Filtering data not displayed in the report allows you to limit the size of tables and charts and show only the values you need to analyze. It is also useful if you want to filter a query to limit the document information to specific values, but you don’t want other users who modify the reports to see the filtered object.