SSRS Lookup Function

SSRS Lookup Function

In this article, we will show you how to use the SSRS Lookup function and its advantages. Or, How to combine multiple datasets in the SSRS report. Or, How to work with multiple data sets in report creation using SSRS Lookup with an example.

We are going to use the below-shown DataSet to explain the SSRS lookup function. Please refer to Embedded Data Source, and Dataset articles to understand the steps involved in creating Embedded Data Source, and Dataset that we used for this SSRS report.

Report DaatSet

Data written by the custom SQL query that we used in the above Dataset is shown in the report preview.

SSRS Lookup Example

To demonstrate the Lookup in SSRS, we are going to use the previously designed report.

Please refer to Table Report, Format Table Report article to understand the steps involved in creating Table Report & format, and Add headers and Footer to Report for adding Page headers, and footers.

Table Report

First, let me show you the Report Preview.

Table Report Preview

The above report is the Client’s primary criteria, and all of a sudden client asked you to add the Department Name to the above report. As you know that the Employees table doesn’t have Department Name, let me add another dataset.

To add the second data set, right-click on the Datasets folder and select the Add Dataset.. option from the context menu.

Add Second DataSet

The following screenshot will show you the Embedded Dataset that we are using

DataSet Properties

You might be tempted to add the Department name easily. Let me show you what will happen if I drag and drop the Department Name to the existing table?

Multiple DataSets Errors

From the above screenshot, you can see that it is throwing an error because the report designer will only allow you to use only one dataset.

Now let me show you the ideal approach to do so. First, insert a column to the right side of a Sales column by right-clicking on the Sales column -> Insert Column -> Right

Lookup Function 7

before we go further, let me explain the SSRS Lookup function

Lookup in SSRS Example

The SSRS lookup function returns the matching records from the specified dataset. And the syntax is:

Lookup(Source_IDColumn, 
          Target_IDColumn, 
            Target_Column, 
                       Target_Dataset)
  • Source_IDColumn: Similar to a Primary key. It is the Id column of the first data set.
  • Target_IDColumn: Similar to a Foreign key column. It’s the Id column of the Second data set. SSRS Lookup function will check whether Source_IDColumn is equal to Target_IDColumn and if they match then corresponding Target_Column value will return
  • Target_Column: Column that you want to add to the existing table.
  • Target_Dataset: Second Dataset name.

Next, Please select the newly created column, and right-click on it will open the context menu. Please select the Expression option from it.

Lookup Function Expression

Once you select the Expression option, a new window called the Expression will open. Use this expression window to write the SSRS lookup function.

=Lookup(Fields!DeptID.Value, 
          Fields!DeptID.Value, 
            Fields!DepartmentName.Value, 
                       "SQLTESTDepartmentDS")
Lookup Function Code

Once you completed with SSRS lookup function, Click OK to close the window

Lookup Function 10

As you can see that our report preview is displaying the Department Name. It means, the SSRS Lookup report is displaying data from multiple datasets

Lookup Function 11

Comments are closed.