Query filter and prompt operators

4.9.2.2.1 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

4.9.2.2.2 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

Available filters Object

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

Equal To, In List Hierarchy in BEx query