maandag, mei 14, 2007

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)

donderdag, mei 03, 2007

Complex tables do not export

At the moment, I'm creating some complex tables in Reporting services (2005).

In fact, the only two tables you can have are vertical tables and matrix tables. It is possible to create a horizontal table, but you have to rework a matrix to get to it.

The table I'm currently building in Reporting services involves nesting a vertical table inside a matrix.

As it seems, the table works fine, it exports well to pdf -- but excel won't work. It seems to be impossible to export complex nested tables to Excel.