Using the Dataset and Query Dialog

To open the Dataset and Query dialog, click the Dataset and Query icon .

When working with a sub-dataset, open the dialog by right-clicking the dataset name inside the Outline view, and selecting Dataset and Query....

Right-Click Menu

The Dataset and Query dialog allows you to:

Select a data adapter with which to configure the dataset. Usually a data adapter is selected, but it is possible to change it if necessary.
Select a query language for the dataset being edited (which can be the main dataset or a sub-dataset used to populate a chart or a table).
Specify the query by typing text or by using a tool, if available, designed for the selected language. A tool is available for several languages including SQL, XPath and JSON.
Retrieve the fields from the selected Data Adapter: these can be provided directly by the Data Adapter or by executing the query and reading the response metadata.
Add, edit, and remove fields and parameters.
Edit sort options.
Provide an expression to filter dataset records.
Preview your data, if supported by the selected data adapter.

Data Preview Tab

Defining all the fields of a report by hand can be tedious, especially when there are a lot of fields. JasperReports requires all report fields to be named and configured with a proper class type. The main purpose of the Dataset and Query dialog is to simplify this process by automatically discovering the available fields provided by a data adapter without using a query or by the data which comes from the execution of a query. To execute a query it is important to use the proper data adapter: for example when using SQL as language, in order to execute a SQL query you must use a JDBC data adapter to connect to the database. The Read Fields button starts the discovery process: the fields found are listed in the Fields tab and added to the report. Close this dialog by clicking the OK button.

Some query languages, like XPath, do not produce a result that resemble a table, but more complex structures which requires extra steps to correctly map result data to report fields. An example of this is the multidimensional result coming from MDX results (MDX is the query language used with OLAP and XML/A connections): in this case each field is mapped by specifying an expression stored in the field description. For some of languages, such as instance XPath and JSON query, the Query dialog displays a tool that simplifies the creation of both the query and the mapping.