JasperReports 3.6: Using Multiple Relational Databases to Generate a Report

Exclusive offer: get 50% off this eBook here
JasperReports 3.6 Development Cookbook

JasperReports 3.6 Development Cookbook — Save 50%

Over 50 recipes to create next-generation reports using JasperReports

$29.99    $15.00
by Bilal Siddiqui | June 2010 | Cookbooks Java Open Source

In the previous two articles on JasperReports, we covered generating reports from XML data and relational database. This article by Bilal Siddiqui, author of JasperReports 3.6 Development Cookbook, teaches you how to create a report with data coming from two different database sources. The main report has six columns, five of which come from one database. The sixth column comes from a subreport, which fetches its data from the second database.

(For more resources on JasperReports, see here.)

Refer to the installPostgreSQL.txt file included in the source code download (chap4) to install and run PostgreSQL, which should be up and running before you proceed.

The source code also includes two files named copySampleDataIntoPGS.txt and copySamplePaymentStatusDataIntoPGS.txt. The copySampleDataIntoPGS.txt file will help you to create a database named jasperdb5 and create a table named CustomerInvoices with five columns (InvoiceID, CustomerName, InvoicePeriod, ProductName, and InvoiceValue) and copy sample data for this article. Similarly, the copySamplePaymentStatusDataIntoPGS.txt file will help you to create a database named jasperdb5a and create a table named PaymentDetails with two columns (InvoiceID and PaymentStatus) and copy sample data.

You will be using two JRXML files MultiDBReport.jrxml and PaymentStatusSubreport.jrxml in this recipe. You will find these files in the Task4 folder of the source code download for this chapter. The MultiDBReport.jrxml file is the master report, which uses the other file as a subreport. The master report has to refer to its subreport using a complete path (you cannot use relative paths). This means you have to copy the two JRXML files to the c:\JasperReportsCookBookSamples\ folder on your PC. I have hardcoded this complete path in the master report (MultiDBReport.jrxml).

How to do it...

You are about to discover tricks for using multiple databases in a single report in the following simple steps:

  1. Open the PaymentStatusSubreport.jrxml file from the c:\JasperReportsCookBookSamples\ folder. The Designer tab of iReport shows an empty report, as shown in the following screenshot:

    JasperReports 3.6

  2. Right-click on the Parameters node in the Report Inspector window on the left of the Designer tab, as shown next. Choose the Add Parameter option from the pop-up menu.

    JasperReports 3.6

  3. The Parameters node will expand to show the newly added parameter named parameter1 at the end of the parameters list. Click on parameter1, its properties will appear in the Properties window below the palette of components on the right of your iReport main window.
  4. Click on the Name property of the parameter and type InvoiceID as its value. The name of the parameter1 parameter will change to InvoiceID.
  5. Click on the Parameter Class property and select java.lang.Integer as its value.
  6. Click on the Default Value Expression property and enter 0 as its value, as shown in the following screenshot. Leave the rest of the parameter properties at their default values.

    JasperReports 3.6

  7. Click the Report query button on the right of the Preview tab; a Report query dialog will appear, as shown in the following screenshot:

    JasperReports 3.6

  8. Type SELECT * FROM paymentdetails WHERE invoiceid = $P{InvoiceID} in the Query editor. The fields of the paymentdetails table will be shown in the lower-half of the Report query dialog. Click the OK button, as shown in the following screenshot:

    JasperReports 3.6

  9. Double-click the Fields node in the Report Inspector window. You will see that it contains invoiceid and paymentstatus fields, as shown below.

    JasperReports 3.6

  10. Drag-and-drop the paymentstatus field from the Fields node into the top-left corner of the Detail 1 section, as shown in the following screenshot:

    JasperReports 3.6

  11. Select PaymentDetails in the datasources drop-down list, as shown in the left image given below. Then switch to the Preview tab; a Parameter prompt dialog will appear, which will ask you for the invoice ID, as shown in the right image given below. Enter 1001 as the value of the InvoiceID parameter. You will see a report containing a single record showing the payment status of the invoice having the ID 1001.

    JasperReports 3.6

  12. Switch back to the Designer tab. Click anywhere in the Page Header section; its properties will appear in the Properties window below the palette. Select the Band height property and set 0 as its value, as shown in the following screenshot:

    JasperReports 3.6

JasperReports 3.6 Development Cookbook Over 50 recipes to create next-generation reports using JasperReports
Published: July 2010
eBook Price: $29.99
Book Price: $49.99
See more
Select your format and quantity:

(For more resources on JasperReports, see here.)

  • Similarly, repeat step 12 for the Title, Column Header, Column Footer, Page Footer, and Summary sections and set 0 as the value of the Band height property. This will set all sections to zero height except the Detail 1 section.
  • Double-click on the blue line at the bottom of the Detail 1 section. The height of the Detail 1 section will become equal to the height of the text field dropped into the Detail 1 section in step 10. The report in the Designer tab will look as shown in the following screenshot:

    JasperReports 3.6

  • Click on the name of your report in the Report Inspector window on the left of the Designer tab, as shown in the following screenshot:

    JasperReports 3.6

  • The report properties will appear in the Properties window below the palette. Set 0 as the value of the Top margin and Bottom margin properties in the Margins section of the Properties window, as shown in the following screenshot:

    JasperReports 3.6

  • Now open another report named MultiDBReport.jrxml from the c:\JasperReportsCookBookSamples folder. The Designer tab of iReport shows a report containing data in the Title, Column Header, Customer Group Header1 and Detail 1 sections, as shown in the following screenshot:

    JasperReports 3.6

  • Select PG in the datasources drop-down list. Then click the Preview button and you will see a report containing invoices grouped by customer names, as shown in the following screenshot:

    JasperReports 3.6

  • Copy-paste the Invoice Value column label and place the new copy of the Invoice Value label to the right of the existing label, as shown in the following screenshot:

    JasperReports 3.6

  • Double-click on the new copy of the Invoice Value label and type Payment Status as its value.
  • Drag-and-drop a Subreport component from the palette into the Detail 1 section, just below the PaymentStatus label. A Subreport wizard dialog will appear, as shown below.

    JasperReports 3.6

  • Choose the Use an existing report option and click the Browse button to browse to the PaymentStatusSubreport.jrxml file located in the c:\JasperReportsCookBookSamples\ folder and click the Open button, as shown next. The browser dialog will disappear. Click the Next button in the Subreport wizard dialog.

    JasperReports 3.6

  • A Connection exp (2 of 4) dialog will appear, as shown in the following screenshot. Notice that now the left side of the Subreport wizard shows four steps (Subreport, Connection exp, Parameters, Subreport exp). You are at step 2 (Connection exp) which is shown in bold.

    JasperReports 3.6

  • Choose the Use another connection option and type the java.sql.DriverManager.getConnection("jdbc:postgresql://localhost:5432/jasperdb5a", "postgres", "postgres") expression in the area below the Use another connection option, as shown below. Click the Next button at the bottom of the window.

    JasperReports 3.6

  • A Parameters (3 of 4) dialog will appear. This will show the InvoiceID parameter of the PaymentStatusSubreport.jrxml subreport and allows you to enter an expression to map it to elements (that is Fields or Variables) of the main MultiDBReport.jrxml report.

    JasperReports 3.6

  • Click on the Expression field beside the InvoiceID parameter. A drop-down list will open. This will show all elements of the MultiDBReport.jrxml report. Select the InvoiceID field from the list, as shown in the following screenshot:

    JasperReports 3.6

    Click the Next button.

  • A Subreport exp (4 of 4) dialog will appear. Continue with the Store the directory name in a parameter option selected and click the Finish button.

    JasperReports 3.6

  • The Subreport element will be placed in the Detail 1 section, as shown in the following screenshot:

    JasperReports 3.6

  • Click on the Subreport element; its properties will appear in the Properties window below the palette. Find the Width property and set 80 as its value.
  • Click on the Height property and set 15 as its value, as shown in the following screenshot:

    JasperReports 3.6

  • The size of the Subreport element will become equal to the size of the other fields placed in the Detail 1 section, as shown in the following screenshot:

    JasperReports 3.6

  • Click the Preview button and you will see a report containing invoices with their status coming from the payment status subreport, as shown in the following screenshot:

    JasperReports 3.6

  • How it works...

    In this article, you have done four things:

    1. First you made a subreport with a parameter named InvoiceID in steps 1 to 16. The subreport is a very useful feature that allows you to design a portion of a report as a separate, independent report and then insert the portion into the main report.
    2. Then you inserted the subreport into a main report in step 21.
    3. You configured a new database connection for the subreport in step 24. Note from the screenshot of step 24 that you used the java.sql.DriverManager class to configure this database connection for the subreport.

      When I configured the database connection in step 24, the name of my database server was postgresql, the network address of the machine hosting the server was localhost, the server was listening at port 5432, the name of the database was jasperdb5a, and the username and password were both postgres. Putting all this together, I got the complete expression of step 24 as "java.sql.DriverManager.getConnection ("jdbc:postgresql://localhost:5432/jasperdb5a", "postgres", "postgres")". This way you can configure any database (for example MySQL) for your subreport.

    4. You mapped the InvoiceID parameter of the subreport to the InvoiceID field of the main report in steps 25 and 26.

    The result of these four steps is that the main report fetches its five columns from its own database connection. Then it asks the subreport to provide data for the sixth column (that is, Payment Status). The subreport fetches data for the sixth column from another database connection and returns the data to the main report, which eventually displays the data coming from the subreport in the sixth column.

    Summary

    This way you can design reports using multiple databases.


    Further resources on this subject:


    JasperReports 3.6 Development Cookbook Over 50 recipes to create next-generation reports using JasperReports
    Published: July 2010
    eBook Price: $29.99
    Book Price: $49.99
    See more
    Select your format and quantity:

    About the Author :


    Bilal Siddiqui

    Bilal Siddiqui is an Electronics Engineer, an XML consultant, and the founder of XML4Java.com, a company focused on simplifying e-business. After graduating in 1995 with a degree in electronics engineering from the University of Engineering and Technology Lahore, he began designing software solutions for industrial control systems. Later, he turned to XML and used his programming experience in C++ to build web- and WAP-based XML processing tools, server-side parsing solutions, and service applications. Bilal is a technology evangelist and a frequently published technical author.

    Bilal has been focusing exclusively on Java and XML-based open source tools and solutions since 2006. He has extensively used popular open source products such as JasperReports, ADempiere, Openbravo, and Eclipse. Bilal is a strong advocate of open source tools and is engaged not only in designing solutions based on open source tools but also collaborating with local universities in Lahore to train software and IT personnel in using open source technologies.

    Books From Packt


    iReport 3.7
    iReport 3.7

    Pentaho Reporting 3.5 for Java Developers
    Pentaho Reporting 3.5 for Java Developers

    Amazon SimpleDB Developer Guide
    Amazon SimpleDB Developer Guide

    RESTful Java Web Services
    RESTful Java Web Services

    Apache JMeter
    Apache JMeter

    Apache Geronimo 2.1: Quick Reference
    Apache Geronimo 2.1: Quick Reference

    Tomcat 6 Developer's Guide
    Tomcat 6 Developer's Guide

    JavaFX 1.2 Application Development Cookbook
    JavaFX 1.2 Application Development Cookbook


    Your rating: None Average: 1.5 (2 votes)
    Too many connections by
    Hi Bilal, Thanks for your great article, it is very well written. I'm having a problem when placing the subreport into the detail band - for each detail row the subreport is opening a new connection to the database, resulting in a "Too many connections" error. Other than increasing the max number of connections in the database, is there a way to reuse the same connection for each instance of the subreport, or to close the connections after generating the report? Kind Regards, Mon

    Post new comment

    CAPTCHA
    This question is for testing whether you are a human visitor and to prevent automated spam submissions.
    C
    7
    x
    i
    i
    6
    Enter the code without spaces and pay attention to upper/lower case.
    Code Download and Errata
    Packt Anytime, Anywhere
    Register Books
    Print Upgrades
    eBook Downloads
    Video Support
    Contact Us
    Awards Voting Nominations Previous Winners
    Judges Open Source CMS Hall Of Fame CMS Most Promising Open Source Project Open Source E-Commerce Applications Open Source JavaScript Library Open Source Graphics Software
    Resources
    Open Source CMS Hall Of Fame CMS Most Promising Open Source Project Open Source E-Commerce Applications Open Source JavaScript Library Open Source Graphics Software