Understanding Query Filter Basics

Objectives

After completing this lesson, you will be able to:

  • Create simple query filters

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.

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

OperatorRetrieves dataExampleData retrieved
Equal toEqual to a specified valueCountry - Equal to USFor the US only
Not Equal toDifferent from a specified valueQuarter - Not Equal to Q4For all quarters except Q4
Greater thanGreater than a specified valueCustomer age - Greater than 60For customers older than 60
Greater than or equal toGreater than or equal to a specified valueRevenue - Greater than or equal to 10,000For revenue of $10,000 or greater
Less thanLower than a specified valueExam grade - Less than 40For exam grades less than 40
Less than or equal toLower than or equal to a specified valueCustomer age - Less than or equal to 30For customers aged 30 or less
BetweenIncluding and between two specified valuesWeeks - Between 25 and 36For all weeks between and including weeks 25 to 36
Not betweenOutside the range of specified valuesWeeks - Not between 25 and 36For all weeks excluding week 25 through to 36
In listSame as specified valuesCountry - In list 'US; Japan; UK'For US; Japan; and UK only
Not in listDifferent to multiple specified valuesCountry - Not in list 'US; Japan; UKFor all countries except US; Japan; and UK
Is nullFor which there is no value in the databaseChildren - Is nullFor customers without children, where no value is entered as a value in the children column
Is not nullFor which there is a value in the databaseChildren - Is not nullFor customers with children where any value is entered in the children column
Matches patternIncluding a specific stringPhone - Matches pattern "%773%"For all phone number which include '773'
Different from patternDoes not include a specific stringPhone - Different from pattern "%773%"For all phone numbers which do not include 773
BothCorresponds to two specific valuesAccount type - Both "Fixed" and "Mobile"For customers with both a fixed line and a mobile phone
ExceptCorresponds to one specified value but not to a second specified valueAccount 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.

Single-Value Query Filter

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.

Single-Value Query Filter Edits

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.

Selections from a List of Values

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

Query Filter Basics

Log in to track your progress & complete quizzes