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:

 

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:

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

[Effective 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

[Effective 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

[Effective 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

[Effective 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

[Effective 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

[Effective 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:

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.