Jaspersoft Report using two separate data sources.

Normally $P{REPORT_CONNECTION} parameter is used to pass the main report's database to the subreport for the same report connection. A subreport can use a different JDBC connection than the main report. 

There are several ways to accomplish this requirement:

Why this approach: One of our customers did not want to expose connecting string or username password information in the .jrxml file nor they do not want to use the data adapter approach.

In this approach, we need to create two parameters to create a new database connection. I will detail the steps below.

For this wiki, I am using two different JDBC data sources for my report one is a sample database that comes with TIBCO Jaspersoft sample database “SugarCRM” it’s a PostgreSQL database and the other is an MSSQL database.

Here are the steps I followed:

Create a report in Jaspersoft Studio using “Sugarcrm” with the following query:

SELECT id,

                date_entered,

                name,

                modified_user_id,

                date_modified

FROM accounts

Read fields, preview the report, and run the report.

Create two parameters in this report:

1. $P{DATA_SOURCE_PATH}

Class: com.jaspersoft.jasperserver.api.metadata.jasperreports.domain.JdbcReportDataSource

Default value expression:

com.jaspersoft.jasperserver.api.engine.jasperreports.util.RepositoryUtil.getThreadRepositoryContext().getRepository().getResource(null,"/public/Samples/Data_Sources/DB_MSSQL")

Here “/public/Samples/Data_Sources/DB_MSSQL” is the path to the JDBC data source that is created on the Jaspersoft

Server. (Right click go to properties and copy the path of the JDBC data source from Jaspersoft server)

Attached a screenshot for your reference below:

Screenshot of the param below:

2. $P{SUBREPORT_CONNECTION}

class: java.sql.Connection

default value expression: “java.sql.DriverManager.getConnection($P{DATA_SOURCE_PATH}.getConnectionUrl(),$P{DATA_SOURCE_PATH}.getUsername(),$P{DATA_SOURCE_PATH}.getPassword())”

Either you can create a sub report separately or you can add use the drag and drop subreport element and start from a blank sub-report (it’s up to you).

Just to keep it easier, I will create sub report separately with MSSQL data source and bring in some fields and add them to the detail band. Preview it and ensure that the report runs and brings in some data as shown below:

Once the sub report is created, I will change my Data Adapter to not use any data source as shown below:

The purpose of the above steps is to ensure that the metadata of the fields are read and the report has fields added to the report from your second data source i.e. MSSQL in this case.

Now, go ahead and drag and drop sub report component from the palette into the summary section of the main report. Select an existing report and point to the report that you have created using the MSSQL in the above step (using workspace resource).

click “subreport” section

You should see something like this:

Now go ahead and change the value of the connection expression. Change it to $P{SUBREPORT_CONNECTION}

We are passing the connection string from the main report to the sub report using the parameters.

Now, preview the main report in the Jaspersoft Studio. You may see errors like this:

I did not research more on the error messages. If anyone has any input let us know in the comment section(We might need to add all the jars from the Jaspersoft server to be added to the classpath on this project in the studio)

Go ahead and publish this report to the Jaspersoft Server, and test it on the server.

You should see your report running without any errors. Here is the screenshot of the report from my instance.

Note: I attached the jrxml files to this KBA so that you can take this as a reference if you run into any challenges during configuration on your end.  This is tested in the latest version of the JRS 8.0

Hope this helps someone!

diffdbtype-postgresql-mssql.jrxml

diffdbtype-subreport.jrxml