June 10, 2014

SSRS - Passing Multiple Parameters with Stored Procedure

1) Add data source as needed.
2) Create dataset as needed. Below, you can see there are 4 parameters. We will focus on contractname dataset for multi values parameter.
3) You can see, TSQL stored procedure is used to get the list of contract name for contractname dataset. This stored procedure returns the list of contract name.

4) Then you need to go to parameter. Right click on contractname parameter -> go to properties.
5) Define the values of dataset from Available Values.

6) In the general tab of the properties, select Allow multiple values

7) Run the report. You will be able to see as below.

8) When the parameter is passed on the stored procedure, it should be like
Select * from ContractTable where ',' + @contractname + ',' Like '%,' + Contract + ',%'
and date between @startfiscalyear and @endfiscalyear and quarter = @quarter

No comments: