locked
ssrs passing parameters (multi values) RRS feed

  • Question

  • I have in an SSRS one parameter. (I defined myself the available values).

    Then the dataset is a SP I run, in the SP I have

    @Partner nvarchar(max)

    and in the where clause:    and ar.Partner in (@Partner)

    When I run the SSRS, if I select only one parameter works perfect but, when I do multiple it returns nothing... Why could this be happening?

    I want the parameters to be passed as 'value1', 'value2'... not as 'value1,value2'

    Tuesday, July 3, 2018 10:40 AM

All replies

  • its because of the way parameters are getting passed

    you need to do a small tweak if you want  to work with multi valued parameter values

    like this

    and ar.Partner in (select Value from String_Split(@Partner,','))

    assuming your using version on above SQL 2016

    for earlier version you need to create a UDF instead like

    https://visakhm.blogspot.ae/2010/02/parsing-delimited-string.html

    and use like

    and ar.Partner in (select Val from dbo.ParseValues(@Partner,','))


    Please Mark This As Answer if it solved your issue
    Please Vote This As Helpful if it helps to solve your issue
    Visakh
    ----------------------------
    My Wiki User Page
    My MSDN Page
    My Personal Blog
    My Facebook Page


    Tuesday, July 3, 2018 11:05 AM
  • Hi maca128

    According to your description , it seems that you think it is an syntax issue about the punctuation in sql query , right?

    Per my understanding , if you use the normal select query like ‘ select * from table name where field in (@parameter1)’  , the string like ‘value1,value2,value3 ‘ is worked . while the string like ‘’value1’,’value2’’ is not work.  You could have a try.

    However, if you choose to use stored procedure for dataset. And in the SP, you use the query which is splice by different strings .   then in this scenario you could do the escape (or Translation ) for your query ‘s parameter. So the string for parameter like ‘’value1’,’value2’’ might work. Even though , you still have to determine which format of string you need to pass to the parameter in sql query . then ,get to know how the format of string for parameter like in SSRS .

    If you need string like ‘’value1’,’value2 ’’

    You could create a parameter (sequence is the last) then add the expression like below for the available value :

    ="''"+join(Parameters!ReportParameter2.Value,"'',''")+"''"

    Or you could do the escape in the query.


    Hope it can help you.

    Best Regards,

    Eric Liu


    Best Regards, Eric Liu MSDN Community Support Please remember to click Mark as Answer if the responses that resolved your issue, and to click Unmark as Answer if not. This can be beneficial to other community members reading this thread.


    Wednesday, July 4, 2018 3:08 AM