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:

About Samuele Resca

Samuele Resca is an Microsoft MVP Visual Studio and Development Technologies, Software Engineer, specializing mainly on ASP.NET MVC and in general about everything that revolves around the web. Samuele was born in 1994, and works as a software developer @ YOOX NET-A-PORTER Group He loves the MVC frameworks, ASP.NET MVC, Javascript, Node.js and Typescript.