How to use multiple data sources in Jaspersoft Studio or JasperReports Server

There are many ways to use multiple data sources in reports designed via Jaspersoft Studio or JasperReports Server:

  1. Multiple datasets, which is a standard feature in the product. You can associate a different dataset with each component added to a report, for example Tables, Crosstabs and Charts. If you require something more complex, another way to use multiple data sources is via Subreports. See this article for more details. And this blog post about using JNDI datasources in Subreports in particular (which would be recommended). Each of those components can be also nested into another one, if required. See also this article about conditionally using different SQL queries in the same report.

    1. You can specify a data adapter for an individual data set by setting the net.sf.jasperreports.data.adapter property on each data set.

      For JasperReports Server, you would deploy each data adapter as a file, and set up the Report Unit as per https://community.jaspersoft.com/wiki/how-deploy-and-use-data-adapter-jasperreports-server-v56  which still works in JasperReports Server 6.X and 7.X.

  2. Domains with a Virtual Data Source: see the Administrator Guide

  3. Jaspersoft ETL to integrate data from multiple sources into the same data warehouse or reporting database. Likely the most performant solution, when done right

  4. Using Profile Attributes in the Data Source definition allows to connect to a different database depending on the Profile Attributes defined at User, Tenant or Server level – as long as it's supported by the same JDBC driver

  5. Instance of java.sql.DriverManager.getConnection to be passed as a connection expression to a dataset:

    1. Create parameter
      Name: subConn
      Class: java.sql.Connection
      EXAMPLE Default Value Expression: java.sql.DriverManager.getConnection("jdbc:postgresql://localhost:5432/foodmart", "myusr", "mypwd")
    2. Component (e.g. Chart / Subreport) Sub Dataset
      Connection/Data Source Expression: Use a Connection expression / Use another connection / Connection Expression (depending on which element in the Jaspersoft Studio UI)
      Expression content: $P{subConn}

  6. Dynamic SQL table name in the same data source, e.g. via Parameter Contributors

  7. Custom Data Sources or a data source that (potentially) provides access to multiple sources in its underlying implementation, e.g. the Webservice Data Source

  8. And finally, a JRDataSource interface implementation if you need even more control, and use anything you can in Java to access multiple data sources and present them together via one interface. This option is probably the most complex to implement and maintain. See here for an example: How to cache data in a report for use in multiple datasets

Please note, the links to the documentation above are specific to 6.3.0. Please change the target URL's if you would like to check a different version, e.g. 6.4.0.

Feedback
randomness