How to use Lookup functions SSRS

• by Samuele Resca

SSRS Lookup functions allow you to combine data from two datasets in a single data region.
There are different types of lookup functions:

  • Lookup
  • LookupSet
  • MultiLookup

Lookup

The Lookup function is used for an 1-1 relation between two dataset.

LookupSet

The LookupSet function is used for an 1-N relation between two dataset:

Usually, LookupSet is combined with Join() function:

Lookup and LookupSet example

In this example, the lookup function join the Products Dataset and the Suppliers dataset using the SupplierID:

Lookup functions SSRS - Supplier products Diagram

Supplier – products Diagram

The column Vendor is calculated using this expression:

Result:

Lookup functions SSRS - Perview LookupFunction example

Perview lookup function example

MultiLookup

The MultiLookup function is used for an N-N relation between two dataset:

The difference between previous functions is that Fields!datasetA_IDs.Value is an array of values.
Usually, we can use the Split() function to convert delimiteted strings to a list of value: