Creating a filter
A filter condition is an If expression that must evaluate to True in order to include a data row. 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/2010 and 6/30/2010
Figure 10‑1 shows an example of a condition defined in Filter. As the illustration shows, Filter helps you define the condition by breaking it down into the displayed parts.
Figure 10‑1 Filter displaying a filter condition
Specifying filtering options
You can filter data at the data set level and at the table level. Filtering at the data set level narrows the scope of data retrieved from the data set and can improve design‑time performance if the data set contains a large amount of data. Filtering at the table level narrows the scope of data displayed in a BIRT design and is the typical filtering option. If a BIRT design contains filters at both the data set and table levels, Report Studio executes the filter at the data set level first, then at the table level. Design the filters accordingly.
When creating a filter condition, users can specify whether or not the aggregate data in the BIRT design is recalculated to meet the filter condition. This feature is useful when comparing the filtered data values with the unfiltered totals, for example, when performing a percentage calculation of the unfiltered aggregate totals.
When you create a filter condition on a column containing Float or Double data type, the In, Not In, Equal to, and Not Equal to operators do not work as expected in Java-based applications. To make sure you obtain the results you expect, do one of the following:
*When using the In or Not In operators in a filter condition specified on a computed column, make sure you round the values in the column to a specified number of digits. For example, the following expression rounds the value obtained by three decimal places:
ROUND([dbo_ITEMS:PRICEQUOTE]*[dbo_ITEMS:QUANTITY]*0.001, 3)
*Use the Between operator in filter conditions in place of the Equal to or Not Equal to operator.
Table 10‑1 lists examples of filter conditions.
Table 10‑1 Examples of filter conditions
Type of filter condition
Usage
Example
Multiple comparison values
Returns columns that are valid for more than one comparison value.
Country In USA, UK, France, Japan
Empty or blank values
Tests if a field contains a value or not.
E‑mail Is Null, Email Is Not Null
Excluding data
Excludes data that fulfills the condition.
Country Not In USA, UK, France, Japan
Order Amount Not Between 1000 and 5000
Product Code Not Like 'MS%'
Top or Bottom 'n' values
Tests if the column value is within the top or bottom 'n' values.
Use at the report table level or at the group level.
Use the Top/Bottom N dialog box to modify a group level filter.
Order Amount Top N 50
Median Price Bottom Percent 10
Comparing to a date value
Compares the date‑and‑time values in a column to a specific date.
The value supplied must be in one of the following formats, regardless of your locale:
3/26/2008
3/26/2008 2:30:00 PM
Use the calendar icon to select a date.
Order Date Equal To 3/26/2008
Shipped Date Equal To 3/26/2008 2:30:00 PM
Comparing to a string pattern
Tests if each string value in the column matches a string pattern.
Customer Like M%
Comparing to a value in another column
Tests values in one column against values in the specified column.
Price>MSRP