Jaspersoft iReport: How to pass a parameter to a sub-dataset

Jaspersoft iReport: How to pass a parameter to a sub-dataset

Let’s say our main report is grouped by continent and the details band holds sales figures for each country. We would like to add now a pie chart to the group footer to visualize the share by product by continent.

Imagine our main report query is:

SELECT
     product_sales.`continent` AS product_sales_continent,

product_sales.`city` AS product_sales_city,     

     product_sales.`product` AS product_sales_product,
     product_sales.`sales` AS product_sales_sales
FROM
    `product_sales` product_sales
ORDER BY
1, 2, 3

To add a sub-dataset right click on the report root element and choose New Dataset:
Follow the wizard instructions and insert a query. In our imaginary case it is something like this:

SELECT
    product_sales.`product` AS product_sales_product,
    SUM(product_sales.`sales`) AS product_sales_sales
FROM
    `product_sales` product_sales
GROUP BY 1
ORDER BY 1

You must have noticed that there is something missing: This is of course not our final query, we want to restrict the results by continent. In order to do that, we have to first pass the parameter to the sub-dataset.

Now let’s have a look at how this is done:

1. Create the parameter within the sub-dataset: Give it a name in the properties panel and make sure that the parameter class is the same as for the parameter that you want to pass from the main query. Also, untick Use as a prompt:
   
2. Now add a chart to the design area. Right click on it and choose Chart Data:
3. Specify all the standard settings for the chart (Connection etc), then click on Parameters and Add. From the top pull down menu Dataset parameter name choose the sub-dataset parameter we just created before. Click on the button next to Value expression: You can now choose from the parameters, fields and variables of the main dataset. In our case, we just choose the continent field:
   Apply, Click Apply, OK, and Close.
4. Now we can finally change our SQL query in a way that it looks like we originally intended it to be. Therefore, right click on the dataset and choose Edit Query:
   Now add a WHERE clause to the query like this one:WHERE
   product_sales.`continent`=
   From the right hand side, you can drag over the parameter. It should then look like this:OK.

For your reference, please find the whole last SQL query below:
SELECT
    product_sales.`product` AS product_sales_product,
    SUM(product_sales.`sales`) AS product_sales_sales
FROM
    `product_sales` product_sales
WHERE
product_sales.`continent`=$P{CONTINENT_CHART}
GROUP BY 1
ORDER BY 1

Now everything will be working as intended. The parameter will now be passed from the main report to the chart (which is kind of a hidden sub-report). This method works for Lists, Tables and Crosstabs as well.