Multiple datasources in iReport for lists, tables, and subreports

Introduction

In iReport, you can set up multiple datasources within the same report. This is especially useful when dealing with table, list and subreport components, which can use their own subdatasets.

Using different JDBC Connection for subreport, list and table components

A subreport, table or list component can use a different JDBC connection than the main report. Normally the $P{REPORT_CONNECTION} parameter is used to pass the main report's database to the subreport, list or table component. Since we want to use a different database connection we need to create a parameter that will create a new database connection.

Create a separate parameter, and give it a name for the second database connection. Set the type to java.sql.Connection. Note that you may have to manually enter the datatype if it isn't available in the drop down menu. Uncheck the 'Use as prompt' box.

You'll now define the JDBC URL in the default value expression for that parameter. Use the following expression to create the jdbc connection:

java.sql.DriverManager.getConnection("jdbc:postgresql://dbhost:5432/database","dbusername","dbpassword") // Modify this to match your datasource

When Using the Subreport component

From the palette in the main report, drag the subreport element onto your report. The subreport wizard will appear. On the first page, choose the path to your subreport and click 'next'.

On the second page, select 'don't use any connection or datasource' and click 'next'. Click 'next' through the parameters section on the third page.

Choose an option for your subreport expression and click 'Finish'.

Click on the subreport element to highlight it and edit the Parameters property by clicking the (...) button. Add the following parameters:

SUBREPORT_CONNECTION = $P{SUBREPORT_CONNECTION}

If you recive a driver error when trying to run the report in iReport, you may need to copy your database driver jar file to your jdk_version/jre/lib/ext directory. When deploying to JasperServer, make sure your subreport is added as a resource to the main report and that the main report correctly refers to the subreport in the subreport expression with the repo syntax ("repo:subreport.jrxml").

Using List or Table components

List and Table components are useful when working with subdatasets, which can have their own queries separate from the main report's query, as well as their own datasources.

Table components

When dragging over a table component to one of the report's bands, iReport will prompt you for the subdataset it is for. After choosing, it will then prompt your for the fields you want, and finally the datasource to use. Once you've got the Table component in the design area, you can edit the table datasource (by right-clicking on the component).

• In the "Connection/Datasource Expression" tab, set the drop-down to "Don't use connection or datasource"
• In the "Parameters" tab, add a new parameter:
  • Set the "Dataset parameter name" to REPORT_CONNECTION
  • Set its corresponding Value Expression to the parameter containing the database connection statement

List components

When dragging over a list component to one of the report's bands, iReport will not prompt you for anything, unlike the process for the table component. You'll have to configure it once it's on the design area. Once the List component is in the design area, you can edit the table datasource (by right-clicking on the component).

• In the "Connection/Datasource Expression" tab, set the drop-down to "Don't use connection or datasource"
• In the "Parameters" tab, add a new parameter:
  • Set the "Dataset parameter name" to REPORT_CONNECTION
  • Set its corresponding Value Expression to the parameter containing the database connection statement

Once you have that ready, you will be ready to drag fields, values or parameters for that subdataset over to the List element's area.

Troubleshooting

We have noticed that on some systems, we need to change the report language from "Groovy" to "Java" for the above process to work. It seems like it's an intermittent process.