Lookup functions in SSRS 2008 R2 - I

 

Introduction

Most of us who are regularly working with SSRS have always felt the need of some way through which multiple DataSets could be joined – something similar to JOINs of SQL. But there was no straight forward way to get it done in SSRS until the release of SSRS 2008 R2. SSRS 2008 R2 came up with Lookup Functions which plays a very handy role in getting the things done in a very simple way. However, these functions can not be taken as a alternate to JOINs. As their name suggests, they are just lookups helping us to retrieve values from a dataset that has name/value pairs and that is not bound to the data region.

Different Flavors

We are provided with 3 different lookup functions -

1. Lookup(source_expression, destination_expression, result_expression, dataset)
2. LookupSet(source_expression, destination_expression, result_expression, dataset)
3. Multilookup(source_expression, destination_expression, result_expression, dataset)

Let’s take up them one by one

1. Lookup(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. 

Note, If multiple matches are found, the value from the first matching row will be returned. And we can not use any aggregate functions in the result_expression.

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

E.g. Suppose, we are having 2 Datasets – EmployeeDetails & Departments in SSRS. The tablix is bound to EmployeeDetails and in one of the columns we need to display the DepartmentName to which the Employee in each row belongs to. The below expression will help us to fetch the Name of the department from the Departments Dataset.

=Lookup(Fields!DepartmentID.Value, Fields!ID.Value, Fields!Name.Value, "Departments")

Here, the DepartmentID field from the Employees Dataset will be matched with the ID field of the Departments Dataset and the Name for the matching row will be returned.

So, we have seen how we can make use of Lookup() and get the matching value. Will continue with the other two functions in my next blog.