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.
Components of a Query Filter
See the following video to learn more about the components of a query filter and list of operators.
List of Operators
List of Operators to Choose From
| Operator | Retrieves data | Example | Data retrieved |
|---|---|---|---|
| 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 between and including weeks 25 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.
