Tutorial Gateway

  • C
  • C#
  • Java
  • Python
  • SQL
  • MySQL
  • Js
  • BI Tools
    • Informatica
    • Talend
    • Tableau
    • Power BI
    • SSIS
    • SSRS
    • SSAS
    • MDX
    • R Tutorial
    • Alteryx
    • QlikView
  • More
    • C Programs
    • C++ Programs
    • Go Programs
    • Python Programs
    • Java Programs

SSRS Lookup Function

by suresh

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.

SSRS Lookup Function 1

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

SSRS Lookup Function 01

SSRS Lookup Example

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

Please refer to 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 for adding Page headers, and footers.

SSRS Lookup Function 2

First, let me show you the Report Preview.

SSRS Lookup Function 3

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 dataset, right-click on the Datasets folder and select the Add Dataset.. option from the context menu.

SSRS Lookup Function 4

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

SSRS Lookup Function 5

Data present in the Department Table is:

SSRS Lookup Function 02

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?

SSRS Lookup Function 6

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

SSRS 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 dataset.
  • Target_IDColumn: Similar to a Foreign key column. It’s the Id column of the Second dataset. 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.

SSRS Lookup Function 8

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")
SSRS Lookup Function 9

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

SSRS 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

SSRS Lookup Function 11

Placed Under: SSRS

  • Learn SSRS in 28 Days
  • Create New SSRS Project
  • Create a New SSRS Report
  • SSRS Query Designer
  • SSRS Shared Data Source
  • SSRS Shared Dataset
  • SSRS Embedded Dataset
  • SSRS Embedded Data Source
  • SSRS Lookup – combine datasets
  • SSRS Report Items
  • SSRS Table Report
  • SSRS Table using report wizard
  • Format SSRS Table Report
  • SSRS Matrix Report
  • SSRS Matrix with report wizard
  • Display Image in SSRS Report
  • Add Rectangle to SSRS Report
  • Add TextBox to SSRS Report
  • Create a SSRS List Report
  • Grouping in SSRS Matrix Reports
  • Grouping in SSRS Table Reports
  • Create SSRS Report using Views
  • SSRS report with store procedure
  • Add Total and Subtotal to Report
  • Add Headers & Footers to Report
  • SSRS Global References
  • Export Report to PDF, XML, Excel, Word
  • Format SSRS Table Report
  • Format Textbox Fonts & Bg Color
  • Format Numbers in SSRS Report
  • Format SSRS Report Date, Time
  • Add Row Numbers to Report
  • Headers Visible While Scrolling
  • Repeat Headers on Each Page
  • SSRS Sorting
  • SSRS Interactive Sort
  • Filters at Dataset Level in SSRS
  • Filters at Tablix Level in SSRS
  • Page Breaks in SSRS Report
  • Custom Code in SSRS Report
  • SSRS Calculated Fields
  • SSRS Dashboard Reports
  • SSRS Document Map
  • SSRS Drill Through Reports
  • SSRS Drill Down Reports
  • SSRS Drill Down Matrix Report
  • SSRS Go To URL Action
  • SSRS Subreports
  • SSRS Report Parameters
  • SSRS Cascading Parameters
  • SSRS Multiple Parameters
  • SSRS Multi Value Parameter
  • SSRS Drop Down List Parameters
  • SSRS Charts
  • SSRS Area Chart
  • SSRS Bubble Chart
  • SSRS Column Chart
  • Format SSRS Column Chart
  • SSRS Data Bars
  • Data Bars in SSRS Matrix Reports
  • Format SSRS Data Bars
  • SSRS Funnel Chart
  • SSRS Linear Gauges
  • SSRS Radial Gauges
  • Format SSRS Gauges
  • SSRS Indicators
  • Format SSRS Indicators
  • SSRS Line Chart
  • SSRS Pie Chart
  • Format SSRS Pie Chart
  • SSRS Pyramid Chart
  • SSRS Range Chart
  • SSRS Radar Chart
  • SSRS Scatter Plot
  • SSRS Sparkline
  • SSRS Stacked Bar Chart
  • SSRS Configuration Manager
  • Change Report Manager Title
  • SSRS Deploying Reports in BIDS
  • Deploy using Report Manager
  • SSRS Report Manager Shared DataSource
  • SSRS Cached Reports
  • SSRS Linked Reports
  • SSRS Report Parts
  • SSRS Report Snapshot
  • SSRS Report History Snapshot
  • SSRS Report Subscription
  • SSRS Security
  • Upload File to Report Manager
  • Install SSRS Report builder
  • Report Builder – Create a Report
  • Report Builder – Create a Dataset
  • Report Builder- Create Bar chart
  • Report Builder – Create Pie Chart
  • Report Builder – Create report without wizard

Copyright © 2021 · All Rights Reserved by Suresh

About Us | Contact Us | Privacy Policy