SSRS Lookup Function

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

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

Data written by the custom SQL query that we used in the above Data set is:

SSRS Lookup Example

In order to demonstrate the Lookup in SSRS, we are going to use the previously designed report as we shown below. Please SSRS Table Report, Format Table Report in SSRS article to understand the steps involved in creating Table Report & format, and Add headers and Footer to SSRS Report article for adding Page headers, and footers.

First, let me show you the Report Preview.

Above report is the basic criteria for the client, and all of the sudden client asked you to add the Department Name to the above report. As you know that the Employees table doesn’t had Department Name, let me add another dataset. In order to add second dataset, right-click on the Datasets folder and select the Add Dataset.. option from the context menu.

Following screenshot will show you the Embedded Data set that we are using

Data present in the Department Table is:

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?

From the above screenshot you can see that, it is throwing an error because SSRS 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-click on the Sales column -> Insert Column -> Right

before we go further, let me explain the Lookup function

Lookup in SSRS

This function will return the matching records from the specified dataset. And the syntax is:

Lookup(Source_IDColumn, 
          Target_IDColumn, 
            Target_Column, 
                       Target_Dataset)

• Source_IDColumn: Similar to Primary key. This will be the Id column of the first dataset.
• Target_IDColumn: Similar to Foreign key column. This will be the Id column of the Second dataset. Lookup function will check whether Source_IDColumn is equal to Target_IDColumn and if they match then corresponding Target_Column value will be retunred
• Target_Column: Column that you want to add to 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 the context menu.

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

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

Once you are done, Click OK to close the window

As you can see that our report preview is displaying the Department Name. It means data from multiple data sets

Thank You for Visiting Our Blog