Post reply

Report Using Stored Proc with Parameters

  • December 16, 2016 at 6:39 pm

    #324064

    Hello,

    Been a LONG time since looking at SSRS and have been tasked with creating a new report. I've created a stored procedure that has several parameters. So I have a few questions on how SSRS handles parameters.

    1. When setting the Report Parameter as a Multi-Value, how does it pass the data to the query? CSV list? One value at a time? Currently I have the procedure doing the following but not sure if I need to update for how SSRS sends in the values:

    AND (ISNULL(@Part_Key_List, 0) = 0 OR CHARINDEX(CONVERT(VARCHAR(25), P.Part_Key), @Part_Key_List, 1) > 0)

    2. I see that you can't have a Multi-Value parameter also allow Nulls, but what if the user doesn't want to select anything? When going to Preview it is still requesting a value be entered in the parameter? How does everyone else handle this type of situation?

    TIA for any help!! 🙂

  • December 21, 2016 at 3:23 am

    #1919107

    Multi parameter will pass the list as comma separated. This would mean, for example, that a list of people would better sent like:

    GetSales @Users = 'Steve,Jane,Paul'

    You would need to handle in your SP getting those split out. If you haven't got anything all ready on your server, have a look at the SQL 8K “CSV Splitter” Function[/url].

    Multi values can pass NULLs, but have you have to ensure that it's available to choose. For example, say your parameter is getting it's available values from your table employee. So you simply have a dataset with the following to give your parameter is available values:

    SELECT Name

    FROM employee;

    In there, I highly doubt you have an employee with a name of NULL. As a result, even though you're happy to accept a NULL value, it's not an option for the user. Instead you'd need:

    SELECT Name

    FROM employee

    UNION

    SELECT NULL;

    This will add NULL as a valid option for your parameter, and it will appear in your drop down menu on the GUI.

    Hope that helps.

    Thom~

    Excuse my typos and sometimes awful grammar. My fingers work faster than my brain does.
    Larnu.uk

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

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