SSRS: Lookup Functions

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.

Comments are closed.