如何在Jasper .jrxml中让子数据集以参数形式引用主数据集？

Great question! You absolutely can reference your main dataset's results in sub-datasets without duplicating the query or relying on database views—though the approach isn't exactly using $P{Main_Dataset} as a direct table in SQL. Here's how to do it properly in JasperReports:

Two Primary Approaches #

1. Pass the Main Dataset as a Data Source to Sub-Datasets #

This is the most straightforward method for most use cases. Instead of writing a new SQL query for your sub-dataset, you'll use the main dataset's in-memory results as the sub-dataset's data source, then filter/group the data using Jasper's built-in expressions or JRQL (JasperReports Query Language).

Step-by-Step Setup:

• First, define your main dataset with your full query (only once!). Make sure all necessary fields are declared.
• Create a parameter in your main report to store the main dataset's results (e.g., MainDatasetResults with type java.util.List).
• Use a scriptlet to capture the main dataset's results after it runs. This will populate the parameter with the full list of records from your main query.
• Configure your sub-dataset to accept the MainDatasetResults parameter, then use JRQL to query this in-memory list instead of writing a new SQL query.

2. Use JRQL to Query the Main Dataset's In-Memory Results #

JRQL lets you write SQL-like queries against Java object collections, which means you can treat your main dataset's results as a "virtual table" directly in your sub-dataset.

Example JRXML Snippets:

Main Dataset Definition

<datasetRun name="MainDataset">
    <datasetParameter name="REPORT_CONNECTION">
        <datasetParameterExpression><![CDATA[$P{REPORT_CONNECTION}]]></datasetParameterExpression>
    </datasetParameter>
    <queryString language="sql">
        <![CDATA[-- Your full main query here
SELECT col1, col2, col3, sth FROM main_table WHERE your_filters_here]]>
    </queryString>
    <!-- Declare fields matching your main query -->
    <field name="col1" class="java.lang.String"/>
    <field name="col2" class="java.lang.Integer"/>
    <field name="col3" class="java.lang.Double"/>
    <field name="sth" class="java.lang.String"/>
    
    <!-- Parameter to store main dataset results -->
    <parameter name="MainDatasetResults" class="java.util.List" isForPrompting="false"/>
    
    <!-- Scriptlet to capture results after main query runs -->
    <scriptlet name="StoreResults">
        <scriptletExpression><![CDATA[
            // Capture results from the main dataset's data source
            net.sf.jasperreports.engine.data.JRResultSetDataSource mainSource = 
                (net.sf.jasperreports.engine.data.JRResultSetDataSource)$P{REPORT_DATA_SOURCE};
            $P{MainDatasetResults} = mainSource.getData();
        ]]></scriptletExpression>
    </scriptlet>
</datasetRun>

Sub-Dataset Definition

<subDataset name="MySubDataset">
    <!-- Accept the main dataset results as a parameter -->
    <parameter name="MainDatasetResults" class="java.util.List"/>
    
    <!-- Use JRQL to query the in-memory list (looks like SQL!) -->
    <queryString language="jrql">
        <![CDATA[SELECT col1, col3, sth FROM $P{MainDatasetResults} m 
                GROUP BY m.sth 
                ORDER BY 3, 4 DESC, 2]]>
    </queryString>
    
    <!-- Declare fields matching your JRQL query -->
    <field name="col1" class="java.lang.String"/>
    <field name="col3" class="java.lang.Double"/>
    <field name="sth" class="java.lang.String"/>
</subDataset>

Using the Sub-Dataset (e.g., in a Chart or Table)

<datasetRun name="MySubDatasetRun">
    <datasetParameter name="MainDatasetResults">
        <datasetParameterExpression><![CDATA[$P{MainDatasetResults}]]></datasetParameterExpression>
    </datasetParameter>
    <datasetName>MySubDataset</datasetName>
</datasetRun>

Key Pros & Cons #

• Pros:
  • Only write your main query once—all sub-datasets reference the same results.
  • No need to modify database objects (like views) if you don't want to.
  • Full control over data transformation in Jasper without touching the database.
• Cons:
  • All main dataset results are loaded into memory. For large datasets, this can cause performance issues or memory errors.
  • JRQL has limited support for complex SQL functions (e.g., window functions, advanced aggregations)—you'll need to handle these in your main query or via custom scriptlets.

When to Use Database Views Instead #

If your dataset is large (10k+ records) or requires complex SQL operations, stick with the database view approach. Databases are optimized for handling large-scale data processing, and you'll avoid memory bottlenecks in Jasper.

内容的提问来源于stack exchange，提问作者Nikola Pavlovic