Lookup Function in SSRS

Sometime during the report generation process we come in a situation where the report has multiple dataset and we need to combine the some data fields from two or more dataset in single data region  

Lookup functions allow you to combine data from two datasets in a single data region in report. There are three lookup functions available:

Note: I have attached LookupReport.rdl with respect to this post  Download

·         Lookup

·         Lookupset

·         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

Syntax

LookupSet(source_expression, Destination_expression, Result_expression, Dataset) Source_expression

(Variant) An expression that is evaluated in the current scope and that specifies the name or key to look up.

Destination_expression

(Variant) An expression that is evaluated for each row in a dataset and that specifies the name or key to match on.

Result_expression

(Variant) An expression that is evaluated for the row in the dataset where source_expression = destination_expression, and that specifies the value to retrieve

Dataset

A constant that specifies the name of a dataset in the report.

We have two dataset dataset1 and dataset2.

In this example  lookup function will look dataset1’s stateid in to datset2 stateid and will fetch dataset2 State Name into Resultant column

 

=Lookup(Fields!StateDataset1_ID.Value, Fields!StateDataset2_ID.Value,Fields!State_Name.Value,"DataSet2")

Finally your report output is as in pic. below

Note

1. If multiple matches are found, the value from the first matching row will be returned.
2. We cannot use any aggregate functions in the result_expression.

LookUpSet
In the Lookup () we have seen It is used to fetch the first matching value from the other DataSet. Now, if we want all the matching values from the other DataSet. There is another function  LookupSet.
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. 
Note: I have attached LookupsetReport.rdl with respect to this post Download
Syntax
LookupSet(source_expression, destination_expression, result_expression, dataset)

source_expression – The field which will act as the key/lookup value for the destination. This will be evaluated in the current scope – generally the DataSet with which the Tablix is bound.

destination_expression – The field in which the source expression will be looked in. This field will belong to the dataset provided in the same function as the last parameter. 

result_expression – The field we want to retrieve from the destination DataSet for the matching source_expression & destination_expression for each row. 

Dataset – The Dataset in which we want to look the values into.

Returns – A VariantArray, or Nothing if there is no match.

Note
         1. if multiple matches are found, all the values from the matching rows will be returned. 
         2. we cannot use any aggregate functions in the result_expression

In this example  lookupSet() function will look dataset1’s stateid into datset2 stateid and will fetch dataset2 city name in one column in comma separated value

=Join(LookupSet(Fields!State_ID.Value, Fields!State_ID.Value,Fields!city.Value,"DataSet2"),",")

Expected Result

Here we have used two function

1         LookupSet() – To get an Variant array of the matching values

2         JOIN () – To join all the elements of the array as a comma separated string

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.
Note: I have attached MultiLookupReport.rdl with respect to this post Download
Syntax
Multilookup(source_expression, destination_expression, result_expression, dataset)
source_expression – The field which will act as the key/lookup value for the destination. This will be evaluated in the current scope – generally the DataSet with which the Tablix is bound. The only difference from the previous lookup functions is that, here this is a VariantArray.
destination_expression – The field in which the source expression will be looked in. This field will belong to the dataset provided in the same function as the last parameter. 
result_expression – The field we want to retrieve from the destination DataSet for the matching source_expression & destination_expression for each row. 
Note, If multiple matches are found, the value from the first matching row will be returned for all the values in the source expression. And we cannot use any aggregate functions in the result_expression.
Dataset – The Dataset in which we want to look the values into.
Returns – A VariantArray, or Nothing if there is no match.

In this example we are the Multiplelook up will compare the list of values of dataset1 city value with city_id value of Dataset2 and will fetch the corresponding city name in the required column

  Join(MultiLookup(Split((Fields!City.Value),","),Fields!City_ID.Value,Fields!city.Value,"DataSet2"),",")

we have used 3 functions -

      Split() – To convert the comma separated City value into a value array.

        Multilookup() – To find the Name of City value for the matching  City_ID.

        Join() – Prepare the comma separated string for the names returned by the Multilookup() as  array.