Stored Procedure with IN operator in SSRS

Data source: sample data

Data set: stored procedure

Create PROC dbo.uspSales

@Region nvarchar(50)

AS

BEGIN

SET NOCOUNT ON;

DECLARE @pRegion nvarchar(max)

SET @pRegion = REPLACE(@Region,',',''',''')

DECLARE @SQLCode varchar(max)

--place result set into a temporary table to display the fields

SET @SQLCode='

SELECT Year([OrderDate])[OrderDate]

,[Region]

,[Representant]

,[Category]

,[Item]

,[Units]

,[UnitCost]

,[Total]

INTO ##TempResult

FROM [dbo].[salesCat]

where Region in ('''+@pRegion+''')'

EXECUTE (@SQLcode)

SELECT * FROM ##TempResult

DROP TABLE ##TempResult

END

--------------------------------------------

Exec dbo.uspSales 'Europe,America'

Using a dynamic query and global temporally table. If I would use just the dynamic SQL query, SSRS won't display the fields at all.

Step 1

Add dataset, click on Stored Procedure and find it on the list. Click Refresh Fields button, provide a value for the parameter to display the fields.

Step 2

Add second dataset. This will be the input for the parameter.

Step 3

Adding second dataset

Step 4

Parameter properties

1. GENERAL: Allow multiple values

2. AVAILABLE VALUES: Get values from a query

Test the report