Post reply

Queried parameter with a NULL default?

  • August 16, 2010 at 9:41 am

    #387585

    I'm trying to retrieve a queried list of parameter values, but want to also allow the user to skip the parameter if needed. It seems like the system wants to help me, by checking Allow Null Values, and then under Default Values, I should be able to Specify Values, adding one with value (Null) and accepting that.

    However, this still requires a value to be selected.

    Is the only way around this to add a UNION to my queried values, with 'All' as an option, and then evaluating the parameter value before passing to the sp, where if 'All' then pass NULL?

    Or is there a setting somewhere that I'm missing that would allow the user to ignore that parameter if it is not known?

    ---------------------------------------------------------
    How best to post your question[/url]
    How to post performance problems[/url]
    Tally Table:What it is and how it replaces a loop[/url]

    "stewsterl 80804 (10/16/2009)I guess when you stop and try to understand the solution provided you not only learn, but save yourself some headaches when you need to make any slight changes."

  • August 19, 2010 at 1:42 am

    #1208919

    Hi,

    For single-valued parameters, in your Variable properties, make sure that Allow Null values is checked, then enter the list of param values in the Available Values section; including one with label 'Ignore this parameter' and value NULL. Then set this particular label as your default value. When you run your report, the user can avoid setting this param and NULL is passed by default to the SP.

    In case you have a multi-valued parameter, the above cannot be done, as multi-valued parameters cannot have a NULL as part of the list. This is because the value of input param sent to your SP is a concatenated CSV list in which NULL cannot be added. In such a case, a value must be specified as default which is interpreted as NULL by the SP.

    Hope this helps.

    Thanks,

    Abhishek

  • August 19, 2010 at 6:56 am

    #1209076

    Thanks for the reply Abhishek, that's almost what I need, but my values for my Single-Valued Parameter come from a query, not entered specifically in the Available values. My current solution is as mentioned, UNION in the query with my 'All' choice, then evaluate the parameter in the call to the SP to see if it equals 'All', and if so, pass NULL.

    Thanks for the point about Multi-Valued Parameters not being able to include NULL, that probably would have driven me nuts in the future.

    ---------------------------------------------------------
    How best to post your question[/url]
    How to post performance problems[/url]
    Tally Table:What it is and how it replaces a loop[/url]

    "stewsterl 80804 (10/16/2009)I guess when you stop and try to understand the solution provided you not only learn, but save yourself some headaches when you need to make any slight changes."

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

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