Creating a filter condition

When you create a filter, you define one or more conditions that specify which data rows to return. A filter condition is an If expression that must evaluate to true in order for a data row to be returned. For example:

If the order total is greater than 10000

If the sales office is San Francisco

If the order date is between 4/1/2012 and 6/30/2012

Filter conditions are appended to the information object query’s WHERE clause, for example:

WHERE OrderTotal > 10000 AND SalesOffice LIKE 'San Francisco%' AND OrderDate BETWEEN TIMESTAMP '2012-04-01 00:00:00' AND TIMESTAMP '2012-06-30 00:00:00'

Figure 2-10 shows an example of a condition defined in Filter Conditions. Filter Conditions helps you define the condition by breaking it down into the following parts:

The column to evaluate, such as credit limit

The comparison operator that specifies the type of comparison test, such as > (greater than)

The value to which all values in the column are compared, such as 10000

Figure 2-10

Filter Conditions displaying a filter condition

Table 2-2 lists the operators you can use when you create an expression for a filter condition.

Table 2-2

Operators in filter condition expressions

Operator	Use to test whether	Example
BETWEEN	A column value is between two specified values.	Profit BETWEEN 1000 AND 2000
= (Equal to)	A column value is equal to a specified value.	CreditLimit = 100000
> (Greater than)	A column value is greater than a specified value.	Total > 5000
>= (Greater than or equal to)	A column value is greater than or equal to a specified value.	Total >= 5000
IN	A column value is in the specified set of values.	Country IN ('USA', 'Canada', 'Mexico')
IS NOT NULL	A column value is not a null value. A null value means that no value is supplied.	CreditLimit IS NOT NULL
IS NULL	A column value is a null value.	CreditLimit IS NULL
< (Less than)	A column value is less than a specified value.	Total < 5000
<= (Less than or equal to)	A column value is less than or equal to a specified value.	Total <= 5000
LIKE	A column value matches a string pattern.	ProductName LIKE 'Ford%'
NOT BETWEEN	A column value is not between two specified values.	Profit NOT BETWEEN 1000 AND 2000
<> (Not equal to)	A column value is not equal to a specified value.	CreditLimit <> 100000
NOT IN	A column value is not in the specified set of values.	Country NOT IN ('USA', 'Canada', 'Mexico')
NOT LIKE	A column value does not match a string pattern.	ProductName NOT LIKE 'Ford%'

How to create a filter condition

1	In Information Object Query Builder, choose Filters.

2	In Filters, choose New.

3	In Filter Conditions, in Filter by, do one of the following:

Select a column from the drop-down list. The drop-down list contains the non-aggregate columns that you defined in Columns. To create a filter for an aggregate column, use Having.

Type an expression.

Use the expression builder to create an expression.

4	Select the comparison test, or operator, to apply to the selected column or expression. Depending on the operator you select, Filter Conditions displays one or two additional fields, or a completed filter condition.

5	If you selected an operator that requires a comparison value, specify the value in one of the following ways:

Type the value or expression.

If you selected a column in Filter by, choose Select Value to select from a list of values. Figure 2-11 shows the selection of Boston from a list of possible sales office values.

Figure 2-11

Select Values showing the values in the selected column

Select a parameter or column from the drop-down list. You create parameters in Parameters.

Open the expression builder to create an expression.

Figure 2-12 shows the completed filter condition.

Figure 2-12

Filter Conditions displaying a completed filter condition

Choose OK. The filter condition appears in Filters as shown in Figure 2-13.

Figure 2-13

Examining a filter condition

6	Display the Actuate SQL query. Verify that the filter condition is appended to the WHERE clause and that the syntax is correct, for example:

WHERE SalesOffice LIKE 'Boston%'

Copyright Actuate Corporation 2012