February 6, 2012 at 1:49 pm
I inherited a report that needed some modifications and I noticed a few rows had the too-familiar "#Error" in a certain column. Easy enough fix - the underlying data, an integer, must have been null. I added an IIF(col.Value is Nothing, , ) and expected the problem to be fixed. Much to my suprise, it was not.
It turns out that the column's integer value was being converted to a string so that " Months" could be concatenated. So, the formula turned out to be something like this:
=IIF(col.Value Is Nothing,"?",col.Value.ToString()) + " Months"
STILL the #Errors kept appearing! After a good deal of experimenting, I came to the conclusion that the SSRS report processor, as it grabs each new row of data, looks at the entire expression for a given row, and decided that trying to take a .ToString() of a Null is an error, even though the IIF should prevent that from ever happening!
I finally had to resort to using a report code function, as described in another post titled "IIF #Error" to test for Null and return either a string "?" or " Months" - most unhelpful of SSRS!
Larry Schmidt
United Health Group IT
February 8, 2012 at 2:51 pm
Have you tried this in the expression of the textbox?
=iif(IsNothing(col.Value),"?",col.Value.ToString() & "Months")
February 8, 2012 at 3:30 pm
Yes, in fact, that was the first attempt - same result - #Error.
Again, I strongly suspect that SSRS grabs a row of data, scans the report definitions, and decided that .ToString() is not gonna work with a Null, even though the IIF prevents it from ever having to actually encounter it!
February 9, 2012 at 11:24 am
Go for customization of the expression, something like this
pseudo code :
Public Function fn_getString(a)
dim b as string
b = Convert a to string
if a is NULL
return "?"
else
return b & "Month"
end if
End Function
February 9, 2012 at 12:43 pm
See last paragraph of my original posting - here's the function I actually used (although my original complaint still holds, that SSRS should not have made this necessary!):
PUBLIC FUNCTION NLString(ColData) AS String
IF ColData is Nothing THEN
RETURN "?"
ELSE
RETURN ColData.ToString()
END IF
END FUNCTION
Thanks for the reply, though!
February 9, 2012 at 12:45 pm
. . . and, then, in SSRS:
=code.NLString(Fields!ProjectDuration.Value) + " Months"
So, basically, we are "fooling" SSRS into not worrying about whether that column's value is Null!
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply