by Giulio Toffoli
A subreport is a report included inside another report. This allows the creation of very complex layouts with different portions of a single document filled using different data sources and reports.
In this tutorial we want to create an address book printing for each person in the address book the name, the list of phone numbers and the list of email addresses.
The layout we want to obtain is the one in figure 1.
Figure 1
A master report will be used to select the persons from the address book and render the main page and the person details. A first subreport will be used to select the email addresses of each person and render the orange portion. Finally a second subreport will be used to extract the phone numbers and print them (blue portion).
To fill the report, we will use the following database script.
create table PERSONS (
ID int primary key,
NAME varchar(30),
CITY varchar(30) );
insert into PERSONS values(1, 'ETHAN', 'NEW YORK');
insert into PERSONS values(2, 'CALEB', 'SAN FRANCISCO');
insert into PERSONS values(3, 'WILLIAM', 'LONDON');
create table EMAIL_ADDRESSES (
ID int primary key,
ID_PERSON varchar(30),
EMAIL varchar(100) );
insert into EMAIL_ADDRESSES values(1, 1, 'ethan@yahoo.com');
insert into EMAIL_ADDRESSES values(2, 1, 'ethan@gmail.com');
insert into EMAIL_ADDRESSES values(3, 2, 'caleb@yahoo.com');
insert into EMAIL_ADDRESSES values(4, 2, 'caleb2@linux.com');
insert into EMAIL_ADDRESSES values(5, 2, 'cccleb@jaspersoft.com');
insert into EMAIL_ADDRESSES values(6, 3, 'wlm@somedomain.co.uk');
insert into EMAIL_ADDRESSES values(7, 3, 'william@someemail.eu');
insert into EMAIL_ADDRESSES values(8, 3, 'willy@myemail.org');
create table PHONE_NUMBERS (
ID int primary key,
ID_PERSON varchar(30),
PHONE varchar(100) );
insert into PHONE_NUMBERS values(1, 1, '1(111) 111-1111');
insert into PHONE_NUMBERS values(2, 1, '1(222) 222-2222');
insert into PHONE_NUMBERS values(3, 1, '1(333) 333-3333');
insert into PHONE_NUMBERS values(4, 2, '1(444) 444-4444');
insert into PHONE_NUMBERS values(5, 3, '1(555) 555-5555');
insert into PHONE_NUMBERS values(6, 3, '1(666) 666-6666');
We start from a blank report to create the master report, the one to show the person names. It is a very simple report based on the query:
SELECT * FROM PERSONS;
In the detail band we add the field NAME and CITY. The gray background is obtained using a frame element that contains the two fields.
The line at the bottom of the detail band has the property Position Type set to float, in this way, when we will add the subreports, whatever space the subreport elements will take at run time, the line will move accordingly.
The figure 2 shows the layout of the master report.
Figure 2
Run the report, the result must be similar to the one in figure 3.
Figure 3
The second step is to create the subreport to display the email addresses. Start with a blank report, save it in the same directory as the master, and call it subreport_1.jrxml.
Open the page format dialog (Format → Page format...) and reduce the size of the page to 270 pixels and remove the margins, since they are not useful in a subreport. The height of the report in this case is not very interesting, since it will managed by the master report once this report will be used as subreport.
Figure 4
This report will not list all the email addresses read from the table EMAIL_ADDRESSES, but just the ones of the current person in the master. To filter the email addresses, we will use a parameter. In the report inspector right click the Parameters node and select Add Parameter. In the property sheet change the parameter name to ID_PERSON, set the parameter class to java.lang.Integer and set a default value, i.e. 1.
Figure 5
Open the query dialog and paste the query:
SELECT * FROM EMAIL_ADDRESSES WHERE ID_PERSON=$P{ID_PERSON}
The syntax $P{ID_PERSON} allows the use of a parameter inside a query, in this case to filter the result using a where condition.
Close the query dialog and proceed with the report layout (see figure 6).
Figure 6
If you want you can run this report to see the result.
Repeat the same steps to create the second subreport to display the phone numbers. Call it subreport_2.jrxml and save it in the same directory as the other two jrxml files.
This time the query will be:
SELECT * FROM PHONE_NUMBERS WHERE ID_PERSON=$P{ID_PERSON}
The layout of the second subreport should be similar to the following picture:
Figure 7
It's time to put all togheter. Go back to the master report and drag from the palette into the detail band the subreport element.
The report wizards pops up. Select the second option (Use an existing report) and select the subreport_1.jrxml (or subreport_1.jasper if you have already tried it).
Figure 8
In the following step use the option "Use the same connection used to fill the master report" (which should be selected by defaut). In this way the database connection will be passed to the subreport to let it execute its SQL query.
Click next. In the next step we can set an expression for the parameter exposed by the subreport. Set it to the field ID. When the report will be executed, the email addresses will be filtered based on the ID of the current report (which is the ID of the person we are processing).
Figure 9
Complete the wizard keeping all the default settings. The new subreport element will be placed inside the detail, adjust its size (the width must be set to 270 pixels) and the position so it can fit in the available space (see figure 10).
Figure 10
Copy and paste the subreport element. The only difference between the two subreport elements is the reference to the jasper file to use as subreport. In the property sheet change the Subreport expression as follow:
$P{SUBREPORT_DIR} + "subreport_2.jasper"
The parameter name (ID_PERSON) is the same in both the subreports, like the way it is passed from the master, so there are no other changes to do. Fix the position of the second subreport to be on the right of the first one.
Figure 11
Finally run the report. The expected result is shown in figure 12.
Figure 12
This tutorial covers just the basics of subreporting in JasperReports. It uses a JDBC connection and staic paths to the subreports. Subreports can be filled with any kind of data source, including XML, a collection of java beans, an empty data source and so on. The subreport is then able to return to the master results of calculations using return values, the jasper file to be used a subreoprt can be loaded dynamically and a subreport can include other subreports, allowing the creation of very complex layouts.
More on Complex Reporting
Creating Charts and Subreports with Jaspersoft Studio
Recommended Comments
Create an account or sign in to comment
You need to be a member in order to leave a comment
Create an account
Sign up for a new account in our community. It's easy!
Register a new accountSign in
Already have an account? Sign in here.
Sign In Now