0

[![enter image description here][1]][1]I have an SSRS 2017 report that consists of a main report and multiple sub reports. This report is published to the SSRS Portal, and then called by a web application. The report currently accepts one parameter from the web application, i.e. Case ID. My task is to allow the web application to pass multiple Case IDs, and the report will then be executed multiple times, each for a Case ID. If I pass multiple Case IDs to the main report, the outcome is that each sub report will be repeated multiple times. Let's say, I pass case ID 100 and 101 to the report, the outcome would be something like this: Sub-Report-A-Case-100, Sub-Report-A-Case-101, Sub-Report-B-Case-100, and Sub-Report-B-Case-101, so on and so forth. The ideal scenario would be Sub-Report-A-Case-100, Sub-Report-B-Case-100, Sub-Report-A-Case-101, etc. Since the report is called from a web application, I cannot use data-driven subscription features. What other options do I have?

Please advise. Thanks, Jay

Jay
  • 65
  • 1
  • 9

1 Answers1

0

Assuming you have a list of case id's and you have a multi-value parameter called @CaseID then I would do it as follows...

In you main report:

Create a dataset and call it dsCases for example, with a query something like

SELECT DISTINCT CaseID FROM WHERE CaseID IN (@CaseID) ORDER BY CaseID

This will give you small dataset with 1 row per case.

Next add a table to your report and remove all but one column. (You can remove additional rows too leaving a single 'cell'. Set the datset property of this tablix to dsCases

In the remaining cell, right-click and insert a subreport. Set the subreport parameters as required (point it to Sub-Report-A), passing the CaseID field as the parameter value (Fields!CaseID.Value).

Copy and paste the entire table and align it under the first one.

Change the subreport properties of the subreport in the second table to point to your second sub report (Sub-Report-B).

This will generate a "Sub-Report-A" for each case in the dataset and then it will repeat for "Sub-Report-B". If this does not work, let me know and I will post a full answer with screenshots.

Alan Schofield
  • 19,839
  • 3
  • 22
  • 35
  • Thanks Alan. I'll give it a try and let you know the outcome. – Jay Sep 17 '20 at 16:54
  • Hi Alan, as you suggested, I created a main report with a multi-value parameter to store the case ids. Then added a table to populate the Sub-Report-A. After that I copied this table to get add Sub-Report-B. However, I dont know how to match the case id parameter dynamically. Please advise. Thanks. – Jay Sep 17 '20 at 19:14