title: How to have a multi value parameter text box in SSRS that also allows blanks?
body: <p>I have an SSRS report that the user wants to be able to search upon multiple sites as supposed to just the one site that the report usually accepts. </p>
  
  <p>If they don't enter a site in the parameter text box then the report searches for all sites. </p>
  
  <p>I've handled this successfully within my SQL query in the where clause as shown below</p>
  
  <blockquote>
    <p>WHERE
    ( ( LEN(@ProjectId) > 0 AND  p.ProjectId = @ProjectId ) OR     ( LEN(@ProjectId) =
    0 ) )</p>
  </blockquote>
  
  <p>This worked fine for searching just the one site or all sites if they didn't enter a value. </p>
  
  <p>They want to be able to enter multiple sites and split them with a comma.</p>
  
  <p>I handled this within my SSRS report as defining the parameter in my data set to be </p>
  
  <blockquote>
    <p>=JOIN(Parameters!ProjectId.Value, ",")</p>
  </blockquote>
  
  <p>However this doesn't work for blank values or null values as expected.</p>
  
  <p>I've done some fancy expressions to try and get round this like</p>
  
  <blockquote>
    <p>=IIF(ISNothing(Parameters!ProjectId.Value), "", JOIN(Parameters!ProjectId.Value))</p>
  </blockquote>
  
  <p>To set the parameter passed to the SQL query to be blank and not to do the join if there isn't a value encountered. </p>
  
  <p>Basically I want to know if there is a way I can have a multi set parameter split by commas but passed as a blank if a value is not chosen. </p>
  
  <p>at the moment I've tried setting the parameter to allow multivalues which hasn't worked either I get the error message as shown below</p>
  
  <p><a href="https://i.stack.imgur.com/h2R83.png" rel="nofollow"><img src="https://i.stack.imgur.com/h2R83.png" alt="enter image description here"></a></p>