Thursday, July 28, 2016

#Error while showing DateTime field with Blank or Space in a SSRS Report...

In a SSRS Report, DataSet returns a column which contains datetime values in string data type.
Due to that it contains values with spaces or blank. Let assume the particular field name is "DateValueField".
To show that in the SSRS Report, initially the below expression is used.

=IIf(IsNothing(Fields!DateValueField.Value) OR LTRIM(RTRIM(Fields!DateValueField.Value))="", "", FormatDateTime(Fields!DateValueField.Value, DateFormat.ShortDate))

Eventhough the above expression seems to be correct, when we ran the report, it gives the value as #Error for the rows which contains Blank or Space for the DateValueField.
But it gives the correct value for the rows which contains a valid datetime value.

Actually the issue exists with SSRS IIF expression and to overcome that, we have to modify the IIF expression as below.

=IIf(IsNothing(Fields!DateValueField.Value) OR LTRIM(RTRIM(Fields!DateValueField.Value))="", "", FormatDateTime(IIf(IsNothing(Fields!DateValueField.Value) OR LTRIM(RTRIM(Fields!DateValueField.Value))="", Nothing,Fields!DateValueField.Value), DateFormat.ShortDate))

There may be other situations that the same issue may occur while using IIF expressions.