When you create a JDBC data set, you use a SQL SELECT statement to specify which rows to retrieve from the database. To select only rows that meet certain criteria, add a WHERE clause to the SELECT statement. The WHERE clause consists of the keyword WHERE, followed by a search condition that specifies which rows to retrieve.

For example, the following statement returns only customers from the USA:

SELECT *
FROM Customer
WHERE country = 'USA'

As another example, the following statement returns only customers from USA and whose credit limit exceeds $10,000.00:

SELECT customerName
FROM Customer
WHERE country = 'USA'
AND creditLimit > 10000

In the following example, the statement returns all customers from USA or Canada:

SELECT customerName
FROM Customer
WHERE country = 'USA'
OR country = 'Canada'

How to filter the rows to retrieve from a JDBC data source

This procedure assumes that you have already created a JDBC data set using a SQL query or stored procedure.

Table 15-1 describes the types of filter conditions and provides examples of filter conditions that are used in WHERE clauses.

quantity = 10
custName = 'Acme Inc.'
custName > 'P'
custState <> 'CA'
orderDate > {d '2005-06-30'}

price BETWEEN 1000 AND 2000
custName BETWEEN 'E' AND 'K'
orderDate BETWEEN
   {d '2005-01-01'}
   AND {d '2005-06-30'}

officeCode IN (101,103,104)
itemType IN ('sofa',
   'loveseat', 'endtable',
   'clubchair')
orderDate IN
   ({d '2005-10-10'},
   {d '2005-10-17'})

custName LIKE 'Smith%'
(% matches zero or more
characters)
custName LIKE 'Smiths_n'
(_ matches one character)
custState NOT LIKE 'CA%'

manager IS NULL
shipDate IS NULL
shipDate IS NOT NULL

SQL provides many other operators and options that you can use to create more complex search conditions. For more information about the WHERE clause, see the SQL documentation for your JDBC database.

When you create an XML data set, you specify what data to retrieve from an XML data source by mapping XML elements and attributes to data set columns. To map an XML element or attribute to a column, you specify an XPath expression. If you are not familiar with XML, XPath is a query language for accessing parts of an XML document. Figure 15-2 shows an example of column mappings defined in an XML data set and the data rows that BIRT returns.

To select only data that meets certain criteria, specify the value to search in the XPath expression that was used to map the column. The following XPath expression, for example, specifies that only rows where the author’s name is Sally Bush should be retrieved:

   author[@name="Sally Bush"]

Figure 15-2. Column mappings and the data rows returned for an XML document

When filtering data with an XPath expression, observe the following limitations:

If you need more advanced filtering capabilities, use BIRT Report Designer’s filter tool, which is described later in this chapter.

How to define a filter on row retrieval for XML data sets

There are three places in BIRT you can filter data. You can create a filter in any or all of these places:

The first opportunity to filter data in BIRT is on the data set level. Use this technique if only one report element uses the data set or if you want all report elements that use the data set to use the same set of rows.

Next, you can filter on a report element. You can edit the report element filter properties to specify conditions for displaying only certain data rows. Use this technique if multiple tables, lists, and charts use the same data set, but you want each report element to display a different set of rows.

For example, you create a data set that returns data for all customers in the USA. You use this data set for two elements, such as a table and a list. You can specify a different filter condition for each element to limit further the rows to display. The table element, for example, can filter the rows to display only customers from California. The list element can filter the rows to display only customers from New York. Figure 15-4 illustrates this concept.

Figure 15-4. Filters applied to a data set, a table, and a list

Finally, you can filter on a group of data. If you group data in a table or list, you can edit the filter properties of each group. Filter at the group level if a table or list displays rows in groups and you want to display only certain groups. For example, a sales report groups orders by customer. Rather than showing data for all customers, you can specify a filter to display only customers that have order totals above a certain amount or specify a filter to display only the top three customers.

Figure 15-5 compares three reports that use the same data set but different group filters. The first report shows all customer groups. The second report uses a filter at the customer group level to show only customers whose order totals exceed $100,000.00. The third report uses a filter at the customer group level to show the top three customers with the highest order totals.

Figure 15-5. Three reports using the same data set, but displaying different results, because of filters applied on the group level

You can specify filter conditions at all three levels if your report design needs it. Filtering at each level serves a different purpose, can yield different results, and can have different rules. Use the following guidelines to help you decide where to filter data for a report:

Just as with the data source filtering capabilities, you can design different types of filter conditions with BIRT Report Designer depending on how you want to search for data rows. For example, you can specify that BIRT returns rows when the value of a particular field matches a specific value, when the field value falls within a range of values, when the field value matches a string pattern, or when the field value is null.

The filter tool displays operators as English words instead of the actual operators. For example, the tool displays Equal, Larger than, Larger than or Equal, and Not Equal, instead of ==, >, >=, and !=. Table 15-2 describes the types of filter conditions that you can create with the filter tool. The table also contains numerous examples of expressions you can create using the operators. Most operators can be used with different data types. You should be aware that the filter tool provides two pattern-matching operators: Like and Match. The Like operator enables users who are familiar with SQL to specify pattern-matching expressions using SQL syntax. The Match operator enables users who are familiar with JavaScript to specify pattern-matching expressions using JavaScript’s regular expression syntax.

row["quantity"] Less than 10
row["custName"] Equal "Acme Inc."
row["custName"] Larger than or
  Equal "P"
row["custState"] Not Equal "CA"
row["orderDate"] Less than or
  Equal "06/30/05"

row["manager"] Is Null
row["shipDate"] Is Not Null

The procedure for creating a filter condition is the same whether you create it at the data set, report element, or group level. The difference is in how you access the filter tool.

When you create a filter condition, you specify the following information:

You can create more complex filter conditions that include JavaScript functions or scripts. For example, you can specify calculated values for the expression and value portions of the filter. The following example shows a multiline expression in the expression part. The expression returns a customer’s first name from the customerName field, which stores full names:

spaceCharPosition = row["customerName"].indexOf(" ");
stringToGet = row["customerName"]substr(0, spaceCharPosition);

If you combine this expression with the Equal operator and specify a value of “John”, the filter condition extracts the first name from the customerName field, compares the first name to John, and returns only rows where this condition is true.

The expressions and values that you specify in a filter condition in BIRT Report Designer must use JavaScript syntax. If you filter data using both the SQL query and BIRT Report Designer’s filter tool, be careful not to confuse SQL syntax with JavaScript syntax when specifying the filter condition.

It is easy, for example, to confuse the use of single quotation marks (' ') and double quotation marks (" "). SQL requires single quotation marks for string and date constants, but JavaScript requires double quotation marks. Another example is the comparison operator. You use = for SQL and == for JavaScript.

How to filter at the data set level

How to filter at the report element level

These instructions assume you already created a report that uses a table to display data from a data set.

How to filter at the group level

These instructions assume that you have already created a table that displays data from a data set, and created a group or groups to organize the data.

The filter tool enables you to create any number of conditions for filtering data. BIRT evaluates each condition and includes only data rows that meet all the conditions. For example, assume the following two conditions were created with the filter tool:

row["orderTotal"] Larger Than 10000
row["country"] Equal "USA"

In this example, BIRT includes a row only if the value in the orderTotal field is greater than 10000 and the value in the country field is equal to USA. In other words, creating two filter conditions is equivalent to specifying the following JavaScript expression:

   row["orderTotal"] > 10000 && row["country"] == "USA"

The following rows meet the specified filter conditions:

Country   Order ID  Order Total

USA       1010      15000
USA       1035      18500
USA       1155      25000
USA       1200      12000
USA       1455      20500

If you want to return a row if it meets any one of multiple conditions, create a single filter condition that uses the OR (||) operator to combine multiple conditions. For example, to include a row where either orderTotal exceeds 10000 or country is USA, create an expression that compares to true, as follows:

   row["orderTotal"] > 10000 || row["country"] == "USA" Is True

For expressions that compare to true or false, you must use the comparison operator. As the previous example shows, you use ==, not the assignment operator, =. Figure 15-13 shows how the filter condition is specified in the filter tool. Note that you select Is True from the list of operators under Operator.

Figure 15-13. Filter condition that uses the OR operator

In this example, the following rows meet the specified filter condition:

Country   Order ID  Order Total

Belgium   1020      21000
France    2005      14500
USA       1425       5000
USA       1750       7500
USA       1010      15000
USA       1035      18500
USA       1155      25000
USA       1200      12000
USA       1455      20500