CODEWITHSUNDEEP
sql-server-2008reporting-servicesparametersssrs-2008
Luke K.
Luke K.

Reputation: 177

SSRS passing parameter to subreport

I have 2 tables which both have the column "countyID"

I have a main report that brings up reports based on a query like:

SELECT countyID, name, address, state
FROM TableA

---I have a parameter set on TableA where you select the Name to view its report.

I then have a second report based on a query like:

SELECT * 
FROM TableB

I want to use the second report as a subreport, as in, when you select the Name for the top report it should then list all records from TableB with the same CountyID.

Is there a way to set countyID as a parameter and then pass it to the subreport? This way the subreport would always only return records with matching countyID's to the currently selected record of the main report.

Upvotes: 2

Views: 28260

Answers (1)

user2620038
user2620038

Reputation:

I'm assuming that you've created a drop-down @Name parameter to load into the DataSet you're using for TableA. You've configured this Parameter to specify its values from the Name field of your TableA DataSet, which likely uses a query similar to:

SELECT CountyID, Name, Address, State
FROM TableA
WHERE Name = @Name

You would then need to create another parameter to store the relevant CountyID values from that DataSet, e.g. @CountyID. Set it to 'Hidden'. For the Available Values and Default Values, point it to the same TableA DataSet, but have it use the CountyID field for its values/labels.

You'd then need to pass the @CountyID parameter into your subreport. The DataSet for this tablix should be, like you outlined:

SELECT *
FROM TableB
WHERE CountyID = @CountyID

Are you sure you're using a subreport for this, and not just another tablix? The reason being, if you are using a subreport, you would need to open the subreport report object and create the @CountyID parameter on there as well, assuming that is where the TableB DataSet is. Set it to Hidden but don't worry about setting any values, since it will received from your Main report.

Lastly, go back to the original Main report, right-click on the subreport box that you dragged onto your report body, and go to Properties > Parameters > Set Name to CountyID , and the Value as [@CountyID] .

**Note: In SSRS, parameters have the annoying trait of being case-sensitive. It's always good to keep this in mind sooner rather than later in report development.

Upvotes: 2

Related Questions