reporting services - SSRS multi-value parameter using a stored procedure

Question

I am working on a SSRS report that uses a stored procedure containing a few parameters. I am having problems with two of the parameters because I want to have the option of selecting more than one item.

Here's a condensed version of what I have:

CREATE PROCEDURE [dbo].[uspMyStoredProcedure] 
  (@ReportProductSalesGroupID AS VARCHAR(MAX) 
  ,@ReportProductFamilyID AS VARCHAR(MAX)
  ,@ReportStartDate AS DATETIME
  ,@ReportEndDate AS DATETIME)

--THE REST OF MY QUERY HERE WHICH PULLS ALL OF THE NEEDED COLUMNS

WHERE DateInvoicedID BETWEEN @ReportStartDate AND @ReportEndDate
AND ProductSalesGroupID IN (@ReportProductSalesGroupID)
AND ProductFamilyID IN (@ReportProductFamilyID)

When I try to just run the stored procedure I only return values if I enter only 1 value for @ReportProductSalesGroupID and 1 value @ReportProductFamilyID. If I try to enter two SalesGroupID and/or 2 ProductFamilyID it doesn't error, but I return nothing.

-- Returns data
EXEC uspMyStoredProcedure 'G23',     'NOF',     '7/1/2009', '7/31/2009'

-- Doesn't return data
EXEC uspMyStoredProcedure 'G23,G22', 'NOF,ALT', '7/1/2009', '7/31/2009'

In SSRS I get an error that says:

Incorrect syntax near ','

It appears that the , separator is being included in the string instead of a delimiter

Answer 1

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'):