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
  • It may be a good idea to add See Also section to this (and other articles) and link to your other SSRS articles.

  • Also, why do you use nvarchar(500) parameter? Why not nvarchar(max)?

  • Hi Naomi,

    Do we have anything like consolidated lists for SSRS ,like we have for T-SQL -  social.technet.microsoft.com/.../17785.sql-server-query-language-transact-sql.aspx

  • Considering this example , the input parameter can be maximum of

    select len('day,night,evening') . I guess , I had given enough size for input parameter , let me know if it needs to be changed !!

  • Nice article.

    * the splitting part in the input parameter @ShiftName better to do using CLR SPLIT function. this is a must function in any server i think! If you are not convinced by my recommendation then you can check this link: