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")


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 :

See Also :

1 comment:

Mukesh Singh said...
This comment has been removed by a blog administrator.