How to create a filter condition using Actuate SQL
1  
2  
*
*
Type the filter condition using Actuate SQL, as shown in Figure 3-29. If a table or column identifier contains a special character, such as a space, enclose the identifier in double quotation marks (").
Figure 3-29  
Selecting multiple values for a filter condition
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-30 shows the selection of London and Paris from a list of sales office values.
Figure 3-30  
Excluding data
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:
OrderAmount NOT BETWEEN 1000 AND 5000
The filter condition in the next example excludes products with codes that start with MS:
ProductCode NOT LIKE 'MS%'
Filtering empty or blank values
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 quotes 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:
email IS NOT NULL
The following filter condition displays only rows where the e-mail column contains null values:
email IS NULL
When filtering blank values, use the NOT LIKE operator with '' (two single quotes without spaces) as the operand. For example, to exclude rows with blank values in an e-mail column, specify the following filter condition:
email NOT LIKE ''
Conversely, to display only rows where the e-mail column contains blank values, create the following condition:
email LIKE ''
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:
email IS NOT NULL AND email NOT LIKE ''
Specifying a date as a comparison value
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:
TIMESTAMP '2008-04-01 12:34:56'
Do not use locale-dependent formats such as 4/1/2008.
Specifying a number as a comparison value
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:
123456.78
Do not use a comma (,).
Comparing to a string pattern
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:
Customer LIKE 'M%'
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:
Country LIKE 'USA%'
instead of the filter condition:
Country = 'USA'
The filter condition Country LIKE 'USA%' matches the following values:
'USA'
'USA   '
'USA     '
The filter condition Country = 'USA' matches only one value:
'USA'
You can use the following special characters in a string pattern:
*
% matches zero or more characters. For example, %ace% matches any value that contains the string ace, such as Ace Corporation, Facebook, Kennedy Space Center, and MySpace.
*
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:
S\_10
To match 50%, use the following string pattern:
50\%
Comparing to a value in another column
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 MSRP (Manufacturer Suggested Retail Price), 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.
How to compare to a value in another column
1  
2  
3  
4  
5  
Figure 3-31 shows an example of a filter condition that compares the values in the priceEach column with the values in the MSRP column.
Figure 3-31  
Choose OK.
Using an expression in a filter condition
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:
DATEDIFF('d', shippedDate, requiredDate) < 3
Figure 3-32 shows this condition in Filter Conditions.
Figure 3-32  
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:
orderDate = CURRENT_DATE( )
Figure 3-33 shows this condition in Filter Conditions.
Figure 3-33  
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 Encyclopedia 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:
LEFT(firstName, 1) || lastName = CURRENT_USER( )
Figure 3-34 shows this condition in Filter Conditions.
Figure 3-34  

Additional Links:

Copyright Actuate Corporation 2012