When your language is SQL, the Query Builder is called the SQL Builder. The tool requires a JDBC data adapter.

The builder is divided in two parts. On the left a tree-view shows all the available schemas and relative objects like tables, views, etc. found by using the JDBC connection provided by the data adapter. On the right side there are three tabs which present the query in different ways.

The first Text tab contains a text area in which you can write a query. Tables and other objects can be dragged from the metadata view into the text area, so you don't have to write the entire qualified name of that objects. Although the SQL builder does not support arbitrary complex queries (which may use database-specific syntax), this text area can be used for any supported query, included stored procedures if supported by the report query executer.

If a query has been already set for the report, this text area shows it when the query dialog is open.

The Outline and Diagram tabs are used to visually build the query. The current version of Jaspersoft Studio does not support back-parsing of SQL. For this reason, Outline and Diagram editing mode should be used only to create new queries, otherwise the new query replaces any existing query. If you do attempt to overwrite an existing query, Jaspersoft Studio alerts you.

Query Outline View and Diagram View

The purpose of SQL is to select data from the tables of the database. SQL allows you to join tables, so that you can get data from more than one table at a time.

The Outline view is a good tool for people that have basic understanding of SQL. It works in conjunction with the Diagram view, which represents the most simple way to design a query. In the outline view the query is split in its main parts introduced by the relative keyword. Those parts include:

SELECT introduces the portion of query where are listed all the columns that form a record of the final result set (which is basically a list of records).

FROM contains the list of tables involved in our queries and if specified the rules to join that tables.

WHERE is the portion of query that describes the filters and conditions that the data must satisfy in order to be part of the final result, conditions can be combined by using the OR and AND logical operators and can be aggregated by using parentheses.

GROUP BY indicates a set of fields used to aggregate data and is used when an aggregation function is present in the SELECT. For example, the following query counts the number of orders placed in each country.

HAVING works in a similarly to WHERE, but it is used with aggregate functions. For example the following query filters the records by showing only the countries with at least 40 orders:

ORDER BY specifies a set of columns to be used to sort the result set.

The Diagram view shows the tables in the query with the relative join connections and the selected fields. This view is very useful especially to select the relevant fields and easily edit the table joins by double-clicking the connection arrows.

Selecting columns

Columns can be dragged from the database explorer to the outline view into the SELECT node or other nodes. From there they can be used like the ORDER BY node. When selecting a column, be sure the table it comes from is present in the FROM part of your query.

Fields can also be selected in the diagram view by selecting the relative checkbox.

Finally you can add a column by right-clicking the SELECT node in the outline view and selecting Add Column. A new dialog prompts you to pick the column from the ones available, which are the columns present in all tables mentioned in the FROM clause. In case the column to add is not a table column, but a more complex expression, for instance an aggregation function like COUNT(*), add it by right-clicking the SELECT node in the outline view and selecting Add Expression.

When a column is added to the query as part of the SELECT section, it is possible to set an alias for it by double-clicking it.

Aliases are useful when you have several fields with the same name coming from two different tables.

Joining tables

Tables added to the FROM clause or dragged inside the Diagram view can be joined by selecting shared fields. In the Diagram view the relationship can be created by dragging a column of the first table onto the column of the table to join. This type of join can be edited by double-clicking the join arrows. Currently a single join condition is supported.

Joins can also be edited by the outline view by right-clicking a table name and selecting Add or Edit Table Join.

Data selection criteria (WHERE conditions)

A WHERE clause allows you to specify the criteria that must be satisfy by each record in order to be part of the query result. Right click the WHERE node in the outline view and select Add Condition to add a new condition.

If you know in advance which column should be involved in the condition, you can drag this column on the WHERE node to create the condition. In both cases the condition dialog appears.

Many conditions can be organized by creating condition groups and combined by using OR or AND operators. OR groups require that at least one condition is true, while AND requires that all the conditions of the group are verified. By double-clicking the group operator it is possible to change the value from AND to OR and vice versa. Groups may be formed by nested groups in a recursive way.

If the value of a condition is not fixed, it can be expressed by using a parameter with the expression: $P{parameter_name}. In very few cases it could also be useful to use the $P!{} syntax instead, but in general this is not the case when creating condition values, since by using $P!{} there is no escape of the parameters, making the query vulnerable to potential security threats.

When using parameters of type collection, the $X{}expressions allow you to use operators like IN and NOT IN, which test if a value is present or not in the provided list. $X{} syntax allows also the use of other operators like BETWEEN that can be used with numbers, Date Range and Time Range type of parameters.

Acquiring fields

Once the query is ready, it's time map the columns of the result set to fields of the report. When using SQL, this is a pretty straight forward operation: by pressing the button Read Fields the query is executed, and the metadata is read; if the query is valid and no errors occurred, for each column a field is added to the fields list with the proper class type.

This is also a good way to test if a query contains syntactical errors. In general, if the query has been created by using the designer, it should always be valid.

Data Preview

The Data Preview tab allows you to generate a ghost report having the fields map to the fields tab and current query by using the selected Data Adapter. This tool works independently of the query language and is a good way to debug a query or check which records the dataset returns.