Default values in SQL Reporting

At the moment, I'm building some reports in SQL Reporting Services. A nice task, as always, and it does provide for some unexpected challenges once in a while.

Here's what happens in Reporting services when you use parameters – in fact, I had this particular issue quite a few times – I only just found how to get around it.

Problem description: When using a multivalue report parameter with all values selected by default, in fact, none is selected.

Solution: add the filter IS NOT NULL

I can't begin to explain how hard I hit my forehead with the palm of my hand when finding this one out J

Binabik

 

 

SSRS MultiValue and Nulls

When you create a multivalue parameter and you also want to include the null values when doing a filter (a select all), you got two options 1) create a union query to include null. Or (like me) -- you can modify the parameter with code. In the report properties, on the code tab, add the code below (it is probably not the most efficient code, but it works):

function addnulltoparameter(ByVal param as object) as object
dim strNewParam() as string
dim countery as int32
dim x as object
for each x in param
countery = countery + 1
next
redim strNewparam(countery + 1)
dim counterx as int32
counterx = 0
for each x in param
strNewParam(counterx) = x
counterx = counterx + 1
next
strNewParam(counterx) = ""
return strNewParam
end function

next, in the properties of you dataset, click the tab filters, create a filter on a field, choose in for the operator and in the value type :
code.addnulltoparameter(Parameters!name_of_the_parameter.Value) -- make sure you don't leave (0) at the end or you will only send the first element of the array.

The result is that a null value is concatenated into the parameter. If you want, you can also create a Boolean parameter, that allows you the choice to include nulls.

then the code for the filter would be
=iif(parameters!includenull=True,code.addnulltoparamater(Parameters!someparameter.Value),Parameters!someparameter.Value)