Resources For IT Professionals
Post an article
Wikis - Page Details
  • First published by (8Microsoft Community Contributo)
  • When: 9 Sep 2013 3:34 AM
  • Last revision by (eMicrosoft Partne)
  • When: 17 Sep 2013 11:54 AM
  • Revisions: 8
  • Comments: 8
Options
RSSSubscribe to Article (RSS)
Can You Improve This Article?
Positively! Click Sign In to add the tip, solution, correction or comment that will help other users.

Report inappropriate content using these instructions.
Wiki  >  TechNet Articles  >  SSRS: Multi-valued Parameter as Stored Procedure Input

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 SSRS JOIN function .

See Also


, , , , , [Edit tags]
Leave a Comment
  • Please add 1 and 5 and type the answer here:
  • Post
Wiki - Revision Comment List(Revision Comment)
Sort by: Published Date | Most Recent | Most Useful
Comments
  • 9 Sep 2013 4:47 AM

    Naomi  N edited Revision 2. Comment: Formatting

  • 9 Sep 2013 3:39 AM

    SathyanarrayananS edited Original. Comment: minor edit

Page 1 of 1 (2 items)
Wikis - Comment List
Sort by: Published Date | Most Recent | Most Useful
Posting comments is temporarily disabled until 10:00am PST on Saturday, December 14th. Thank you for your patience.
Comments
  • Posted by
    on 9 Sep 2013 3:39 AM

    SathyanarrayananS edited Original. Comment: minor edit

  • Posted by
    on 9 Sep 2013 4:47 AM

    Naomi  N edited Revision 2. Comment: Formatting

  • Posted by
    on 9 Sep 2013 4:59 AM

    It may be a good idea to add See Also section to this (and other articles) and link to your other SSRS articles.

  • Posted by
    on 9 Sep 2013 5:00 AM

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

  • Posted by
    on 9 Sep 2013 9:00 AM

    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

  • Posted by
    on 9 Sep 2013 9:05 AM

    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 !!

  • Posted by
    on 14 Sep 2013 9:50 AM

    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: www.sqlperformance.com/.../split-strings

  • Posted by
    on 15 Sep 2013 1:20 AM

    Hi Pituach,

    Thanks for sharing very good article on splitting technique !!!

    I will recommed and implement CLR SPLIT function in my future articles :)

Page 1 of 1 (8 items)