SSRS: Multi-valued Parameter as Stored Procedure Input

SSRS: Multi-valued Parameter as Stored Procedure Input

Few months back I wrote an article on Multi-valued Parameter as Stored procedure Input, which will work only for single character multi-valued input parameter.

Now let us see an example for SSRS report with Multi-valued Parameter as Stored procedure Input, which will work in all cases.

Example:

Create Stored procedure in AdventureWorks database as shown in the following code block:

USE AdventureWorks2012
GO
 
CREATE PROCEDURE  Shift_Get
@ShiftName NVARCHAR(500)
AS
BEGIN
;WITH CTE1
AS
(
SELECT CAST(('<i><j>' + REPLACE(@ShiftName, ',''</j></i><i><j>') + '</j></i>'  ) AS  XML) CSV
),CTE2
AS
(
SELECT CAST(i.query('./text()') AS  VARCHAR(100))CSV
FROM CTE1 
CROSS APPLY CSV.nodes('/i/j') As  x(i)
)
SELECT * FROM HumanResources.Shift 
WHERE Name  IN (SELECT * FROM CTE2)
END
 

Create Dataset pointing to this newly created Stored procedure in AdventureWorks database as shown below:



Create Dataset for Multi-valued input parameter as shown below:



Follow report parameter settings as shown in these images:






From Toolbox - > Report Items - > drag and drop Table on to the report body under Design pane
and then assign the column values with fields from dataset (Report_Dataset).

Now click on Preview pane, select the parameter values and click on View Report:

  

Note: I didn't make use of the SSRS JOIN function


See Also


Sort by: Published Date | Most Recent | Most Useful
Comments
Page 1 of 2 (13 items) 12