SSRS - Expression returning error if null

Question

I'm expecting only the names to appear on the calendar if they exist and nothing if they don't.

I created an expression where it returns only the last name of a person by using the comma as a delimiter.

My current expression:

=iif(IsNothing(Fields!EmployeeName.Value), nothing, Left(Fields!EmployeeName.Value,-1 + InStr(Fields!EmployeeName.Value, ",")))

Current results where #Error appears if a name doesn't exist:

score 2 · Accepted Answer · answered Dec 13 '16 at 16:52

The error is occuring because you are passing in a number less than 0 to the left function. When your string does not have a comma in it you are passing in -1.

To handle this I added two if statements to the expression. The first will return the whole string if the index of the first comma is 0. The second checks for the -1 condition and passes a 0 to the left function when that occurs.

=
iif(
    IsNothing(Fields!EmployeeName.Value), 
    nothing, 
    iif(
        InStr(Fields!EmployeeName.Value, ",") = 0, 
        Fields!EmployeeName.Value, 
        Left(Fields!EmployeeName.Value,iif(-1 + InStr(Fields!EmployeeName.Value, ",") < 0, 0, -1 + InStr(Fields!EmployeeName.Value, ",")))
        )
    )

SSRS - Expression returning error if null

1 Answers1