Query modifiers, also called WHERE portions in Logi Report, are the WHERE clauses in SQL select statements. In Logi Report, 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 run using a Java application that uses the Engine Bean, the Logi Report aPI 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 Logi Report system via the Engine Bean, so that you can generate the report on different data. You cannot run the report from the standard Logi Report Server user interface.
To add a query modifier in a catalog:
- Open the catalog to which you want to add the query modifier.
- In the Catalog Manager, expand the desired data source and then the Queries node.
- Right-click the Query Modifiers node and select New Query Modifier from the shortcut menu. The Query Modifier Editor appears.
- Type a name for the query modifier in the Name text box and specify its type in the Type drop-down list, Structure or String.
- Select a query from the Query drop-down list to build the query modifier. You can select the Show Query button to view the query.
- 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 type 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 selecting buttons (for details about how to set the condition, see Filtering with the filter format).
- Select 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.
- Select the Show SQL button to view the string of the query modifier.
- Select OK to create the query modifier.