Queries

Relational databases typically contain many rows of data, with each row constituting a separate record. Most relational database queries retrieve only a portion of the records contained in a table. The WHERE clause qualifies the query command statement to limit the data to specific records from the tables.

Queries are a higher-level object in a catalog. The concept is similar to that of views in the database but they are stored in the catalog file rather than the database itself. A query can mash up multiple data resources from different connections including tables, views, synonyms, imported SQLs, stored procedures, user defined data sources and other existing queries and create distributed joins among the data resources. Data mash-up makes it possible for multiple separated application systems in your enterprise to provide data in decision making. You can still build queries when you do not have a database connection. The data resources added in a query are all treated the same as tables. In this way, a query is independent from the raw database since JReport uses a mapping name that is unique instead of the table.column syntax of SQL.

JReport Designer provides you with an interactive query designer - the Query Editor, to build any queries as you like. However, only the queries built on tables, views, and synonyms from JDBC connections can support all of the functions. If a query mashes up multiple data resources, only part of the Query Editor functions are supported for it. JReport queries support a limited set of SQL 92 functions that are common to all RDBMS systems. Each vendor has many extensions to these basic functions and if you want to use them, it is best to use either imported SQL or stored procedures.

When you build a query using the Query Editor and do not add features JReport doesn't know how to parse, you can take advantage of Push Down Group Query to allow JReport to add aggregation functions and a GROUP BY clause to specify the groups required based on the groups in the component being used. This is a big performance advantage when you use queries instead of imported SQLs, stored procedures and other query types.

You can use queries to view, change and analyze data in different ways, and JReport can help you with the building of various professional reports based on queries. When making a query, you can place criteria or restrictions on the data to extract only the required data from the database. For example, instead of having to view all the customers of your company, you can view just the customers from Japan. To do this, you create a parameter that is passed to the query where the user can specify conditions that limit the results such as Country field = "Japan".

See an example: The SampleComponents catalog, included with JReport Designer, contains reports that have examples of how queries can be used in a report. For the query example, open <install_root>\Demo\Reports\SampleComponents\Query.cls.