Query modifiers

Query modifiers, also called WHERE portions in JReport, are the WHERE clauses in SQL select statements. In JReport, the SQL select statements are called queries. Both queries and query modifiers are stored in the catalog, and query modifiers are saved in the catalog independently (not related to queries). When a report is running, JReport will construct the SQL statement based on the query used in the report and may substitute the WHERE clause in the SQL statement if there is a given query modifier.

The query modifier can be stored in the catalog in two formats: structure and string. In the structure format, the groups of conditions are stored. The query modifier consists of many condition groups and the groups are connected by a logical operator. Each condition consists of many conditions and the conditions are also connected by a logical operator. Each condition consists of a left expression, the relationship operator and a right expression. In string format, the text of the query modifier is stored.

Before running a report, you can specify a query modifier to the JReport system via the Engine Bean, so that you can generate the report on different data.

Adding a query modifier

To add a query modifier, follow the steps below:

1. In JReport Designer, open the catalog to which you want to add the query modifier.
2. In the Data tab of the Catalog Browser, expand the desired data source, then the Relational > connection > Queries node.
3. Right-click the Query Modifiers node and select Add Query Modifier from the shortcut menu. The Query Modifier Editor dialog displays. See the dialog.

   

4. Enter a name for the query modifier in the Name text box and specify its type in the Type drop-down list, Structure or String.
5. Select a query from the Query drop-down list to build the query modifier. You can click the Show Query button to view the query if required.
6. In the editing area, edit the query modifier as required.

   If String type has been selected, a text editor will be shown where you can key in the WHERE clause. If the Structure type has been selected, a condition editor will be displayed where you can construct the query modifier by clicking buttons (for details about how to set the condition, see Filtering with the filter format).

7. Click the Check button to check the validity of the query modifier according to the query.

   The validity of a query modifier is defined as: all the fields and formulas referred to by the query modifier are available to the chosen query. This validation check also ensures that the query modifier will work with the specified query.

8. Click the Show SQL button to view the string of the query modifier.
9. When done, click OK.