3.1.1. SQL Dataset

SQL dataset is produced as result of SQL query execution. It is recommended to use aliases for query result fields with the help of the as operator. It is also recommended to enclose the aliases in double quotes to prevent possible case conversion by the DBMS:

select u.name as "userName", u.login as "userLogin"
from sec_user u

You can use report input parameters and parent bands fields in the query. Parameters should be addressed by name enclosed in ${}, for example, ${dateFrom}. Parent band fields should be addressed similarly, by adding the band name in front of the field name: ${band1.field1}.

Below is an example of an SQL query with a groupId parameter, obtained from the group parent band and an external active parameter:

select u.name as "userName", u.login as "userLogin"
from sec_user u
where u.group_id = ${group.groupId}
    and u.active = ${active}
    and u.delete_ts is null
Warning

You should manually include conditions to filter soft deleted records for SQL queries.

By default, SQL queries are executed on the main database. If you want to query an additional data store (see Developer’s Manual), set its name in the Data store field.

Query preprocessing in bands

If you need to modify the SQL/JPQL query dynamically depending on the report input parameters, or the parameter values from the parent band, you can use the SQL preprocessing. The template engine enables you to modify SQL/JPQL queries using Groovy. In order to activate it, check the Preprocess query as Groovy template checkbox below the band editor. The resulting query will be processed by GStringTemplateEngine that will have access to:

  • the report parameters: ${<parameter_name>},

  • values from parent bands: ${<band_name>.<parameter_name>}.

Let’s consider the following example: depending on whether the createTs2 report parameter is passed you need to select one of the query conditions: e.create_ts < ${createTs2} or e.create_ts < current_timestamp.

In this case the query should look like:

select e.create_ts, e.id, e.vin from ref_car e
where
e.create_ts >= \${createTs1}
and
<% out << (createTs2 != null  ? 'e.create_ts < ${createTs2}' : 'e.create_ts < current_timestamp')%>

Thus, if the createTs2 parameter is not passed, the initial query will be transformed into the following resulting query:

select e.create_ts, e.id, e.vin from ref_car e
where
e.create_ts >= \${createTs1}
and
e.create_ts < current_timestamp

If createTs2 is passed, the following resulting query will be used for the band:

select e.create_ts, e.id, e.vin from ref_car e
where
e.create_ts >= \${createTs1}
and
e.create_ts < ${createTs2}