A subreport is a report that is embedded within the master report. There are many reasons for which we might do this. One of the reasons would be to add a chart to a master report that runs a different data source. It's worth mentioning that we can only run a single data source per report. If we wanted to create a report that not only lists the orders in detail but also displays a chart for the summary of the orders, then we would have to create a subreport. The subreport would contain the chart for the summary, and we would include this subreport in the master report.
We are going to use two PDI transformations to populate the report from the previous recipes. These transformations will give us a list of orders by country, city, and product line, as well as a summary of orders grouped by country that we will use for our chart in the subreport:
- In Report Designer, go to File | New.
- Click on the Data tab in the top-right corner of Report Designer.
- Right-click on the Data item in the list and select Pentaho Data Integration.
- To add a new data source, click on the plus button in the top-left corner of the pop-up window.
- Set the Name property of the query to Orders.
- Click on the Browse button to open the Select Transformation dialog.
- Select the chapter5-getorders transformation.
- Select the OUT step in the list of steps available in this transformation.
- Click on Preview.
- Click on OK.
We have mentioned before that we are also going to need the summary orders data source to populate the chart in our subreport. We are going to add the summary orders data source to the master report and then pass it down to the subreport to populate the chart. Let's add the secondary data source to the master report right now:
- Click on the Data tab in the top-right corner of Report Designer.
- Right-click on the Data item in the list and select Pentaho Data Integration.
- To add a new data source, click the Plus button in the top-left corner of the pop-up window.
- Set the Name property of the query to OrdersSummary.
- Click on the Browse button to open the Select Transformation dialog.
- Select the chapter5-getorders-summary transformation.
- Select the OUT step in the list of steps available in this transformation.
- Click on Preview.
- Click on OK.
We should now be looking at a report with two data sources available. The Orders data source has been selected by default for the master report because we added it first. The Orders Summary data source is just sitting idle at the moment. We can tell which data source is selected for a report if we can see the fields listed in that data source, as shown in the following screenshot:
Let's populate the master report with data from the Orders query:
- Click and drag the country field from the Data tab and drop it into the Details band of the report just to make sure that the item is aligned to the top-left side of the Details band.
- Click and drag the city field to sit next to the country field in the Details band. If the items overlap in the Details band, then the item background color will turn pink. This is to inform you that you have overlapping items in the report.
- Finally, click and drag the remaining line and totalPrice fields into the report and set them alongside the previous fields.
At this point, we can preview our report and it will display data for the Orders data source. Let's move on to the subreport.
The subreport, as we have mentioned before, will contain a single chart that is populated from the Order Summary data source in the master report. We want this Orders Summary chart to only appear once at the top of our final report. This will give the user a breakdown of the Orders data contained in the rest of the report:
- Drag Sub Report Component from the toolbar on the left into the report canvas and drop it into the Report Header band.
- A popup will appear asking whether you want an inline or banded subreport. Click on banded. This will set the subreport to take the full width of the parent band.
- Another popup will appear asking you which data source you want to pass from the master report to the subreport. Click the Orders Summary data source.
- Click on OK.
After you click on OK, you should be looking at a new report tab next to the master report. This subreport acts like any other regular report with the only difference being that it is technically contained within the master report.
Let's add a chart to this subreport:
- Drag a chart component from the toolbar on the left into the Report Header band of the subreport.
- Resize the Chart Component so that it has a decent width and height.
- Double-click on the Chart Component in Report Header.
- In the Primary Data Source tab, set the Category column property to country.
- Set the Value column property to totalPrice.
- Set the auto-generated-series property to true.
- Click on OK.
Now that we have defined our chart and set the correct field for the data source, we can click on the Preview Report button. If you preview in the subreport, it will actually preview the master report.
You should be seeing something like the following screenshot:
Subreports are nested within the master report. We define ALL data sources in the master report and specify which data sources we want to use when we add subreports to the master report.
The subreport has all the functionality of the master report. We could even go as far as to specify a more complex subreport that contains a lower granularity of data. This subreport would generate many rows and would then be embedded into the master report.
There is no limit to how many subreports you can add to a master report, although things can start to get complex pretty quickly if you are not careful.