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.
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).
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.
Try using IsDBNull
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With