reporting-servicesexpression

SSRS - EXPRESSION - HANDLING NULL VALUES AND LINEBREAK FOR AN CONCATENATED FIELD

I have a column that is concatenated; separated by a semicolon. I wrote an expression to check for null values and to get a line break at the semicolon. It is doing the right thing where there is data but for null values, I'm still getting an #Error is the columns where there is no data.

This is my expression...

=IIF(IsNothing(Fields!GOAL.Value), " ",Fields!GOAL.Value.ToString().Replace(";",vbCrLf))

Here is what I'm getting:

enter image description here

Solution

  • I think the problem is that both sides of the IIF() are evaluated, even if the result is never used.

    To get round this, you need to repeat the IIF() in the false part of the current expression, so that .ToString returns a valid value for the .Replace

    In the example below, we swap out Nothing with an empty string, but this can be anything as it will never get used anyway.

    This is untested but should work...

    =IIF(
    IsNothing(Fields!GOAL.Value)
    , " "
    ,IIF(IsNothing(Fields!GOAL.Value), "", Fields!GOAL.Value).ToString().Replace(";",vbCrLf)
    )