Post reply

Having a programmer pass a CSV text string variable to a parameter in an SSRS Report using 2012

  • December 28, 2015 at 8:57 am

    #303414

    I am trying to create a report to receive a text string parameter, such as :

    'American Dad', 'Archer', 'Family Guy', 'King of the Hill', 'Robot Chicken', 'Simpsons', 'Venture Bros.' into parameter name : tvshowlist

    (Actual parameter names changed to prevent the monotonous) 😛

    I then want to use a T-SQL query command to return records for the title of the shows, such as:

    WHERE TV_Table.Showtitle IN(@tvshowlist)

    Any suggestions would be appreciated.

    Thanks in advance!

  • December 28, 2015 at 9:21 am

    #1848285

    Either use the "Allow multiple values" option in SSRS. Link

    Or use the DelimitedSplit8k in your query. Link[/url]

    Luis C.
    General Disclaimer:
    Are you seriously taking the advice and code from someone from the internet without testing it? Do you at least understand it? Or can it easily kill your server?

    How to post data/code on a forum to get the best help: Option 1 / Option 2

  • December 28, 2015 at 9:22 am

    #1848286

    time 2390 (12/28/2015)

    I am trying to create a report to receive a text string parameter, such as :

    'American Dad', 'Archer', 'Family Guy', 'King of the Hill', 'Robot Chicken', 'Simpsons', 'Venture Bros.' into parameter name : tvshowlist

    (Actual parameter names changed to prevent the monotonous) 😛

    I then want to use a T-SQL query command to return records for the title of the shows, such as:

    WHERE TV_Table.Showtitle IN(@tvshowlist)

    Any suggestions would be appreciated.

    Thanks in advance!

    Hello and welcome to the forums.

    As far as string splitters go, Jeff Moden has an article on one of the best. See http://www.sqlservercentral.com/articles/Tally+Table/72993/. With the ITVF, you'll be able to accomplish what you're after with something like this:

    WHERE TV_Table.ShowTitle IN (SELECT Item

    FROM dbo.DelimitedSplit8K(@tvshowlist, ','))

    You won't have to include extra quotes in your string parameter either because the string will be treated like a table. There's a lot of cool stuff in there to learn, but if you aren't familiar with it yet, it will change your expectations of performance. You'll also find it's useful in more places than you thought. Enjoy!

    Tally Tables - Performance Personified
    String Splitting with True Performance
    Best practices on how to ask questions

Viewing 3 posts - 1 through 3 (of 3 total)

You must be logged in to reply to this topic. Login to reply