CODEWITHSUNDEEP
sql-serverreporting-services
David Lopez
David Lopez

Reputation: 39

How can I select all parameters to run a report, but have them run independently

My quality engineers have asked me to create a report based on a document that they fill out manually. At first glance, it appeared to be an easy enough request. It has been anything but easy. This report shows the results of two inspections and I have a parameter where the user selects one inspection or the other. As it stands now, this report runs just fine when selecting one inspection, but I am anticipating a request to be able to run the reports for both inspections at the same time. And, that's where I run into trouble.

The problem is that I have a text box in the header that is referencing a value from a table in the report that identifies the inspection. When I set the parameter to a multi-value parameter and select all, the report only displays one of the inspections across the entire report. Is it possible to have the user select both inspections but have the report run them separately? I have one dataset for the table and a column in that table that identifies the inspection. I have tried the multi-value parameter and I have tried using the filter option in the dataset properties, but I get the same result.

Inspection Report

Upvotes: 0

Views: 301

Answers (1)

Alan Schofield
Alan Schofield

Reputation: 21683

The easiest way to do this might be to create a new master report and use your current report as a sub-report. It's fairly simple.

Here's the basic steps...

Make a copy of your current report and rename it (say sub_InspectionReport) Change this report so the the inspection parameter is NOT multi-valued

Create a new report and add your parameter(s) the same as you setup for your original report, This time make sure your Inspection parameter (e.g. pInspection) IS multi-valued.

Create a dataset (dsInspectionIDs) that returns a list of the selected inspection values from the pInspection parameter. The query could be something simple like

SELECT InspectionID FROM myInspectionTable WHERE InpectionID IN (@pInspection)

Note: @pInspection is the name of your report parameter, it is case sensitive.

Now add a table to the report and, as a simple, test, se the dataset of the table to your daatset dsInspectionIDs. Set the first column to show the only available field (in this example InspectionID).

Run the report and test the output using different inspection parameter selections. The table should match what you have selected...

We're almost there...

Now in one of the table cells (but not in the header) , right-click and select "Insert / Subreport". Now right-click the sub-report placeholder and set the sub report to be the copy of the original report we made at the start (sub_InspectionReport in this example). Now, still in the sub-report properties, go to the parameters tab and set the Inspection parameter to the the InspectionID field.

Now when you run the report, you will get your sub-report run once for each selected parameter value with that parameter passed to the sub-report.

Hope that makes sense, I'm not near my PC so I can't provide a sample with images at the moment.

Upvotes: 1

Related Questions