SQL Server Reporting Services - Lookup Expression

In this article, let us see an example on how to write lookup expression in SSRS.

If you are new  to creating SSRS report & not familiar with creating datasources and datasets, refer my previous post on SSRS.

Lookup expression:

Returns the first matching value for the specified name from a dataset that contains name/value pairs.

Syntax:

Lookup(source_expression, destination_expression, result_expression, dataset)

Explanation on syntax:

IF (source_expression = destination_expression),

then return result_expression from the specified dataset.

Example for Lookup expression:

From SSMS, create below sample dataset:

CREATE DATABASE SSRS_Lookup_TEST
GO

CREATE TABLE Country_Master(Country_Code VARCHAR(10),Country_Name VARCHAR(10))
GO

INSERT INTO Country_Master SELECT 'IND','INDIA'
INSERT INTO Country_Master SELECT 'US','USA'
INSERT INTO Country_Master SELECT 'CN','CHINA'
GO


CREATE TABLE Employee_Master(Employee_Name VARCHAR(10),Country_Code VARCHAR(10))
GO

INSERT INTO Employee_Master SELECT 'Sathya','IND'
INSERT INTO Employee_Master SELECT 'Deepak','US'
INSERT INTO Employee_Master SELECT 'Sunny','CN'
GO

Step 1: 

Create datasource, above created database “SSRS_Lookup_TEST” will be the datasource

Step 2: 

Create dataset named “Country_Master” with the below query

SELECT Country_Code,Country_Name FROM Country_Master

Step 3: 

Create dataset named “Employee_Master” with the below query

SELECT Employee_Name,Country_Code FROM Employee_Master

Step 4:

Create input parameter for report, which is the Country_Code from dataset  “Country_Master” as shown in below image:

Step 5:

Under Design tab, Drag & drop two Textbox's from Toolbox 

Textbox 1 -  for title “Employee Country Information”,

Textbox 2 - for  displaying employee name and country name for the input country code.

Right-click on Textbox 2, goto Expression..and enter the expression mentioned below in yellow letters.

Use parameters and datasets under category for writting expression as shown in below image.

="Employee Name :"

&

Cstr(Lookup(Parameters!Input_Country_Code.Value,Fields!Country_Code.Value,Fields!Employee_Name.Value, "Employee_Master"))

&

"  Country Name :"

&

Cstr(Lookup(Parameters!Input_Country_Code.Value,Fields!Country_Code.Value,Fields!Country_Name.Value, "Country_Master"))

Explanation for expression:

• ="Employee Name :"  --just a string

• &  -- for concatenation

• Cstr for converting to string

-> First lookup is for

Cstr(Lookup(Parameters!Input_Country_Code.Value,Fields!Country_Code.Value,Fields!Employee_Name.Value, "Employee_Master"))

IF(Input Country Code = Country_Code of dataset “Employee_Master” )

then display Employee_Name from dataset “Employee_Master”

-> Second lookup is for

Cstr(Lookup(Parameters!Input_Country_Code.Value,Fields!Country_Code.Value,Fields!Country_Name.Value, "Country_Master"))

IF(Input Country Code = Country_Code of dataset “Country_Master” )

then display Country_Name from dataset “Country_Master”

Step 6:

Under preview tab, select country code from drop downlist and click on view report as shown in below image:

 Here is the final report:

See Also:

• My posts on SSRS