SAP Webi - Query Filter Types

---

---

Following types of query filters are normally used −

• Predefined Filters
• Custom Filters
• Quick Filters
• Prompts

You can add multiple filter types on a single query.

Predefined Query Filters

These filters are normally Administrators and saved at the Universe level. They are used to populate data that is permanently available.

These filters require some good knowledge of database design and complex expressions. Using predefined filters on Universe means you don’t need to create custom filter every time when a new Webi document is created.

Also note that you can’t access the components of Predefined filters and it is also not possible to edit them.

To add a Predefined filter, you can drag or double-click this filter to Query Panel → Filters Pane.

When you run the query, corresponding data w.r.t filters will be added to the report.

How to Use a Predefined filter?

You can use a Predefined filter by a double-click or just by dragging the filter to Query Filter.

Quick Filters

Quick filters are used to quickly retrieve the values you want without the use of filter editor. Quick filters use equal to operator while using a single value or list in operator when you use multiple values.

Quick filters can’t be used with BEx queries.

How to Use a Quick Filter?

Quick filters are used in Query panel. To apply a Quick Filter, select the object you want to filter.

Select ‘Add Quick Filter’ at the top right corner of Result Object Pane.

New dialog box will open. You have to select the value you want to use in the Quick filter from LOVs.

That filter will be added Query Filters pane. To delete this filter, select the filter in Query filter pane and press ‘Delete’ button.

Custom Query Filters

You can also create custom query filters to meet the business requirement or to hide the data from specific users.

To create custom query filter, add the object you want to use in the filter. Drag it to Query filter pane. Click the arrow next to default operator and select the operator.

Click on the arrow to select the filter type.

You can select the following filter type −

• Constant
• Value from list
• Prompt
• Object from this query
• Result from another query

Select the value from this that you want to include in the filter. To remove a filter, select the filter and press ‘Delete’ button. To remove all the filters, use ‘Remove All’ option at the top right corner of the screen.

Prompts

Prompt is defined as a special filter for users, which allows them to enter a value every time data is refreshed in the document.

Using prompts, you have multiple users viewing one document to display different sub set of data in database. Prompts allow you to retrieve the data from database and reduce the time.

Following are the elements of a Prompt −

• An Object
• An Operator
• Message

Example − Current Year Equal to (“Enter the Year”). In this Prompt, Current Year is the object, “Equal to” is the Operator and prompt message is “Enter the Year”.

A Prompt can be applied to Dimension, Measures, attributes, hierarchies, etc.

You can use AND, OR operators to create multiple prompts in the same query. While using BEx and .unx Universe, you can only use AND operator with prompts.

Merged Prompts

When you use multiple data providers in a single document, you can merge multiple prompts with same data type, same operator types and same prompt text are merged. While refreshing all the data providers, one prompt appears for all multiple prompts.

LOVs displayed by merged prompt is the list associated with the object in prompt with the most displayed property constraints.

Hierarchical Prompts

You can also create hierarchical prompts where the following objects display their LOVs hierarchically in a Prompt −

• Hierarchies
• Levels
• Dimensions associated with hierarchical LOVs

Hierarchical prompts are displayed in a tree form and you can navigate up and down the tree. As per the filters in Prompt, you can select items from different levels of LOVs.

Building Prompts

To build a prompt, add the object you want to filter with a prompt to Query Filter pane.

Select the filter operator from the list and click on the last arrow mark to select a prompt.

You can add the text message for Prompt value and run the query.

When you run the query, Prompt dialog box will allow you to enter the values as per the selected operator.

When the values are selected and you click ‘Ok’, and the data for selected values in the document will be reflected.

When you refresh the document in Webi, prompt will appear every time to select the values.

Select an Existing Prompt

You can also select from existing prompts to add to the query in query panel.

To use an existing prompt, drag the object on which prompt has to be applied to query filter pane.

Select from Universe, select an existing Prompt → Ok. It will display the list of all prompts that are compatible with the object in Query filter.

Deleting a Prompt

To delete a prompt, select the prompt in Query filter pane and press the ‘Delete’ button. You can also select Remove or Remove all option.

Combining Prompts with Query Filter

You can also combine prompts with query filters to limit the data in the document and to select a specific record from the filtered data.

Example − You can apply Query filters for Department and Year and Prompt for a specific Employee name input.

State Not Equal to Florida
Year 2005
Which Category

When you run the query, it will ask you to enter the value for Category.

In Webi document, it will filter the data as per filters in the query panel and display the result according to Prompt value.

Order of Prompts

You can also use multiple prompts in a single query. To add multiple prompts, drag all the objects on which you want to apply prompts. Select prompt by clicking on Arrow mark in the end of query.

To define the order of Prompts, you have to go to Query properties at the top. From there, you can select the order of prompts as per requirement. You can move up/down a prompt from the list.

Subqueries for Data Filtering

Subqueries provides a more flexible option to filter the data as compared to an ordinary query filter. Using subqueries, you can limit the values returned with a WHERE clause. You can also compare the values of objects in a subquery with the other objects.

Using subqueries, you can implement complex logic to limit the size of data, which is not possible to apply with simple query filters.

Subqueries work on SQL which is used to retrieve the query data. SQL is supported by most of RDBMS and each database uses its own syntax. If any database doesn’t support SQL, an option to create a subquery will not highlight in the query panel.

Subqueries can be built on dimensions, measures and attributes and not on the hierarchical objects.

Building a Subquery

Select the object in the result pane on which you want to build a subquery.

It will add a subquery outline in the Query filter pane. Now if you want to add a WHERE condition, drag an object to the area of subquery.

Select the Operator and Value used to filter object in WHERE condition. You can add multiple subqueries to query panel. You can use “AND” or “OR” relationship between subqueries. To change the relationship, you can click on AND to change it to OR.

By default, two subqueries are linked with an AND relationship. You can also nest a subquery. Drag a subquery to the area and drop an object.

Parameters − You can use the following parameters while passing a subquery to Query filter pane.

Filter Objects − These are the objects whose values are used to filter result objects.

Filter By Objects − The object that determines which filter value subquery returns.

Operator − This operator defines the relationship between the filter object and the filter by object.

WHERE Condition − This is used to constraint the list of values of the filter by object.

Relationship Operator − AND, OR