Paginated SubReport passing multivalue parameter from parent dataset

[razaross]

I have a paginated report that also contains a subreport. The parent report displays data from dataset dsMain. The subreport accepts a multivalue parameter. The subreport is not in the parent report's data region, but should come afterward and only once. (It's a Glossary of definitions of categories that appear in the parent report's data region) How, exactly, can I gather all distinct CategoryIds (one of the fields of dsMain) and pass that to the subreport as a multivalue parameter value?

[v-jianboli-msft]

Hi @razaross ,

Add subreports to a paginated report when you want to create a main report that is a container for multiple related reports. A subreport is a reference to another report. To relate the reports through data values (for example, to have multiple reports show data for the same customer), you must design a parameterized report (for example, a report that shows the details for a specific customer) as the subreport. When you add a subreport to the main report, you can specify parameters to pass to the subreport.

You can also add subreports to dynamic rows or columns in a table or matrix. When the main report is processed, the subreport is processed for each row. In this case, consider whether you can achieve the desired effect by using data regions or nested data regions.

To add a subreport to a report, you must first create the report that will act as the subreport.

For more information on creating the subreport, see Subreports (Report Builder and SSRS).

View parameters for paginated reports in the Power BI service - Power BI | Microsoft Learn

Best Regards,

Jianbo Li

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[razaross · Accepted solution]

Yes, I understand what subreports are and I don't want to make it part of parent data region. It's more of a summary of definitions of each category that gets displayed in the parent report and I don't want to run the same query more than once since it's a fairly expensive one.

I actually got this to work by asking ChatGPT the exact question above how to do it and this is what it came up with:

--------------------------------------------------------------------------

1. In the parent report, create a query parameter that will hold the list of distinct CategoryIds. To do this, go to the "Report Data" pane, right-click on "Parameters", and select "Add Parameter". Give the parameter a name, such as "CategoryIds", and set the "Data type" to "Integer" and "Allow multiple values". In the "Available Values" section, choose "Get values from a query" and then select the dataset that will be used to populate the parameter with values.

2. In the "Dataset" dropdown, select the dataset that contains the distinct CategoryIds (in this case, dsMain). In the "Value field" dropdown, select the field that contains the CategoryIds (in this case, "CategoryId").

3. In the subreport, create a parameter that will receive the values from the parent report. To do this, go to the "Report Data" pane, right-click on "Parameters", and select "Add Parameter". Give the parameter the same name as the parameter in the parent report (in this case, "CategoryIds"), and set the "Data type" to "Integer" and "Allow multiple values". In the "Default values" section, choose "Get values from a query" and then select the dataset that will be used to populate the parameter with values.

4. In the subreport, use the "CategoryIds" parameter in your query to filter the data that is displayed in the report. For example, you could use a WHERE clause like this:

WHERE CategoryId IN (@CategoryIds)

1. In the parent report, add the subreport to the report body, and then set the "CategoryIds" parameter of the subreport to the "CategoryIds" parameter of the parent report. To do this, right-click on the subreport and select "Subreport Properties". In the "Parameters" tab, you will see a list of parameters for the subreport. Click on the "CategoryIds" parameter and then click on the "fx" button. In the expression builder, click on the "Parameters" node and then select the "CategoryIds" parameter of the parent report.

That's it! When you run the parent report, the subreport will be filtered using the list of CategoryIds that are gathered from the dsMain dataset of the parent report.

-----------------------------------------------------------------------------------------

ChatGPT for the WIN!! 🤣