SSRS 2005 - Multi-value parameters and allowing blank values

• Vicki Peters

  SSCommitted

  Points: 1866

  More actions

  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!

• Jack Corbett

  SSC Guru

  Points: 184394

  More actions

  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

• Vicki Peters

  SSCommitted

  Points: 1866

  More actions

  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!