Filtering data
Filtering enables you to present the information that answers specific business questions, such as which sales representatives generated the top 10 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.
Using Interactive Viewer, you can filter data at the report table level to narrow the scope of data in a table, and also remove unwanted fields of data that show too much information. You can also filter data for report elements such as charts.
Sometimes when you view a report with existing filters, the report requires you to enter parameter values to determine which data to display each time you run the report. You can view specific data in the report by modifying these parameter values.
Creating a filter
To create a filter, you define a condition specifying which data rows to display. A filter condition is an If expression that must evaluate to True for a data row to be included. The following list shows examples of possible filter conditions:
If the credit limit is greater than 100000
If the sales office is San Francisco
If the order date is between 4/1/2008 and 6/30/2008
Figure 7‑23 shows an example of a filter condition. When defining a filter condition, you can break it down into the following parts, as shown in Figure 7‑23:
*The column to evaluate, such as credit limit
*The comparison operator specifying the type of comparison test, such as Equal To
*The value to which all values in the column are compared, such as 100000
Figure 7‑23 Creating a filter
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. Table 7‑1 lists examples of filter conditions.
Table 7‑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
How to create a filter
1 Select and right‑click the column containing the data values to evaluate. For example, to create a filter that displays data rows based on specific values in the credit limit column, select the credit limit column.
2 Choose FilterFilter. Filter appears.
3 In Condition, select the comparison test, or operator, to apply to the selected column. Depending on the operator you select, Filter displays either one or two additional fields, or a completed filter condition.
4 If you select an operator that requires a comparison value, you can specify the value in one of the following ways:
*Type the value.
*Choose Select Values to select from a list of existing data values. Figure 7‑24 shows how you can select a credit limit value of 11000 from the list of possible credit limit values. Use the Previous and Next buttons to navigate the list of values that appears.
5 Select or deselect Recalculate Totals based on whether you want the aggregate data in the report to be modified or not. Choose OK.
Figure 7‑24 Selecting a value for the filter condition
If the report or report element such as a chart contains no data that matches the filter condition, Interactive Viewer does not display any data. Make sure you test the filter by applying it and viewing the resulting report.
When you create a filter condition on a column containing Float or Double data type, the In or Equal to operators do not work as expected. To make sure you obtain the results you expect, do one of the following:
*When using the In operator 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 operator.