Inserting a parameter marker in the SQL query
After creating a report parameter, insert a parameter marker in the WHERE clause of the SQL query of the data set. The parameter marker, represented by a question mark (?), indicates where you want BIRT to insert the parameter value.
For example, to ask the user to specify the threshold inventory quantity for a restocking report, insert the ? parameter marker in the WHERE clause, as shown in the following example:
WHERE quantityinstock < ?
When the report runs, if the user specifies 500 for quantityinstock, BIRT replaces ? with 500 and sends the following filter condition to the data source:
WHERE quantityinstock < 500
If you write a filter condition that uses more than one field, consider the interaction of the field values. Each of the following WHERE clauses, for example, returns a different set of rows:
WHERE quantityinstock < ? AND productvendor = ?
WHERE quantityinstock < ? OR productvendor = ?
The first clause returns only those rows in which both the quantityinstock and the productvendor values match the values that replace the ? markers. The second clause returns more rows. It returns rows in which the quantityinstock value is less than the value that replaces the quantityinstock ? marker and rows in which the productvendor value matches the value that replaces the productvendor ? marker.
Before completing all of the steps to enable filtering, test the filter conditions by specifying actual values in the WHERE clause to verify that the results meet your expectations.
SQL supports many options and operators for specifying filter conditions. For complete information about writing WHERE clauses and SQL statements in general, consult a book about SQL.
How to insert a parameter marker in the SQL query
This procedure assumes that you already created a data set.
1 In Data Explorer, right-click the data set for which you want to edit the query, then choose Edit. Edit Data Set displays the query.
2 Add a WHERE clause with one or more parameters, as shown in the following examples:
WHERE quantityinstock < ?
WHERE quantityinstock <= ? AND productvendor = ?
WHERE quantityinstock <= ? OR productline LIKE ?
Figure 13‑3 shows an example of a query with two parameter markers.
Figure 13‑3 Query with two parameter markers
At this point, you are ready to define a data set parameter for each ? parameter marker. The next section describes this task.
