SQL 2005 Reporting Services if check for null

Question

In SSRS 2005 I have a table with a dataset linked to it. I want to check if the value of a field is null and if it is not null then format the data to make sure it has one decimal place and add a % sign etc.

This is my expression on the field/column:

=iif(IsNothing(Fields!COL01.Value), "" ,Format(CDbl(Trim(Replace(Fields!COL01.Value, "%", ""))), "N1") + "%")

It doesn't seem to work though when the data is null (It works fine if there is data). The report displays but the field shows up as #ERROR.

I think its checking to see if both cases are valid even though its null. I'm trying to use the if statement to avoid formating a null.

Answer 1

Try using IsDBNull

Answer 2

You could check for null in the SQL query instead of at the report level. Like IsNull(fieldname,0) then just format for the %. Provided of course your data is from SQL Server.

Answer 3

I would try using ISNULL(fieldname, 0) when querying for your dataset.

If you are connecting to a datasource without an ISNULL operator (ie. Oracle) then try using COALESCE(fieldname, 0), which iSeries, oracle and sql all support.

Answer 4

You shouldn't have to change the Data Source!!! SSRS report should be able to handle this, but it doesn't do it well. Developers don't always have the luxury of changing the query - most often not as the sproc may be used by other apps that handle the NULL quite well.

I've ran into this and have resolved it, but I'm having the issue again, and can't quite find it. The SSRS Report field expression should be able to handle this.

anyone?

Answer 5

SSRS expressions are evaluated using Visual Basic, which usually does a full (i.e. not a short-circuit) evaluation of all operands in an expression, e.g. in IIf(cond, truexp, falsexp), besides cond, both truexp and falsexp are evaluated and may throw regardless of the value of cond.

Since there doesn't seem to be a coalescing function in VB.NET 2.0, you might want to add one into the Code section of the report, e.g. for Decimal as returned from Oracle

Function Coalesce(fieldValue As Object, defaultValue As Decimal) As Decimal
  If IsDBNull(fieldValue) OrElse IsNothing(fieldValue) Then
    Coalesce = defaultValue
  Else
    Coalesce = CDec(fieldValue) 
  End If
End Function

It would be possible to define a generic function, too, e.g. Coalesce(Of TResult).