How do I build a true optional multi value parameter in SSRS 2008

1

I want to be able to use or not use an "Allow multiple values" parameter. So far, all I've found is how to add "blank" or "null" as a parameter default, but that only pulls all the values that are blank or null into the result set, it doesn't allow me to skip the parameter.

Also, I do not use a subroutine solution or "Get values from query", I allow users to type the list manually in a comma separated text box as (myFirstParam) with no settings and use a second dummy param (mySecondParam) checked to "Allow multiple values" with Default Specified value: =SPLIT(REPLACE(myFirstParam," ",""),",") and in the dataset query add: WHERE myField IN (@mySecondParam). I had to add @mySecondParam in the dataset parameter list, using the specified value as the expression.

If another solution is better, I will try anything, as long as data values are manually entered and entry is optional.

Thanks for any help you can offer.

2

In SSRS, you don't need to code the split for multi-value parameters. SSRS does it for you.

3

Thanks, Scott. I found just entering more than one item, with or without commas, simply hung the report until received Out of Memory errors. Yet ran fine with one number. Found this solution, and it does at least work. Don't know why, Any ideas on how to allow user to skip the parameter?

4

You can make the parameter optional and the user can skip it. You might have to assign a default value in order to make it optional, I don't remember off the top of my head.

5

That's the problem, none of the property settings that I can find work to really make it optional. Setting a Default Specified to ="Blank", for instance, simply pulls all records where the field is actually blank, not the same as skipping it.

Setting it to a dummy value like: ="NoEntry" and then editing the query to: WHERE (field IN (@MyParam) or @MyParam = 'NoEntry') pulls every single record with data present.

Do you have any other ideas?

6

Make the default "NULL". No value is ever "=" or "IN" NULL.

But, btw, wouldn't:
WHERE (field IN (@MyParam) or @MyParam = 'NoEntry')
only pull all rows if the param wasn't given a different value, and isn't what you want?