UC2: Creating Reports with Subreports

First, we will decide what our parent report will look like. For this tutorial we will create a report that populates a list of clients with the patients they have brought to the vet hospital for treatment. We'll include some basic information about the clients and the patients as well. If you need a refresher on how to create a report, please see the tutorial in UC1.

We can start by creating a new report using the Blank A4 template removing all of the bands except for the Title, Column Header, and Detail bands. This is the SQL query we will be using:

SELECT c.client_id, c.account_number, c.first_name, c.last_name, a.addr_ln1, a.city, a.state, a.zip from hospital_clients c
LEFT OUTER JOIN ( select client_id, addr_ln1, city, state, zip from hospital_address where addr_Type = 'MAILING' ) a

ON a.client_id = c.client_id
WHERE lower(c.last_name) like $P{Client Name}

$P{Client Name} is not a field but a parameter that must be added and then dragged into the query. This results in a prompt that acts as a search term matching to all clients with the desired last name. The query editor window should look like the example below. Don't forget to select a Data Adapter and use the Read Fields button.

From here, we need the information from our report visually organized. Feel free to do it however you would like - I chose to keep mine fairly simple and straightforward. 

Running this report as-is will give you a list of clients and their information matching the last name typed into the prompt. We would prefer a more comprehensive report, however. 

As with most features of Jaspersoft Studio, there is more than one way to create a subreport. Locate the Subreport element from the Palette View and drag it to the detail band of your main report. A subreport wizard should appear. If you had an already existing report you would like to use as a subreport, you would choose that option in the wizard. For this tutorial, we will choose to create a new report. Select a template of your choice and give it a name as you would with any new report. When it comes to selecting a Data Adapter, be sure to choose the same one as your main report. The SQL query we will be using for the subreport is:

SELECT p."CASE_NO",
p."PATIENT_NAME",
s."SPECIES",
b."BREED"
FROM "HOSPITAL_PATIENTS" p
join "HOSPITAL_CLIENT_PATIENT_LINK" l ON
l."PATIENT_ID" = p."HOSPITAL_PATIENT_ID"
left outer join "BREEDS_MASTER" b ON
p."BREED_ID" = b."BREED_ID"
join "SPECIES_MASTER" s ON
p."SPECIES_ID" = s."SPECIES_ID"
WHERE
l."CLIENT_ID" = $P{client_id}

Again, $P{client_id} is a parameter that has to be manually added. if we were to run this report on its own, it would simply prompt us to enter a client id number. Since we are using it as a subreport, we will connect that parameter to the client id field in the main report.

Since this is intended as a subreport, we need even less formatting than the main report. Leave only the Detail and Column Header bands. We also will not have a need for things like margins. Right-click the report to select the "Remove Margins" option. Depending upon how much space you would like each part of your final report to take up, you will have to play with the report properties. I decided to leave just over 60px of space in my main report for the subreport, so I set the detail band and the column header band in the subreport to a height of 30px each. Select the band from the Outline View to change its properties in the right-hand side of the screen.

From here, we can again turn to the Outline, drag our desired fields into the detail band, and arrange our report elements how we'd like. Again, I kept mine fairly simple. 

This step is a simple one, but easily forgotten and it can be difficult to locate. We need to pass the field of our Main Report as the parameter our subreport is expecting. First, click on the subreport element in your main report. On the right-side of the screen you will see a "Subreport" tab in the Properties View.

Click "Edit Parameters" and then click "Add". You will be presented with a window from which you can choose from our list of parameters. Scroll down until you find the "client_id" parameter we added to the subreport when we made it.

Now that we have the correct parameter selected, we have to define what information we would like to pass through it. Next to the text area for "Parameter Expression" there is a small icon that looks like a window. Click that and you should be presented with this new window.

This is where Subreports can become very robust and powerful giving you the option to map expressions to parameters. For our purposes, we only want to select the CLIENT_ID field from the main report. This way, when our subreport queries information about patients from the database, it will pull information connected to the client id from the main report. So, when the main report pulls the entry for "John Smith" the subreport takes the client id for John Smith and looks for all the animals in the records that belong to that client id.

Select Finish until you're back to the main report. If it compiles without any issues, switch over to the Preview tab at the bottom of the Report Editing Area. You will se a prompt where you can type any last name you'd like. Once entered, you will see a report listing every client with a matching last name and a list of their animals underneath their names.