(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:
- 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:
- 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.
- 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.
- Click on the Name property of the parameter and type InvoiceID as its value. The name of the parameter1 parameter will change to InvoiceID.
- Click on the Parameter Class property and select java.lang.Integer as its value.
- 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.
- Click the Report query button on the right of the Preview tab; a Report query dialog will appear, as shown in the following screenshot:
- 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:
- Double-click the Fields node in the Report Inspector window. You will see that it contains invoiceid and paymentstatus fields, as shown below.
- 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:
- 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.
- 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:
