Working with the Query Builder

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

The builder has 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 there are three tabs that present the query in different ways.

The first Text tab contains a text area for writing a query. You can drag tables and other objects from the metadata view into the text area, so you don't have to write the entire qualified names of those objects. Although the SQL builder doesn't support arbitrary complex queries (which may use database-specific syntax), this text area can be used for any supported query, including stored procedures if supported by the report query executer.

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

Use the Outline and Diagram tabs to visually build the query. The current version of Jaspersoft Studio doesn't support back-parsing of SQL. For this reason, you should use Outline and Diagram editing mode 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 with a basic understanding of SQL. It works in conjunction with the Diagram view, which represents the simplest 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.

SELECT

	count(*) as number_of_orders,

	Orders.country

FROM

	Orders

GROUP BY

	Orders.country

HAVING works a bit like a WHERE, but it's used with aggregate functions. For example the following query filters the records by showing only the countries that have at least 40 orders:

ORDER BY specifies a set of columns for sorting the result set.

SELECT

	count(*) as number_of_orders,

	Orders.country

FROM

	Orders

GROUP BY

	Orders.country

HAVING

	count(*) > 40

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

You can drag columns from the database explorer into the SELECT node or other nodes in the outline view. Make sure the columns you select are from tables present in the FROM part of your query.

You can also select fields by their check boxes in the diagram view.

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 those in the tables mentioned in the FROM clause. If a column you want 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, you can 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

You can join tables you've added by selecting shared fields. You can create the relationship the Diagram view by dragging a column of the first table onto the column of the table you're joining to. You can edit this type of join by double-clicking the join arrows. Currently a single join condition is supported.

You can also edit joins in the outline view by right-clicking a table name and selecting Add or Edit Table Join.

Data Selection Criteria (WHERE Conditions)

Use a WHERE clause to specify the criteria to be for each record's part in 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 is displayed.

You can organize conditions by creating condition groups, and then combining them with or and and operators. At least one condition must be true for an OR group. All conditions must be true for the AND operator. You can double-click to change the value of either of these group operators.

If the value of a condition is not fixed, you can express it by using a parameter with the expression: $P{parameter_name}.

When using collection type parameters, the $X{}expressions allow you to use operators like IN and NOT IN, which determine whether a value is present in the provided list. The $X{} syntax also allows you to use other operators like BETWEEN for numbers, Date Range and Time Range type of parameters. See Expression Operators and Object Methods for more information about the $X{} syntax.

Acquiring Fields

When your 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.

Press the Read button to execute the query. If the query is valid and no errors occur Jaspersoft Studio adds a field for each column with the proper class type to the fields list.

Data Preview

Use the Data Preview tab to generate a ghost report that maps the fields to the fields tab using your query and selected Data Adapter. This tool is independent of the query language and a good way to debug a query or check which records the dataset returns.