Understanding Query Filters - Intermediate


After completing this lesson, you will be able to:

  • Create a query filter using a wildcard
  • Use prompts in query filters
  • Use multiple query filters to restrict data

Wildcards in Query Filters

You can use wildcards as placeholders for partial pattern searches.

When you create a 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 from the pattern you specify.

Restrict Data Using Wildcards

Prompted Filters

The following is an example of an optional prompt:

The optional prompt uses the between operator and allows the user to enter two values to set the limits of a range, but the user enters only the lower end of the range. As a result, the generated query updates the operator from between to greater than.

When a prompt is made optional, the user can choose to ignore the prompt. The result is that everything is returned in the report. For example, if you make a year prompt optional, all years are retrieved in the report if the user chooses not to answer the prompt.

The following are uses of a prompted filter:

  • The Prompts dialog box allows you to specify the data that is retrieved and displayed in the report, allowing you to focus on a specific part of the information available.
  • A prompted filter allows multiple users viewing a single document to specify different subsets of the data and display them in the same tables and charts in the report.
  • You can use a prompted filter to define a question that displays whenever the data in the document is refreshed. The users can answer the prompt either by entering or by choosing values, depending on the parameter properties which have been specified for the prompt in the Query Panel.

    For example, you can use the prompted query filter to produce a report that shows sales revenue per state and prompts the user to choose a product category at each refresh.

    This way, you do not restrict the data displayed to a single product category, such as Jewelry. Instead, users choose the product category that interests them at that moment.

You can define prompts on any dimension, measure, or attribute object listed in the Universe outline in the Query Panel.

You cannot use the operators Is Null and Not Null when creating prompted filters.

Prompted Query Filter Creation

To create a prompted query filter, use the Prompt operand.

After you choose the Prompt operand, choose Parameter Properties between the Prompt text field and Operand type to display the Parameter Properties dialog box.

Parameter Properties

Query Filters using Parameter Properties

Prompt textEnter the text that you want to display to prompt the user to select a value. This text displays each time the document is refreshed.
Prompt with List of ValuesChoose this option to display all the values for the object and allow the user to choose from those values.
Select only from listChoose this option to prevent users from entering values that do not exist in the database.
Keep last values selectedChoose this option to display by default the value chosen at the previous refresh. The user can change the value or use the previously chosen value.
Optional prompt

Choose this option to allow users to refresh the document without responding to the prompt or by partially responding to the prompt.

When the user does not respond to the prompt, the query filter is not applied to the data.

Set default valuesChoose this option to define the default values that displays in the prompt. Define the default values using one of the following methods:
  • In the Type a value field, enter the values. Use the arrow to move the values to the field on the right.
  • Choose Values to choose the values from the list of values present in the database.

Some important points about prompted query filters are as follows:

  • When the document contains multiple data providers and the same prompt and prompt text are included in them, Web Intelligence displays the prompt only once.
  • When you make a prompt optional, the Prompts dialog box displays text to inform report users that when they do not provide a value for the prompt, the filter does not apply.

Prompts Dialog Box

The options you define for the prompt determine how the Prompts dialog box appears to users and how users can specify the values for their reports in the dialog box.

Prompt Query Filter Modification

After you have run the query and reviewed the data displayed in the report, you may need to change the filter criteria.

By simply refreshing the document lets you change the filter values without needing to return to the Query Panel.

Prompt Variant Sets in the Prompts Dialog Box

You can save a group of most used variable value sets as a prompt variant in the Prompts dialog box.

After the Web Intelligence document query is created, run, and saved for the first time, when you refresh or open the document, you can see the available prompt variants options in the Prompts dialog box. Once you have selected the values for each prompt in the Prompts Summary UI, you name and save the variable value set as a prompt variant. It is subsequently available in the Prompts dialog box from the available prompt variants list.

You must create, run, and then save the document to the CMS to see the prompt variant options. Only the user who created the prompt variants can view them and they only apply to the document in which they have been created on. When you delete a prompt variant, it is completely removed from the document. It cannot be recovered. Prompt variants do not work in Web Intelligence Rich Client in offline or standalone mode.

"Prompts" dialog box features:

Save variantThis feature is available after you have created, run, and saved the Web Intelligence query in the CMS for the first time. A button that allows you to create (or save) a prompt variant.
Variants managementThis feature lists any prompt variants you have saved.
DeleteA button that allows you to delete an existing prompt variant.

Add a Prompt

Logical Operators for Multiple Conditions

To produce a report that precisely focuses on certain data, you may need to apply more than one filter. When you specify more than one filter in a query, the relationship between the filters must use either the AND or OR operator. These are known as logical operators.

The types of logical operators are as follows:

  • AND

    You use AND when you want both conditions to be met to return a row from the database when you run a query.

  • OR

    You use OR when you want either of the conditions to be met to return a row from the database when you run a query.

See the following video to learn more about the operators.

Filter Prioritization

A complex filter uses more than one filter in a single query. To ensure that the filters retrieve exactly the data you want, you need to prioritize the operators.

In the Query Filters panel, by positioning the query filter statements and deciding how to group them together, you define the filters that are processed first. The positioning and grouping of the filters depends on the logic of the information you are trying to retrieve.

Restrict Data with Multiple Filters

Log in to track your progress & complete quizzes