none
Passing multi-value parameter in stored procedure ssrs

    Question

  • I have  customer parameter which is a drop down list in my report and I have set it to "allow multiple values". This is an SSRS report.

    How do I pass multiple values to my stored procedure?


    RJ

    Monday, April 16, 2012 8:24 PM

Answers

All replies

  • Hi Spydy

    Give this a whirl

    http://munishbansal.wordpress.com/2008/12/29/passing-multi-value-parameter-in-stored-procedure-ssrs-report/


    Best Wishes, The Redman; If something helps, please help show it by voting, if it solves, bonus!

    Monday, April 16, 2012 10:59 PM
  • Hi ,

    Create a Table valued function in SQL Functions  as below 

    Step 1

    CREATE FUNCTION [dbo].[FnSplit]
    (
    @List nvarchar(2000),
    @SplitOn nvarchar(5)
    )  
    RETURNS @RtnValue table 
    (

    Id int identity(1,1),
    Value nvarchar(100)

    AS  
    BEGIN
    While (Charindex(@SplitOn,@List)>0)
    Begin 
    Insert Into @RtnValue (value)
    Select
    Value = ltrim(rtrim(Substring(@List,1,Charindex(@SplitOn,@List)-1))) 
    Set @List = Substring(@List,Charindex(@SplitOn,@List)+len(@SplitOn),len(@List))
    End 

    Insert Into @RtnValue (Value)
    Select Value = ltrim(rtrim(@List))
    Return
    END

    Step 2 in your store procedure change the parameter where condition something like below

    ALTER PROCEDURE [dbo].[SomeSP] 

    -- Add the parameters for the stored procedure here
    @CostCentre NVARCHAR(255)

    SELECT

    [ProjectCode],[ProjectName],[ProjectManager],SUM([Hours]) AS [Hours MTD]FROM dbo.Rpt_NRMA_CATS NC

    INNER JOIN PeriodID P ON NC.PeriodID=P.PeriodID
    WHERE 
    ([CostCentre]) collate database_default IN(SELECT Value FROM dbo.FnSplit(@CostCentre,','))

    END

    I hope this will help you.


    Dasari

    Tuesday, April 17, 2012 12:14 AM
  • Hi RJ,

    In SQL Server Reporting Services, multi-value parameters are passed to SQL stored procedures as a comma-delimited string of the values. We need to parse/split the comma-delimited string into values that can be used in “IN” clause or operator. For the details, you can refer to the article below:
    http://blog.summitcloud.com/2010/01/multivalue-parameters-with-stored-procedures-in-ssrs-sql/

    Hope this helps.

    Regards,
    Mike Yin

    Thursday, April 19, 2012 3:22 AM
    Moderator
  • This was helpful.

    Even for passing Text value with comma separated in a single value parameter we can use function.


    Thanks Shiven:) If Answer is Helpful, Please Vote

    Friday, July 06, 2012 2:40 AM
  • Hi Jeevan Thanks for the info..

    I followed your instructions and everything works fine but my SELECT ALL is not working in thedropdown list and it is retrieving the data from only a selected items and not for all items..??

    Any Help pls..??

    Wednesday, November 07, 2012 6:59 PM
  • Check if your list is a unique list and contain no null value
    Tuesday, December 04, 2012 5:32 PM
  • Looks like you need to redefine the parameter (input) in your stored procedure and the function to be larger. I usually use NVARCHAR(MAX).

    Changing the code to the following may reduce the possibility of bugs:

    CREATE FUNCTION [dbo].[FnSplit]
    (
    @List nvarchar(MAX),
    @SplitOn nvarchar(MAX)
    )  

    Saturday, December 29, 2012 12:41 AM
  • Hi Jeevan,

    I have the following procedure, I have used the same split function you have mentioned above but I am unable to get all the records. It is displaying just the first row. In SSRS i have selected multiple values property and set the default values for the parameters by using an other query. Any hint why it is not working? I have tried in both 2008R2 and 2012.

    ALTER PROCEDURE [dbo].[Multiple_Qualification_Summary]
    (
    	 @qual_codea nvarchar(10),
    	 @qual_codeb nvarchar(10)
    )
    AS
    
    select DISTINCT 
    	 COUNT(b.PRN) as Total
    	,A.UKCC_Qualification_Code AS 'Qualification Code A'
    	,b.UKCC_Qualification_Code as 'Qualification Code B'
    from dbo.Practitioner_Register_Part_Status as a, dbo.Practitioner_Register_Part_Status as b, dbo.Qualification_Register_Part as c
    where a.PRN = b.PRN 
      and a.UKCC_Qualification_Code collate database_default IN (select Value from dbo.FnSplit(@qual_codea, ','))
      and b.UKCC_Qualification_Code collate database_default IN (select Value from dbo.FnSplit(@qual_codeb, ','))
      AND A.IsLatest = 1 AND B.IsLatest = 1
      AND A.UKCC_Qualification_Code = C.UKCC_Qualification_Code
      AND C.QUALIFICATION_ACTIVE_SWITCH = 1
     group by A.UKCC_Qualification_Code 
    	,b.UKCC_Qualification_Code 
    
    order by 1


    MH

    Tuesday, December 31, 2013 12:02 PM
  • In the dataset did you set the parameter to =JOIN(parameter!<parametername.value,",")??

    coreyb1969

    Thursday, February 27, 2014 4:01 PM
  • This does not work, I get the following error: Msg 207, Level 16, State 1, Line 31
    Invalid column name 'value'.
    Wednesday, December 23, 2015 6:03 PM
  • Posting an error out of context won't get you any help.

    Also, responding to dead threads is a bit of a no-no. Instead, open your own thread, and post full details of the issue you're facing and we'll be happy to help.


    Don't forget to mark helpful posts, and answers. It helps others to find relevant posts to the same question. Enjoyed my post? Hit the up arrow (left)
    Really enjoyed it? See my profile!
    My Tech Net Articles.

    Wednesday, December 23, 2015 6:05 PM