Search

Advertisements

SSRS - Example for Lookup , LookUpSet and MultiLookup functions

This article is about an example for Lookup , LookUpSet and MultiLookup functions  .

As mentioned on MSDN

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

LookUpSet   -
Returns the set of matching values for the specified name from a dataset that contains name/value pairs.

MultiLookup -
Returns the set of first-match values for the specified set of names from a dataset that contains name/value pairs.

Example :

Create Data source and then Datasets

Dataset name : LookUp_Parameter

Dataset Query :

DECLARE  @Country_Master TABLE(Id INT,Country_Code VARCHAR(10),Country_Name VARCHAR(10))
INSERT INTO @Country_Master SELECT 1,'IND','INDIA'
INSERT INTO @Country_Master SELECT 2,'US','USA'
INSERT INTO @Country_Master SELECT 3,'CN','CHINA'
SELECT * FROM @Country_Master



 



Dataset name : LookUp

Dataset Query :

DECLARE  @Employee_Master TABLE(Employee_Name VARCHAR(10),Country_Code VARCHAR(10))
INSERT INTO @Employee_Master SELECT 'Sathya','US'
INSERT INTO @Employee_Master SELECT 'Deepak','IND'
INSERT INTO @Employee_Master SELECT 'Madhu','US'
INSERT INTO @Employee_Master SELECT 'Kate','CN'
SELECT * FROM @Employee_Master






 Parameter Name : CountryCode

 Parameter Settings : 




 Parameter Name : CountryCodes

 Parameter Settings :




Expressions for Lookup , LookUpSet & MultiLookup textboxes :

Lookup  

="EmployeeName belonging to input country code  :  " & Lookup(Parameters!CountryCode.Value,Fields!Country_Code.Value,Fields!Employee_Name.Value, "LookUp")

Another example for Lookup function - http://www.allaboutmssql.com/2012/09/ssrs-lookup-expression.html
 
LookUpSet 

="EmployeeNames belonging to input country codes  :  " &Join(LookUpSet(Parameters!CountryCodes.Value(0),Fields!Country_Code.Value,Fields!Employee_Name.Value, "LookUp"),"," + vbcrlf)


MultiLookup 


=Join(MultiLookup(Split(Parameters!CountryCodes.Value(0),","),Fields!Country_Code.Value,Fields!Employee_Name.Value,"LookUp"),", ")







References :
Lookup -  http://technet.microsoft.com/en-us/library/ee210531.aspx
LookUpSet - http://technet.microsoft.com/en-us/library/ee240819.aspx
MultiLookup -  http://technet.microsoft.com/en-us/library/ee210450.aspx

See Also :








1 comment:

Mukesh Singh said...

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