Supplementing WHERE portions

JReport allows you to filter a query at runtime. To do this, you can call the method setWherePortion() of JReport Engine Bean. However, by calling this method, the original WHERE portion of the query will be replaced. If you want to append the runtime WHERE portion to the original one instead of replacing it/them, you can do it in the following way:

  1. Open the demo catalog SampleReports.cat, then in the Catalog Browser, expand the desired data source and create a type-in parameter named p_Portion of String type (leave the other settings to their default).
  2. In the same data source, create a query CustomersInfo on the table Customers. Select all the fields contained in the table.
  3. Create a table report based on the query, and have the fields Customer ID, Customer Name, City and Phone displayed in the report.
  4. In the Resource View panel, right-click the node that represents the query, and then select Edit Query from the shortcut menu.
  5. In the Query Editor, click Query > Filter.
  6. In the Search Condition dialog, add two condition lines (WHERE portions) for the query as follows (for details, see Filtering with the filter format):

    The colon is used to append the parameter value (of String type) to the first condition row instead of replacing it.

  7. Click OK to apply the change to the query.
  8. View the report, and in the Enter Parameter Values dialog, write a WHERE portion in standard SQL syntax as value of p_Portion, for example, CUSTOMERID<20. Then, both of the two WHERE portions will be used to filter the query.