Creating a filter condition

When you create a filter, you define a condition that specifies 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/2008 and 6/30/2008

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

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

Figure 3-25 shows an example of a condition defined in Filter Conditions.

Figure 3-25

Filter Conditions displaying a filter condition

As Figure 3-25 shows, 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.

Table 3-3 lists the operators you can use when you create expressions for filter conditions.

Table 3-3

Operators in filter condition expressions

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

How to create a filter condition

1	In the graphical information object editor, 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 on the Columns page. To create a filter for an aggregate column, use the Having page.

Type an expression.

Choose Ellipsis 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 3-26 shows the selection of Boston from a list of possible sales office values.

Figure 3-26

Select Value showing the list of values in the selected column

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

Choose Ellipsis to create an expression.

Figure 3-27 shows the completed filter condition.

Figure 3-27

Filter Conditions displaying a completed filter condition

Choose OK. The filter condition appears in Filters as shown in Figure 3-28.

Figure 3-28

Filters page displaying 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