Filtering Data Using Simple Query Filters

Query filters retrieve a subset of the available data, based on the definition of the filter. For example, you can apply a query filter on the Year dimension, to view only the sales revenue for a specific year.

Restricting the query ensures you retrieve only the data which interests you, increasing the reports usefulness.

Advantages of using query filters include the following:

• You can retrieve and focus only on the data that you need to answer a specific business question.

• You can limit data you do not want specific users to see when they access the document.

• You can minimize the data returned to the document, thus improving performance, because the time required to create and refresh the document over the network is reduced.

Some universes have predefined filters built in by the designer. At other times, you may want to create your own query filters to limit the data. Users without rights to edit the query cannot modify the query filters you define. This ensures that the data saved in a document is appropriate for those who view or analyze it within the document.

Your administrator can prevent objects from being filtered. If you create filters on these objects and try to run a query, a warning appears, listing the filters that you cannot use. You need to remove these filters from the query to run the query.

List of Operators

The function of each operator depends on its context. Operators can perform a wide variety of comparisons yielding Boolean results.

List of operators to choose from:

Operator	Retrieves data	Example	Data retrieve
Equal to	Equal to a specified value	Country - Equal to US	For the US only
Not Equal to	Different from a specified value	Quarter - Not Equal to Q4	For all quarters except Q4
Greater than	Greater than a specified value	Customer age - Greater than 60	For customers older than 60
Greater than or equal to	Greater than or equal to a specified value	Revenue - Greater than or equal to 10,000	For revenue of $10,000 or greater
Less than	Lower than a specified value	Exam grade - Less than 40	For exam grades less than 40
Less than or equal to	Lower than or equal to a specified value	Customer age - Less than or equal to 30	For customers aged 30 or less
Between	Including and between two specified values	Weeks - Between 25 and 36	For all weeks excluding week 25 through to 36
Not between	Outside the range of specified values	Weeks - Not between 25 and 36	For all weeks excluding week 25 through to 36
In list	Same as specified values	Country - In list 'US; Japan; UK'	For US; Japan; and UK only
Not in list	Different to multiple specified values	Country - Not in list 'US; Japan; UK ‘	For all countries except US; Japan; and UK
Is null	For which there is no value in the database	Children - Is null	For customers without children, where no value is entered as a value in the children column
Is not null	For which there is a value in the database	Children - Is not null	For customers with children where any value is entered in the children column
Matches pattern	Including a specific string	Phone - Matches pattern "%773%"	For all phone number which include '773'
Different from pattern	Does not include a specific string	Phone - Different from pattern "%773%"	For all phone numbers which do not include 773
Both	Corresponds to two specific values	Account type - Both "Fixed" and "Mobile"	For customers with both a fixed line and a mobile phone
Except	Corresponds to one specified value but not to a second specified value	Account type -"Fixed" Except "Mobile"	For customers who have a fixed phone line but no mobile

Types of Query Filters

Predefined filters make the specific data you most typically need for reports permanently available.

They're created by a BI administrator and saved with the universe. Predefined filters often contain complex expressions that require a detailed knowledge of the database structure. Including predefined filters on the universe means you don’t need to create the same custom filters every time you create a new document based on the same universe. In the query panel, predefined query filters are listed along with other objects in the objects list.

You can't view the component parts of predefined filters or edit predefined filters.

When you add a single-value query filter to a query, you limit the data returned by a specific object to a single value.

For example, you can find data related to a certain store name by building a condition on the Store name dimension object and selecting the name of the store you are interested in by selecting it from the dialog box that appears.

After you run the query and review the data that the report displays, you may need to include or restrict more values for the object you used to create the query filter.

You can change the query filter by editing the query in the Query Panel.

When selecting from a list of values in a query, items in the list can appear either as a single or multi column list or a hierarchy, depending on the object.

In a list of values, select items that you want to appear:

• If the list of values does not appear when a dialog box opens, refresh the list, or search the list to retrieve values. Some lists of values require an initial search to display values because the list is too large to be loaded in full.
• If the list of values is divided into ranges, use the control above the list to navigate through the ranges. Some large lists of values are divided into ranges to reduce the amount of data retrieved from the database. When you select a range, the list displays the values in that range.
• If the list of values depends on other lists of values, specify the dependent values in the prompt dialog box that appears. A list of values can be dependent on other lists of values, for example when it is part of a hierarchical list of values. For example, if the list of values contains cities, and the City object is part of the hierarchy Country, Region, City, you need to specify values for Country and Region first to filter the list of Cities.

Settings of the list of values:

Use single and multiple character wildcards for pattern matching in query conditions when you don't know the exact word, phrase, or line product you're seeking.

Wildcards are special characters that denote any single character or any number of characters.

When entering a pattern match for a prompt (or a constant in the query), use the following wildcards:

• _ (underscore) for any single character.
• % (percent sign) for any number of characters.

When you create the query filter using a wildcard, you must use one of the following relational operators:

• Matches Pattern: To find values that match the pattern you specify.
• Different from Pattern: To find values different than the pattern you specify.