Query filters are used to limit the rows returned in a Webi document. Query filters allow you to hide the data that you don’t want to show to specific people and also limit the size of .wid document. When you run a query, it only returns the rows that meet the query filter definition.

Following are the key features used in Query filter −

You can retrieve data as per specific business requirement.
You can hide the data that you don’t want specific users to see in a Webi document.
Limit the size of Webi document of the network and hence provides performance optimization.

Example − Being an Area Sales Manager for NY, you want to see the margin values for your region. Sales universe contains data from all the regions in United States. Also you only want to see data from the stores in NY where the sales margin is greater than 100K USD in the second Quarter (Q2) 2015.

Now to create a Webi document with this information, you have to apply filters on these dimensions – State, Year, and Quarter and Filter on sales margin.

Filter Operator AND
	Filter Condition
	Year = 2015
	Quarter = Q2
	State = NY
	Sales Margin >= 100000

Difference between Query Filters and Report Filters

Query filters are applied in Query Panel and they are used to limit the number of rows from data sources and to return the same in the document.

Report filters are applied at the report level on reports, tables, charts, etc. These filters are not used to limit the data retrieval from the data source and only hide certain values at the report level.

Query Filter Structure

Query filter is applied in this format −

Object (On which filter is applied), Operator (You can use various comparison operators), and Operand.

1 59

Operand − You can select from the following list of Operands −

Constant
List of Values (LOVs)
Prompt
Universe Object
Result from Other Query

Operators − You can select from the following operators −

Equal to (=)
Not Equal to
Greater than
Less than
Greater than Equal to
Less than Equal to
Between

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.

2 58

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.

3 58

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

4 54

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

5 49

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.

6 41

Click on the arrow to select the filter type.

7 34

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.

8 31

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.

9 23

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

10 16

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

11 13

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

12 9

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

13 8

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.

14 7

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.

15 4

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 FloridaYear 2005Which Category

16 3

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

17 3

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

18 2

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.

19 1

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.

20 1

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.

21 1

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.

22 1

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.

23 1

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

So, this brings us to the end of blog. This Tecklearn ‘Query Filters and Filters Types in SAP Webi’ blog helps you with commonly asked questions if you are looking out for a job in SAP Business Object and SAP Domain. If you wish to learn SAP Webi and build a career in SAP domain, then check out our interactive, SAP Business Objects Certification Training, that comes with 24*7 support to guide you throughout your learning period. Please find the link for course details:

https://www.tecklearn.com/course/sap-business-objects-certification-training/

SAP Business Objects Certification Training

About the Course

Business intelligence tools and features are used by multiple companies and firm for the effective results their large flow of data. The completion of this SAP Business Objects training will be helpful in landing yourself a good paying job. With this Business Objects online training, you will gain considerable proficiency in SAP BO architecture, data warehousing, use of Information design tool, create multiple reports and the implementation of Web intelligence and Efficiency in solving the issues of data sources and Crystal dashboard design.

Why Should you take SAP Business Objects Training?

The Average salary of SAP Business Objects Business Intelligence Analyst is $94,000 per annum – PayScale.com
Business Objects is an enterprise reporting tool and Scalability is one of the primary reasons large corporations choose Business Objects for their BI needs.
Worldwide Business Intelligence and Analytics Market to grow to $22.8 billion in next 2 years – Gartner.

What you will Learn in this Course?

Introduction to Business Objects

History and Version of Business Object
How Business Objects works in SAP family
SAP BO-Business Intelligence package and tool list
Data warehousing concept from SAP perspective

SAP Business Object Architecture

SAP BO Architecture
Various tiers in architecture: Client, Processing, Intelligence

SAP Web Intelligence

Web Intelligence Overview
Variable in Web Intelligence environment

Using Universe Designer Tools (UDT)

Overview of the Universe Designer Tool
Working with shared, secured and personal connections
Understanding of the Semantic layer

SAP BO Reports

SAP BO Reports
Chart Reports, Table Reports, Sorting and aggregating of Reports
Various elements of Reports, graphs, text elements
Reports and Filters
Section Report
Break Report
Alerts Report
Scope of Analysis Panel
Conditional Reporting
Ranked Report
Drill Report

Advanced Formatting

Advanced methods of formatting in SAP BO
Identifying Chasm trap and resolving Chasm traps with various methodologies
Detection and resolving of loop

Information Design Tool (IDT) Integration

Introduction to SAP BO information design tool
Extracting data from various sources using OLAP methodologies
Creating of Universe with IDT
IDT Vs UDT
Working with IDT as an Integration tool

Getting started with Crystal Dashboard

Data visualization tool for creating interactive dashboards
Point-and-click Features
Various dashboard features and interface

Live Office

Live office
Detailed design of Crystal Dashboard

SAP BO 4.2 Features

Got a question for us? Please mention it in the comments section and we will get back to you.

479