SSRS multi-value parameter using a stored procedure

by

You need three things:

  1. In the SSRS dataset properties, pass the multi-value param to the stored procedure as a comma-delimited string 

    =Join(Parameters!TerritoryMulti.Value, ",")

  2. In Sql Server, you need a table-value function that can split a comma-delimited string back out into a mini table (eg see here). edit: Since SQL Server 2016 you can use the built-in function STRING_SPLIT for this

  3. In the stored procedure, have a where clause something like this:

    WHERE sometable.TerritoryID in (select Item from dbo.ufnSplit(@TerritoryMulti,','))

    … where ufnSplit is your splitting function from step 2.

(Full steps and code in my blog post ‘SSRS multi-value parameters with less fail‘):

More Related Contents:

  1. Compare SQL Server Reporting Services to Crystal Reports [closed]
  2. SSRS 2008 R2 – SSRS 2012 – ReportViewer: Reports are blank in Safari and Chrome
  3. Does the iif function compute both paths in SSRS or is it short-circuited?
  4. SSRS Expression Divide by Zero Error
  5. Reporting Services export to Excel with Multiple Worksheets
  6. When to use RDLC over RDL reports?
  7. SSRS 2005 Set SimplePageHeaders on the report instead of the server?
  8. SSRS 2008 R2 – SSRS 2012 – ReportViewer: Reports in Safari/Chrome but works fine in Firefox/Internet Explorer 8… why?
  9. SSRS – How to build a simple multi-column report?
  10. SSRS – How to continue data to next column?
  11. SSRS distinct lookupset function
  12. SSRS Conditional Formatting Switch or IIF
  13. How to combine aggregates within a group with aggregates across groups within SSRS
  14. Pivot a table in reporting services?
  15. Access Page number in report body In SSRS
  16. The permissions granted to user ‘ are insufficient for performing this operation. (rsAccessDenied)”}
  17. hide/remove header and footer
  18. Passing multiple values for a single parameter in Reporting Services
  19. Left Outer Join Not Working?
  20. SSRS 2014 report deploy issue
  21. Changes to parameter not showing on Report Server after deployment
  22. Sql PIVOT and string concatenation aggregate
  23. Tablix: Repeat header rows on each page not working – Report Builder 3.0
  24. divide by zero/null workaround in SSRS 2008 report
  25. Could not load file or assembly ‘Microsoft.ReportViewer.Common, Version=11.0.0.0
  26. SSRS report definition is newer than Server
  27. Need help in calculation using two Datasets using Expression SSRS
  28. How do I format date and time on ssrs report?
  29. Optional multi-valued parameters in SSRS
  30. Programmatically Export SSRS report from sharepoint using ReportService2010.asmx

Leave a Comment