How to handle null values in SSRS 2008

Question

my sharepoint list query is fetching null value as there are no rows  so  in my reporting server it gives an error "parameter values missing" . i used IsNothing like this in filter part in one of my dataset

expression:  true

operator: =

value:  =Iif(IsNothing(Parameters!Bench_Resource.Value(0)),"NA","present")

still its not working

---

thank you

Answer 1

Add a new Calculated Field (ex: CalcBench_Resource) in the Field Source, and set the expression to replace null with <Empty>.
Now filter your data by the calculated field, not the current field.

=IIF(Fields!CalcBench_Resource.Value=Nothing,"<Empty>",Fields!CalcBench_Resource.Value)

Answer 2

Hi Immortal12,

From your description, it seems that the parameter “Bench_Resource” retrieves values from the SharePoint list query, right? The error may occur if the query returns no value while the parameter doesn’t allow null value.

To avoid this issue, please open the Parameter Properties window, check the “Allow null value” and set the “Available Values” to “None”. If it is a multi-value parameter, please uncheck the “Allow multiple values” at this time. After that, modify the filter like below:
Expression: [Bench_Resource]
Operator: =
Value: =IIF(IsNothing(Parameters!Bench_Resource.Value),Nothing,Parameters!Bench_Resource.Value)

When the SharePoint list is updated and returns non-null values, we can modify the parameter and retrieve the parameter available values from the query.

Hope this helps.

Regards,
Mike Yin

Answer 3

Hello Immortal,

Please try out this.. this may help you..

in Expression field ..  : =IIF(IsNothing((Parameters!Bench_Resource.Value(0)), "1", Field!Resource.Value )

Operator : whatever is required for your report.

Type : (If it is integer, write it in expression as 1 else if it its string, write it as "1")

Value : =(IIF(IsNothing((Parameters!Bench_Resource.Value(0)), "1",Parameters!Bench_Resource.Value)

---

20CF