SSRS - Example for Lookup , LookUpSet and MultiLookup functions

In this article, let us see examples for Lookup, LookUpSet and MultiLookup functions  .

As mentioned in 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.

Examples:

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

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 
• LookUpSet 
• MultiLookup 

See Also :

• My Posts on SSRS