How to use – Lookup, LookupSet and Multilookup function in SSRS (Dynamics 365)

Using the Lookup, LookupSet and Multilookup functions we can basically combine values from 2 different data set.

The definition of them are

Lookup/LookupSet/Multilookup(source_expression, destination_expression, result_expression, dataset)

source expression – the field from the source data set (for e.g. primary key)

destination expression – the field to be matched from the destination dataset (sort of foreign key)

result expression – the field whose value has to be returned from the destination dataset

dataset – destination dataset name.

For e.g. we have custom entity Team and Team Members (1 à n)

We have following two DataSet one for Team and one for Team Members.

1. DSTeam
   

1. DSPlayer
   

The sample report has the Tablix which is bound to DSTeam and has two columns one which is bound new_name field of DSTeam dataset and the second column which gets the player name from DSPlayer dataset.

Output:

Lookup function picks the “first matching value” from the second dataset DSPlayer.

To get all the matching we need to use the LookupSet function (along with Join)

Output:

To understand Multilookup, let us create one more entity Tournament that will have a text attribute which stores comma separated team name.

Let us create one more dataset DSTournament and bound it to a new table. Which have one column which lists the Tournament name and the other column that does the multilookup to DSPlayer to get the player name.

It brings the first matching value, by searching (doing lookup) on multiple value i.e. India and South Africa

Hope it helps..