Post reply

How do I pass a user list as a parameter to a report?

  • June 12, 2008 at 7:57 am

    #188964

    I am looking for some help to provide a Text box that a user can paste in a list of values that can then be used as the @List parameter of an IN clause in the query that a report is based on. Now, this can either be done against the existing data set or some other means.

    Example of what I have in mind. I create a dataset that captures all machines for a predefined population (global in this case). My user wants to be able to pass in a list of Serial Numbers to the report and have it return a record for every member of the passed in list - whether or not a record is found (LEFT OUTER JOIN if possible). However, I would be willing to start with just returning a record where a value in the list was found; using an IN () clause.

    I am an expert SQL writer but have limited experience with SSRS; hence my inability to make this work. I have seen some code examples that can be used to create a comma-delimited list but that code was based on a multi-select drop down. So, I am hoping that I can do something similar with a text box in the report interface.

    Any help here would be greatly appreciated.

    Thank you in advance for your time and efforts.

    Rich

  • June 23, 2008 at 1:47 pm

    #833003

    I will assume that as an expert SQL writer, you are using a stored procedure for the dataset, if this is wrong let me know and I'll alter the hints accordingly.

    The other thing is that, once you have the full string value inside the stored procedure you would more than likely need to pass it through a function to strip it to multiple strings (we have a function to do this for us).

    The key then is how to get the string into the SP as a single string, and what I have done is to go to the parameters list area of the dataset, on the left of this you enter the parameter name that the SP is expecting. On the right side you enter the following expression (or similar): =JOIN(Parameters!SerialNumbers.Value,",").

    Good luck, let me know how you get on.

    Nigel West
    UK

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

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