SQLTeam.com | Weblogs | Forums

Allow nulls and blanks with multi-valued parameters



I am having problems with allowing nulls and blanks. I am confused as to where to put code to do this. Is it in each individual parameter or in the dataset for the main parameter, or both?

Also, I have seen various different types of codes, i.e. =IIF(Parameter!myParam.Value '', Nothing, Parameter(myParam.Value) - or something to that effect. And also in the parameter section of the dataset.

If someone can give me an example, I would really appreciate it. I have been over and over articles on the web and none of them make sense. Are there multiple ways to do this?



in your SQL code, in the where clause, insetad of :-

YouDataField in ('Value1','Value2)

use this:-

isnull(YouDataField,'Null') in ('Value1','Value2,'Null')


The standard way to do this thing is (in pseudo-code):

IF (parameter = '' or parameter is null) OR (column = parameter)

With local T-SQL variables, it looks like this:

WHERE (@parameter_1 IS NULL OR column_1 = @parameter_1) AND
(@parameter_2 IS NULL OR column_2 = @parameter_2)

Btw, Never use a function on a data column if you can avoid it.


Thanks for your responses! I have tried putting it in my stored procedure (and in procedure in other reports, too) and I keep getting the following message when I leave a parameter blank: "Please enter a value for the parameter myParameter. The parameter cannot be blank."

What am I supposed to do in the report itself and where?


Right click on Parameter-Click Parameter Properties-then check the box for Allow null values and Allow blank values