Post reply

SSRS 2005 - Multi-value parameters and allowing blank values

  • March 12, 2009 at 7:04 am

    #207297

    I have created a report with a drop-down that has a list of facility types (I'm selecting the values straight from a table). My issue is that it is supposed to be an optional parameter. I want the user to be able to select multiple values (including all) if so desired; however, I need to be able to pass in a blank value as well, as many of my records do not have a facility type.

    I accounted for both the the blank value and multiple values in my stored procedure. In the report I set the parameter to be multi-value and also checked the 'Allow blank value' box. I let the parameter default to null. When I run the report, it is forcing me to select a value for the facility type.

    Does anyone know a way around this, other than to modify my drop-down and union in 'fake' values of "All" and "", and then change my proc?

    Thanks!

  • March 12, 2009 at 7:17 am

    #958281

    I think the only way to do this is to union in a None or blank value into the dataset for the parameter list. You don't have to add all if you use the multi-value parameter type as it adds All automatically.

    Jack Corbett
    Consultant - Straight Path Solutions
    Check out these links on how to get faster and more accurate answers:
    Forum Etiquette: How to post data/code on a forum to get the best help
    Need an Answer? Actually, No ... You Need a Question

  • March 12, 2009 at 7:43 am

    #958307

    Thank you. It never occurred to me to only union in the blank value and allow the multi-value parameter to do its thing with 'Select All.'

    I was able to union in a value of '0' (since it's a string), display a value of 'None Specified,' and default the parameter to 0 so as not to confuse the users or leave it up to them to know to select that value.

    I then only had to change my stored proc from IF @FacilityTypes = '' to IF @FacilityTypes = '0'.

    Thanks so much for the tip. I still think it's ridiculous you are allowed to say you want to allow a blank value but it doesn't actually let you do it!

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

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