This article 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 will use the simple Employee table DataSet with 15 records to explain the SSRS lookup function. Please refer to the Embedded Data Source and Dataset articles to understand the steps involved in creating the Embedded Data Source and Dataset we used for this SSRS report.
The report preview shows the data written by the custom SQL query we used in the above Dataset. For LookupSet, please refer to the article.
SSRS Lookup Example
To demonstrate the SSRS Lookup, we will use the previously designed report. First, let me show you the Report Preview.
Please refer to the Table Report, Format Table Report article to understand the steps involved in creating Table Report & format, and add headers and Footer to the Report for adding Page headers and footers.
The above report is the Client’s primary criteria, and suddenly, the client asked you to add the Department Name to the above report. As you know the Employees table doesn’t have a 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.
It will open the DatasetProperties window to create the Embedded Dataset that we will use for the SSRS lookup function. Let me name this Dataset SQLTESTDepartmentDS and write the below query to extract the department name.
SELECT DeptID, DepartmentName
FROM Department
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 into the existing table.
The above screenshot shows 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 that is SSRS Lookup function 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. Before we go further, let me explain the Lookup function.
Lookup Function in SSRS Example
The SSRS lookup function returns the matching records from the specified dataset. The syntax is:
Lookup(Source_IDColumn, Target_IDColumn, Target_Column, Target_Dataset)
- Source_IDColumn: It is similar to a Primary key. It is the Id column of the first data set.
- Target_IDColumn: It is 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 the corresponding Target_Column value will return
- Target_Column: Column that you want to add to the existing table.
- Target_Dataset: Second Dataset name.
Next, select the newly created column, and right-click on it to open the context menu. Please select the Expression option from it.
Once you select the Expression option, a new window called Expression will open. Use this expression window to write the SSRS lookup function.
=Lookup(Fields!DeptID.Value, Fields!DeptID.Value, Fields!DepartmentName.Value, "SQLTESTDepartmentDS")
Once you have completed with lookup function, Click OK to close the window.
As you can see our report preview displays the Department Name. It means the SSRS Lookup report is displaying data from multiple datasets.
Comments are closed.