Registration of Fields from a SQL Query

The most common way to fill a report is by using a SQL query. Jaspersoft Studio provides several tools to work with SQL, including a query designer and a way to automatically retrieve and register the fields derived from a query in the report.

Before you open the query dialog, pay attention to the active connection/data source. All operations performed by the tools in the query dialog use this data source, so ensure that you select the correct connection/data source. Open the query dialog (“Query Dialog”) by right-clicking the name of your report in the Outline view and choosing Dataset and Query....

Query Dialog

Jaspersoft Studio does not need you to define a query in order to generate a report. It can obtain data records from a data source that is not defined by a query execution. JasperReports supports a number of query languages including:

CQL
HiveQL
JSON
MongoDBQuery
PLSQL
SQL
XLS
XPath

If the selected data source is a JDBC connection, Jaspersoft Studio tests the access connection to the data source as you define the query. This allows Jaspersoft Studio to identify the fields using the query metadata in the result set. The design tool lists the discovered fields in the bottom portion of the window. For each field, Jaspersoft Studio determines the name and the Java type specified for that field by the JDBC driver.

A query that accesses one or more tables containing a large amount of data may require a long delay while Jaspersoft Studio scans the data source to discover field names. You may want to disable the Automatically Retrieve Fields option in order to quickly finish your query definition. When you have completed the query, click the Read Fields button in order to start the fields discovery scan.

All fields used in a query must have a unique name. Use alias field names in the query for fields having the same name.

In case of an error during the query execution (due to a syntax error or to an unavailable database connection), an error message appears instead of the fields list.

The field name scan may return a large number of field names if you are working with complex tables. I suggest that you review the list of discovered names and remove any fields that you are not planning to use in your report, in order to reduce unnecessary complexity.When you click the OK button all the fields in the list are included in the report design. You can also remove them later in the outline view, but it’s a good idea at this point in the design process to remove any field names that you won’t be using.

Version: 
Feedback
randomness