SSRS Send multi valued parameter filter to stored procedure
Normally in SSRS if you have a multi-value parameter (checkboxes), you can use IN (@param) on the list to filter your SQL query to find records whose value is in your list.
This works if you are building the SQL in SSRS.
What if you want to pass the search parameters to a stored procedure for reusability with other systems? You can't pass an entire IN clause.
Solution: Pass parameters as a comma separated string.
Your stored procedure will take input:
@SearchList varchar(max)
And will search for matching items using this filter:
(here I treat null/blank as matching everything)
(@SearchList IS NULL OR
@SearchList = '' OR
',' + @SearchList + ',' LIKE
'%,' + cast(MyTable.MyColumn as varchar) + ',%' )
All the commas and wildcards are to ensure a match regardless of how many items were in your search list. Note this doesn't support spaces in your search list, if you have them, you need to add some trims to the above.
Example: find "3,4,5" will match records with MyColumn = 3, 4, or 5, but not 6 or 50.
Calling this from SQL is easy, but how do you call it from SSRS? Remember, your parameter is a list, not a string.
Solution:
In SSRS, Data Properties window, choose Query Type = Stored Procedure.
Click refresh fields
Go to Parameters tab
Map parameters to the parameters in your report. Delete any where you want to pass null.
For the Multi-Valued parameter, you must use an expression to do the mapping. Click the function button, and use the 'JOIN' SSRS formula to convert the list to a string:
=JOIN(Parameters!SearchList.Value, ",")
Now it should all flow through.
Created By: amos 3/28/2014 11:46:36 AM
|
|