Understanding Query Filter Basics

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.

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.

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 multicolumn 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: