Search code examples
reporting-servicesssrs-2008ssrs-2008-r2

Integrating a parameter inside a SSRS lookup expression?

I am attempting to control the visibility of an error message with the following code:

=IIf((Lookup(Parameters!FA.Value, Fields!fa_num.Value,Fields!LastName.Value,"Advisor_Numbers")=""),False, True)

What this theoretically should do is look at the FA # inputted by the user, then look within the "Advisor_Numbers" dataset for a match in fa_num...and once found to check if the last name of the advisor is empty. If this is true: return false. Otherwise, return true.

This code always returns false despite the sample FA numbers I use existing in the Advisor_Numbers table. Is my syntax for writing a parameter within a lookup incorrect?

Ideally I'd just like to lookup if the parameter exists within the table without involving the last name.

Solution

  • I found the solution to my own problem.

    While everything looked fine on the surface, there was probably some empty space somewhere in my data. Therefore, make sure to use Trim.

    =IIf((Lookup(Trim(Parameters!FA.Value),Trim( Fields!fa_num.Value),Trim(Fields!LastName.Value),"Advisor_Numbers")=""),False, True)

    The code above worked smoothly and solved all my issues