If an information object returns more data rows than you need, you can restrict the number of data rows by using a filter. For example, rather than list all customer sales, you can create a filter to select only the sales data for a particular week or only the sales data for a particular region.

Filtering data helps you work effectively with large amounts of data. It enables you to find the necessary pieces of information to answer specific business questions, such as which sales representatives generated the top ten sales accounts, which products generated the highest profit in the last quarter, which customers have not made a purchase in the past 90 days, and so on.

Filtering data can also have a positive effect on processing speed. Limiting the number of data rows can reduce the load on the databases because the information object does not need to return all the rows every time it is run.

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:

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

Figure 3‑23 shows an example of a condition defined in Filter Conditions.

As Figure 3‑23 shows, Filter Conditions helps you define the condition by breaking it down into the following parts:

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

So far, the filter examples specify one comparison value. Sometimes you need to view more data, for example, sales details for several sales offices, not for only one office. To select more than one comparison value, select the IN operator, choose Select Values, then select the values. To select multiple values, press Ctrl as you select each value. To select contiguous values, select the first value, press Shift, and select the last value. This action selects the first and last values and all the values in between.

Figure 3‑28 shows the selection of London and Paris from a list of sales office values.

You use comparison operators, such as = (equal to), > (greater than), or < (less than), to evaluate the filter condition to determine which data to include. Sometimes it is more efficient to specify a condition that excludes a small set of data. For example, you need sales data for all countries except USA. Instead of selecting all the available countries and listing them in the filter condition, simply use the NOT LIKE operator. Similarly, use NOT BETWEEN to exclude data in a specific range, and <> (not equal to) to exclude data that equals a particular value.

For example, the following filter condition excludes orders with amounts between 1000 and 5000:

The filter condition in the next example excludes products with codes that start with MS:

Sometimes, rows display nothing for a particular column. For example, suppose a customer database table contains an e‑mail field. Some customers, however, do not supply an e‑mail address. In this case, the e‑mail field might contain an empty value or a blank value. An empty value, also called a null value, means no value is supplied. A blank value is entered as '' (two single quotation marks without spaces) in the database table field. Blank values apply to string fields only. Null values apply to all data types.

You can create a filter to exclude data rows where a particular column has null or blank values. You use different operators to filter null and blank values.

When filtering to exclude null values, use the IS NOT NULL operator. If you want to view only rows that have null values in a particular column, use IS NULL. For example, the following filter condition excludes customer data where the e‑mail column contains null values:

The following filter condition displays only rows where the e‑mail column contains null values:

When filtering blank values, use the NOT LIKE operator with '' (two single quotation marks without spaces) as the operand. For example, to exclude rows with blank values in an e‑mail column, specify the following filter condition:

Conversely, to display only rows where the e‑mail column contains blank values, create the following condition:

In a report, you cannot distinguish between an empty value and a blank value in a string column. Both appear as missing values. If you want to filter all missing values whether they are null or blank, specify both filter conditions as follows:

When you create a filter condition that compares the date‑and‑time values in a column to a specific date, the date value you supply must be in the following format regardless of your locale:

Do not use locale-dependent formats such as 4/1/2008.

When you create a filter condition that compares the numeric values in a column to a specific number, use a period (.) as the decimal separator regardless of your locale, for example:

Do not use a comma (,).

For a column that contains string data, you can create a filter condition that compares each value to a string pattern instead of to a specific value. For example, to display only customers whose names start with M, use the LIKE operator and specify the string pattern, M%, as shown in the following filter condition:

You can also use the % character to ensure that the string pattern in the filter condition matches the string in the column even if the string in the column has trailing spaces. For example, use the filter condition:

instead of the filter condition:

The filter condition Country LIKE 'USA%' matches the following values:

The filter condition Country = 'USA' matches only one value:

You can use the following special characters in a string pattern:

To match the percent sign (%) or the underscore character (_) in a string, precede those characters with a backslash character (\). For example, to match S_10, use the following string pattern:

To match 50%, use the following string pattern:

Use a filter condition to compare the values in one column with the values of another column. For example, in a report that displays products, sale prices, and manufacturer suggested retail price (MSRP), you can create a filter condition to compare the sale price and MSRP of each product, and display only rows where the sale price is greater than MSRP.

An expression is any combination of Actuate SQL constants, operators, functions, and information object columns. When you create a filter condition, you can use an expression in Filter by, Value, or both. You create an expression in the expression builder.

For example, in an information object that returns customer and order data, you want to see which orders shipped less than three days before the customer required them. You can use the DATEDIFF function to calculate the difference between the ship date and the required date:

Figure 3‑30 shows this condition in Filter Conditions.

In an information object that returns order data, you want to see which orders were placed today. You can use the CURRENT_DATE function to return today’s date:

Figure 3‑31 shows this condition in Filter Conditions.

In an information object that returns employee data, you want the information object to return only data for the user who is currently logged in to the volume. You can use the LEFT function and the concatenation operator (||) to construct the employee’s user name, and the CURRENT_USER function to return the name of the user who is currently logged in:

Figure 3‑32 shows this condition in Filter Conditions.

When you create a filter, you can define one or more filter conditions. Each condition you add narrows the scope of data further. For example, you can create a filter that returns rows where the customer’s credit rank is either A or B and whose open orders total between $250,000 and $500,000. Each condition adds complexity to the filter. Design and test filters with multiple conditions carefully. If you create too many filter conditions, the information object returns no data.

You use the Filters page, shown in Figure 3‑26, to create one or more filter conditions. To create a filter condition, you choose New and complete the Filter Conditions dialog, shown in Figure 3‑25. When you create multiple filter conditions, the IO Design perspective precedes the second and subsequent conditions with the logical operator AND, for example:

This filter returns only data rows that meet both conditions. Sometimes, you want to create a filter to return data rows when either condition is true, or you want to create a more complex filter. To accomplish either task, use the buttons on the right side of the Filters page, shown in Figure 3‑33.

If you create more than two filter conditions and you use different logical operators, you can use the parentheses buttons to group conditions to determine the order in which they are evaluated. Display the information object output to verify the results.

As you add each filter condition, the logical operator AND is inserted between each filter condition. You can change the operator to OR. The AND operator means both filter conditions must be true for a data row to be included in the information object output. The OR operator means only one condition has to be true for a data row to be included. You can also add the NOT operator to either the AND or OR operators to exclude a small set of data.

For example, the following filter conditions return only the sales representatives working in the New York City office:

The following filter conditions return all employees working in the New York City or London offices:

The following filter conditions return all employees working in the New York City office except sales representatives:

The IO Design perspective evaluates filter conditions in the order in which they appear. You can change the order by selecting a filter condition in Filters, shown in Figure 3‑26, and moving it up or down using the arrow buttons. Filter conditions that you type in the Actuate SQL text box, shown in Figure 3‑27, are preceded by AND and are evaluated last.

If you define more than two conditions, you can use parentheses to group conditions. For example, A AND B OR C is evaluated in that order, so A and B must be true or C must be true for a data row to be included. In A AND (B OR C), B OR C is evaluated first, so A must be true and B or C must be true for a data row to be included.

To illustrate the difference a pair of parentheses makes, compare the following examples.

The following filter contains three conditions and none of the conditions are grouped:

Figure 3‑34 shows the first 10 data rows returned by the information object. Although the filter specifies the countries Australia, France, and USA and sales rep 1370, the data rows display data for other countries and sales reps. Without any grouped conditions, the filter includes rows that meet either conditions 1 and 2 or just condition 3.

The following filter contains the same three conditions, but this time the second and third conditions are grouped:

Figure 3‑35 shows the first 10 data rows returned by the information object.
The Country IN ('Australia', 'France', 'USA') condition must be true, then either the SalesRepNumber = 1370 condition or the CreditLimit >= 100000 condition is true.

You can change any of the conditions in Filters.

To delete a filter condition, in Filters, select the condition. Then, choose Delete. Verify that the remaining filter conditions still make sense.

You can use a parameter to prompt an information object user for a filter value. A parameter enables an information object user to restrict the data rows returned by the information object without having to modify the WHERE clause. For example, for an information object that returns sales data by sales office, instead of creating a filter that returns data for a specific office, you can create a parameter called param_SalesOffice to prompt the user to select an office. The WHERE clause is modified as follows:

You create parameters and define their prompt properties on the Parameters page. Prompt properties include the parameter’s default value, a list of values for the user to choose from, and whether the parameter is required or optional. Parameters appear in the Value drop-down list in Filter Conditions with a : (colon) preceding the parameter name, as shown in Figure 3‑36.

Do not use a parameter in a filter condition with the IN operator, for example:

Actuate SQL parameters can only accept a single value, but the IN operator takes multiple values. Instead, do one of the following for the appropriate column, for example the Country column:

For more information on creating parameters, see Defining parameters.