There are three lookup functions in SSRS 2008 R2: Lookup, LookupSet and MultiLookup.

Lookup

The lookup function compares a value in the current scope to a value in a destination dataset and returns a single value from the destination dataset if a match is found.  The Lookup function is used when there is a 1:1 relationship.

The syntax is:

Lookup(source_expression, destination_expression, result_expression, dataset)

Lookup returns a single value in result_expression.

LookupSet

The LookupSet function compares a value in the current scope to a value in a destination dataset and returns a list of values from the destination dataset based on matches found.  The LookupSet function is used when there is a 1:many relationship.

The syntax is:

LookupSet(source_expression, destination_expression, result_expression, dataset)

LookupSet returns a list of values in result_expression.

MultiLookup

The MultiLookup function compares a list of values in the current scope to the values in the destination dataset and returns a list of values from the destination dataset based on matches found.  MultiLookup is equivalent to calling the Lookup function for a set of key values and is used when there is a 1:1 relationship.

The syntax is:

MultiLookup(source_expression, destination_expression, result_expression, dataset)

The parameter source_expression contains a list of values.  The result_expression is also a list of values.

The following caveats apply to the lookup functions:

• The lookup functions are evaluated after all filters are applied.
• Only one level of lookup is supported.
• Source and destination expressions must evaluate to the same data type.
• Source, destination, and result expressions cannot include references to report or group variables.
• Lookup cannot be used as an expression for the following report items:
• Dynamic connection strings for a data source.
• Calculated fields in a dataset.
• Query parameters in a dataset.
• Filters in a dataset.
• Report parameters.
• The Report.Language property.