Structure of query filters

Query filters have the following structure: filtered object, operator, operand.

In the filter [Country] InList (US;France), the [Country] dimension is the filtered object, InList is the operator, and the list of values (US;France) is the operand. The filter removes all values of [Country] other than US and France from the query result.

Table 4-35: Query filter components Description Component

The filtered object is the object whose values are filtered. Dimensions, at-tributes, measures, hierarchies and levels can be used as filtered objects.

With the exception of BEx queries, the filtered object is not required to ap-pear as a result object in the query. For example, a query that contains the [Customer] and [Revenue] objects can filter on the [Region] object. If the filter is [Region] Equal To "South West", the query returns only those customers in the South West region.

Filtered object

The operator is used to compare the filtered object with the operand. For example, the Equal To operator retains only those values of the filtered object that correspond exactly to the value of the operand.

Operator

The operand supplies the value or values used to filter the filtered object.

The next table describes the operand types.

Operand

Table 4-36: Operand types

Description Operand type

The constant operand is used to type values directly. For example, you can use a constant to type "France" into the filter [Country] Equal To France.

The operand cannot be a constant if the filtered object is a hierarchy, unless the hierarchy is used in conjunction with the Matches pattern or Dif ferent from patternoperator.

Constant

The List of Values operand is used to select values from the list associated with the filtered object. For example, if the filtered object is [City], you can use List of Values to select one or more of the cities associated with the object.

List of Values

A prompt is a dynamic filter that is answered when the query is refreshed.

Prompt

You can select an object from the universe to filter the filtered object against its values.

Note:

You cannot select a universe object as an operand on some OLAP data sources or the filtered object is a hierarchy.

Universe object

Description Operand type

You can compare the filtered object against the values returned by another query.

Result from another query

Related Topics

•BEx queries defined

4.9.1.3.1 Query filter and prompt operators List of operators

Equal To operator

Use the Equal to operator to obtain data equal to a value.

For example, to return data for the US only, create the filter "County Equal To US".

Not Equal To operator

Use the Not Equal To operator to obtain data not equal to a value.

For example, to return data for all countries except the US create the filter "County Not Equal To US".

Note:

This operator cannot be used for OLAP unx universe parent-child hierarchies, or for BEx queries.

Greater Than operator

Use the Greater Than operator to retrieve data greater than a value.

For example, to retrieve data for customers aged over 60, create the filter "[Customer Age] Greater than 60".

Note:

This operator cannot be used for OLAP unx universe parent-child hierarchies, or for BEx queries.

Greater Than Or Equal To operator

Use the Greater Than Or Equal To operator to retrieve data greater than or equal to a value.

For example, to retrieve data for revenue starting from $1.5M, create the filter "[Revenue] Greater than or equal to 1500000".

Note:

This operator cannot be used for OLAP unx universe parent-child hierarchies, or for BEx hierarchies.

Less Than operator

Use the Less Than operator to retrieve data lower than a value.

For example, to retrieve data for exam grades lower than 40, create the filter "[Exam Grade] Less Than 40".

Note:

This operator cannot be used for OLAP unx universes, and hierarchies in filters, or for hierarchies in BEx queries.

Less Than Or Equal To operator

Use the Less Than Or Equal To operator to retrieve data less than or equal to a value.

For example, to retrieve data for customers whose age is 30 or less, create the filter "[Age] Less Than Or Equal To 30".

Note:

This operator cannot be used for OLAP unx universes and hierarchies in filters, or for hierarchies in BEx queries.

Between operator

Use the Between operator to retrieve data between two boundary values (including the two boundary values). The first value declared must be lower than the second value.

For example, to retrieve data for weeks starting at week 25 and finishing at 36 (including week 25 and week 36), create the filter "[Week] Between 25 and 36".

Note:

This operator cannot be used for OLAP unx universe and for BEx hierarchies in filters.

Not Between operator

Use the Not Between operator to retrieve data outside the range of two values.

For example; to retrieve data for all the weeks of the year, except for and not including weeks 25 through 36, create the filter "[Week] Not between 25 and 36".

Note:

This operator cannot be used for OLAP unx universe and for BEx hierarchies in filters.

In List operator

Use the In List operator to retrieve data corresponding to values in a list of values.

For example, to retrieve data for the US, UK and Japan only, create the filter [Country] In List, when you can type values in the "Type a value" field, you have to enter: US;UK;Japan.

When used in a query filter with a hierarchical list of values (either from a dimension associated with a hierarchical list of values or a hierarchy object), In List allows selection of multiple members from any levels of the hierarchy. For example, a prompt on the [Geography] hierarchy using the In List operator allows selection of [Paris] at the City level and [Canada] at the Country level in the prompt.

When used in a report filter, In List produces a flat list of values.

Not In List operator

Use the Not In List operator to retrieve data that does not correspond to multiple values.

For example, if you do not want to retrieve data for the US, UK and Japan, create the filter [Country]

Not In, when you can type values in the "Type a value" field, you have to enter: US;UK;Japan.

When used with a hierarchical list of values (either from a dimension associated with a hierarchical list of values, a hierarchy object or a level object), In List allows selection of multiple members from any levels of the hierarchy. For example, a prompt on the [Geography] hierarchy using the Not In List operator allows selection of [Paris] at the City level and [Canada] at the Country level in the prompt.

Note:

This operator can only be used in certain types of hierarchies - for example, it can be used in level-based hierarchies.

Matches Pattern operator

Use the Matches Pattern operator to retrieve data that includes a specific string or part of a string.

For example, to retrieve customers whose date of birth is 1972, create the filter [DOB] Matches Pattern

"72".

Note:

This operator cannot be used for BEx hierarchies.

Different From Pattern operator

Use the Different From Pattern operator to return data that doesn't include a specific string.

For example, to retrieve customers whose date of birth is not 1972, create the filter [DOB] Different From Pattern '72'.

Note:

This operator cannot be used for BEx or OLAP unx universe parent-based hierarchies.

Both operator

Use the Both operator to retrieve data that corresponds to two values.

For example, to retrieve customers who have both a fixed and a mobile telephone, create the filter [Account Type] Both 'Fixed' And 'Mobile'.

Note:

This operator is not supported for filters based on hierarchy objects, or in universes based on OLAP data sources.

Except operator

Use the Except operator to retrieve data that corresponds to one value and excludes another.

For example, to retrieve customers who have a fixed telephone and do not have a mobile telephone, create the filter [Account Type] 'Fixed' Except 'Mobile'.

The Except operator is more restrictive than Different From Pattern or Not In List. For example, a report that returns customers and that includes the filter [Lines] Different From 'Accessories'excludes all sales records where the item sold is part of the 'Accessories' line. If the same customer has purchased Accessories and non-Accessories items, the customer still appears in the report, but their spending total includes only non-Accessories sales.

If the filter is [Lines] Except 'Accessories', only customers who have bought no accessories are included in the report.

Note:

This operator is not supported in universes based on OLAP data sources.

Related Topics

•Different From Pattern operator

•Not In List operator

Restrictions on filter operators

The following table lists the restrictions on prompt and filter operators based on the filtered object and query type.

Available filters Object

Equal To, Not Equal To, In List, Not In List, Matches Pattern, Different From Pattern

Level-based hierarchy

Equal To, In List, Matches Pattern Parent-child hierarchy

Equal To, In List Hierarchy in BEx query

In document SAP BusinessObjects Web Intelligence Users Guide SAP BusinessObjects Business Intelligence Suite 4.0 Support Package 5 (Page 111-117)