Search

Advertisements

SQL Server Reporting Services - Lookup Expression



If you are new  for creating SSRS report & not familiar with creating datasources and datasets,


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:



In SSMS,

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


In this example,

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 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 below expression 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(Lookup(Parameters!Input_Country_Code.Value,Fields!Country_Code.Value,Fields!Employee_Name.Value, "Employee_Master"))



  •                                                  --Cstr for converting to string

  • Here the 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.




1 comment:

Mukesh Singh said...

SSRS – LookUp, MultiLookUp and LookupSet Functions
These beautiful features bring a lot of satisfaction to report writer and developers to marge values from two datasets. These functions are is playing a very handy role to combine data from two datasets in a single data region which was not possible in earlier versions of Reporting Services.
SSRS – LookUp, MultiLookUp and LookupSet Functions

Advertisements