Allow nulls and blanks with multi-valued parameters

SSAS and SSRS
1

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?

juniormint

2

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

YouDataField in ('Value1','Value2)

use this:-

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

3

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.

4

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?

5

image
image.png1600×900 179 KB

image
image.png1600×900 188 KB

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