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