Subreports in Jaspersoft Studio

Overview

The tutorial "Creating Charts and Subreports with Jaspersoft Studio" provided a brief introduction to the subreport element, which allows you to insert a report inside another report. This powerful too allows you to create complex layouts within a single document, using multiple data sources and reports.

In this tutorial we want to create an address book lisitng each person in a company, including their names, phone numbers, and email addresses, as shown in the following image:

Figure 1

To do this, we need a main report containing two subreports.

Creation of the Data Adapter

For this task, we'll need a data source more complex than the sampleDB. For this reason, we need to install JasperReports Server which contains some useful sample databases. To determine which version of Jaspersoft Server you should use with your operating system, check the tutorial Getting Started with Jaspersoft Studio,  and look in the section "Software Requirements and Installation". After you have installed Jaspersoft Studio, use it to create a new Jasper Report using the template "Flower Gray" with the report name "ContactsList". When asked to select a data adapter, click New, then select Database JDBC Connection and click Next.

When asked to choose a name for your new data adapter, use "ContactsDataset" and configure it with this data:

  • JBDC Driver: org.postgresql.Driver;
  • JDBC URL: jdbc:postgresql://localhost:5432/sugarcrm;
  • Username : postgres
  • Password: postgres

After this, use the button "Test" to check if the connection works, and in that case hit "Finish".

Creation of the Main Report

Now you should be back in the Report Creation wizard, at the step where you are asked to select the data adapter. The new data adapter should be selected automatically; if it isn't, select Contacts Adapter.  In the Insert an SQL query text entry box, enter select id, name, shipping_address_city from accounts. This query returns the name of a company and the city where the store is located. Then click Next.

As the next step, you are asked to define groups. Because we don't need them for this tutorial, click >> to add all the fields to the report and click Next. Click Next again, then Finish. The report is created. It should be similar to the one in the image below:

Creation of the First Subreport

The next step is to create a subreport to display email addresses. Start a new report by selecting File > New > Jasper Report. Choose the template Blank A4, save it in the same directory of the master report, and call it "EmailReport.jrxml". When asked to choose the data adapter, select the ContactsAdapter created earlier, and use the following query:

select contacts.email1
from contacts, accounts_contacts
where accounts_contacts.account_id = 'placeholder' and accounts_contacts.contact_id=contacts.id
 
In this query, a static string "placeholder" is used. As suggested by the name, this is not a real value but a placeholder that changes with a parameter described in the next steps. After entering the query, click Next.
 
The query locates one field. Add that field to the report by clicking >>, then click Next. Skip the next step by clicking Next, then click Finish to create the report.

In the Outline view, select the report root node. Then, in the Properties tab, click Edit Page Format.

In the Page Format window, reduce the width of the page to 270 pixels and remove the margins, since they are not useful in a subreport, then click OK.

Because we used a static value as a placeholder, this report does not list any email addresses. We need to retrieve just information for the current person in the master list. To filter the email addresses, we need to use a parameter. In the Outline view, select the report root node. Then in the Property tab, Click Edit query, filter and sort options. In the Dataset & Query Dialog window, select the Parameters tab and click the Add button.

A new parameter is added with the name "Parameter_1". Change this name to "ACCOUNT_ID", and make sure the type String is applied.

Next, replace the string placeholder' with the value $P{ACCOUNT_ID}. This query is influenced by a parameter that comes from the main report. Click Ok.

The syntax $P{ACCOUNT_ID} allows the use of a parameter inside a query, in this case to filter the results using a "where" condition.

The next step is to drag the band column header into the report. Add a Static Text element from the palette and change it to "eMail address". Drag the field email1  from the Outline view into the Detail band, then delete the unused bands (title, page header, column footer, page footer, summary) and resize the Column Header and Detail bands. You should have a layout like the one in the image below:

Save the report and, if you like, you can run it to see the result.

Creation of the Second Subreport

The procedure to create the second subreport it is almost identical to the first one. Start a new report from File > New > Jasper Report. Choose Blank A4 for the template, save it in the same directory as the master, and call it PhoneReport.jrxml. Select  "ContactsAdapter" as the data adapter, and enter the following query:

select phone_work
from contacts, accounts_contacts
where accounts_contacts.account_id = 'placeholder' and accounts_contacts.contact_id=contacts.id

Add the discovered field to the report, don't define groups and complete the wizard. When the report is created, click Edit Page Format,  remove the margins, and set the width to 270 pixels. In the Edit query, filter and sort expression dialog, switch to the tab parameters and add a new parameter "ACCOUNT_ID". Use this to replace the placeholder in the query, as in the image below:

Next, add a Static Text element containing the text "Telephone" in the Column Header band, and add the field "phone_work" to the Detail band. Delete the unused bands and resize these two elements and their bands until you get a report similar to the one shown below:

Now, save the report and, if you want,  compile it to see how it looks.

Now it's time to finish the work on the main report.

Completing the Main Report

The two subreports have been created. Now, we need to include them in the main report.

Open the report ContactList and resize the Detail band so it is large enough to include the subreports - start with 50 pixels. Next, place a subreport element from the Palette into the Detail band to launch the wizard.  Choose Select an existing report (selected by default) and click the Select a report file button. A window listing all created reports appears. Select EmailReport.jrxml,  click  Ok, then click Next.

In the next step, choose the option Use the same connection used to fill the master report (which should be selected by defaut). This ensures the database connection is passed to the subreport, so it can execute its SQL query, then click Next.

Now we can set an expression for the parameter exposed by the subreport. Click Add to add a new parameter. Double-click to select on the new parameter's name, and enter "ACCOUNT_ID". Select the value field, and and click the button with three dots (the button is visible only if the value field is selected) to open the Expression Editor. In the Expression Editor, double-click to select ID from Fields, then click Finish. When you return to the parameters dialog it should look similar to the one in the image below:

Now click Finish. The first subreport is added to the main report.

From the Palette drag another subreport and repeat the same procedure using as report file PhoneReport.jrxml,  and giving all the other fields the same values. At this point you should have the two subreports into the main report. Resize them to and resize the detail to obtain this result:

Go in the Preview tab to compile. If everything is correct, you should see a result similar to the one below. (Don't worry if the emails are all the same, this is still correct - they are the same in the database we used.):

Feedback
randomness