SSRS Lookup #Error on some rows

ssrsssrs-2012

I'm having trouble working out the details with an IIF containing some lookup statements.

The setup:

Dataset 1: Transactions
Fields: itemid, transactionnotes, rdesc

Dataset 2: EmployeeInfo
Fields: code, fname, surname

I have an expression column with the following:

=iif(Fields!itemid.Value = 100785, 
    "Allocated to: " 
    & lookup(cint(Fields!transactionnotes.Value), fields!code.value, fields!fname.value, "EmployeeInfo") 
    & " " 
    & lookup(cint(Fields!transactionnotes.Value), fields!code.value, fields!surname.value, "EmployeeInfo")
  , Fields!rdesc.Value)

itemid = 100785 indicates a line in the DB where it records a transaction allocated to another staff member. In all isntances where the itemid = 100785 the transactionnotes field (datatype string) will contain a numeric value of who it was allocated to. For those lines, this expression is working exactly as intended.

The problem:

For rows that do not contain itemid = 100785, the rdesc value should just be a string description of the transaction. For about half the rows this is working fine. But for about half the rows it shows #error. I've dug into the DB and the transactionnotes.value is NULL for all the rows getting #error. But its also NULL for the rows that are working. I'm including a screenshot so you can see the relevant values for itemid and rdesc for some rows that work and some that get #error. Description Calc is the field with the expression.

enter image description here

Best Answer

If I had to guess I'd say that the datatype of transactionnotes does not match the datatype of code. I suggest trying ...

=iif(Fields!itemid.Value = 100785, 
    "Allocated to: " 
    & lookup(cint(Fields!transactionnotes.Value), cint(fields!code.value), fields!fname.value, "EmployeeInfo") 
    & " " 
    & lookup(cint(Fields!transactionnotes.Value), cint(fields!code.value), fields!surname.value, "EmployeeInfo")
  , Fields!rdesc.Value)

or perhaps it should go the other way using CStr() instead of CInt() for both transactionnotes and code. Try it out and see.