Reusing Subdataset Data in a Report

Many times we need to represent the same data in different forms in a given report. For instance, we have to display them in a chart, then list them in a table in order to perform some calculations and finally summarize the same data in a crosstab. In such cases, reusing the data between these components is crucial in performance improving, especially when the database or network connection works very slow, or we run complex queries. Once we gathered the data in a datasource, it would be very useful to cache this data and reuse it as needed in subdatasets and report components.

Some useful hints on how to do this are also presented in these pages:

• https://community.jaspersoft.com/wiki/how-cache-data-report-use-multiple-datasets
• https://stackoverflow.com/questions/29943470/multiple-subreports-in-main-report-using-same-datasource

Here we will provide another method that would work for any type of data source and would allow the reuse of a subdataset data for multiple runs. This workaround is based on the following steps:

• use a dummy sortField in subdataset based on the REPORT_COUNT built-in variable to force the engine to cache the subdataset data. It first iterates through it and caches it because the sorting occurs in memory. Sorting on REPORT_COUNT does not actually affect the sort order, but helps us get an instance of SortableDataSource as REPORT_DATA_SOURCE parameter for the subdataset when first run:

<sortField name="REPORT_COUNT" type="Variable"/>

• return the sorted data source from the subdataset in a main variable called MySubDataSource:

<variable name="MySubDataSource" class="net.sf.jasperreports.engine.fill.SortedDataSource" calculation="System"/>
...
<timeSeriesChart>
...
<timeSeriesDataset timePeriod="Month">
 <dataset resetType="Report">
  <datasetRun subDataset="Country_Orders" uuid="c8f3b123-7be5-4ea0-9002-5fc495821df5">
   <datasetParameter name="Country">
    <datasetParameterExpression><![CDATA[$F{ShipCountry}]]></datasetParameterExpression>
   </datasetParameter&gt;
   <returnValue fromVariable="MyDataSource" toVariable="MySubDataSource"/>
  </datasetRun>
 </dataset>

• pass that MySubDataSource as the dataSourceExpression for the other report components that need to reuse the data, and
• make sure the record pointer is moved back to the firsts record by calling moveFirst() in the dataSourceExpression of the report components:

<timeSeriesChart>
...
<timeSeriesDataset timePeriod="Month">
 <dataset resetType="Report">
  <datasetRun subDataset="Country_Orders" uuid="c8f3b123-7be5-4ea0-9002-5fc495821df5">
   <dataSourceExpression><![CDATA[$V{MySubDataSource}; $V{MySubDataSource}.moveFirst()]]></dataSourceExpression>
  </datasetRun>
 </dataset>

This way, the data source produced by the first subdataset run can be used multiple times.

Attached is a modified version of a JRL sample that can be found in the demo/samples/charts directory in the JRL project distro. The original sample file can be found here:

https://github.com/TIBCOSoftware/jasperreports/blob/master/jasperreports/demo/samples/charts/reports/SubDatasetChartReport.jrxml

The modified version of this report displays each chart twice, using the same subdataset, but the second time around, it does not execute the query of the subdataset again, it rather uses cached data from the first run of the dataset performed for the first chart. The report can be run in JSS using the built-in Sample DB datasource.

subdatasetchartreport.jrxml