Sometime during the report generation process we come in a situation where the report has multiple dataset and we need to combine the some data fields from two or more dataset in single data region
Lookup functions allow you to combine data from two datasets in a single data region in report. There are three lookup functions available:
The lookup function compares a value in the current scope to a value in a destination dataset and returns a single value from the destination dataset if a match is found. The Lookup function is used when there is a 1:1 relationship
Syntax
LookupSet(source_expression, Destination_expression, Result_expression, Dataset) Source_expression
(Variant) An expression that is evaluated in the current scope and that specifies the name or key to look up.
Destination_expression
(Variant) An expression that is evaluated for each row in a dataset and that specifies the name or key to match on.
Result_expression
(Variant) An expression that is evaluated for the row in the dataset where source_expression = destination_expression, and that specifies the value to retrieve
Dataset
A constant that specifies the name of a dataset in the report.
We have two dataset dataset1 and dataset2.
In this example lookup function will look dataset1’s stateid in to datset2 stateid and will fetch dataset2 State Name into Resultant column
=Lookup(Fields!StateDataset1_ID.Value, Fields!StateDataset2_ID.Value,Fields!State_Name.Value,"DataSet2")
Note
1. If multiple matches are found, the value from the first matching row will be returned.
2. We cannot use any aggregate functions in the result_expression.
LookUpSet
In the Lookup () we have seen It is used to fetch the first matching value from the other DataSet. Now, if we want all the matching values from the other DataSet. There is another function LookupSet.
LookUpSet()
The LookupSet function compares a value in the current scope to a value in a destination dataset and returns a list of values from the destination dataset based on matches found.
Note: I have attached LookupsetReport.rdl with respect to this post Download
Syntax
LookupSet(source_expression, destination_expression, result_expression, dataset)
source_expression – The field which will act as the key/lookup value for the destination. This will be evaluated in the current scope – generally the DataSet with which the Tablix is bound.
destination_expression – The field in which the source expression will be looked in. This field will belong to the dataset provided in the same function as the last parameter.
result_expression – The field we want to retrieve from the destination DataSet for the matching source_expression & destination_expression for each row.
Dataset – The Dataset in which we want to look the values into.
Returns – A VariantArray, or Nothing if there is no match.
Note
1. if multiple matches are found, all the values from the matching rows will be returned.
2. we cannot use any aggregate functions in the result_expression
In this example lookupSet() function will look dataset1’s stateid into datset2 stateid and will fetch dataset2 city name in one column in comma separated value
2. We cannot use any aggregate functions in the result_expression.
LookUpSet
In the Lookup () we have seen It is used to fetch the first matching value from the other DataSet. Now, if we want all the matching values from the other DataSet. There is another function LookupSet.
LookUpSet()
The LookupSet function compares a value in the current scope to a value in a destination dataset and returns a list of values from the destination dataset based on matches found.
LookupSet(source_expression, destination_expression, result_expression, dataset)
1. if multiple matches are found, all the values from the matching rows will be returned.
2. we cannot use any aggregate functions in the result_expression
In this example lookupSet() function will look dataset1’s stateid into datset2 stateid and will fetch dataset2 city name in one column in comma separated value

=Join(LookupSet(Fields!State_ID.Value, Fields!State_ID.Value,Fields!city.Value,"DataSet2"),",")
Expected Result

Here we have used two function
1 LookupSet() – To get an Variant array of the matching values
2 JOIN () – To join all the elements of the array as a comma separated string
Note: I have attached MultiLookupReport.rdl with respect to this post Download
Syntax
Multilookup(source_expression, destination_expression, result_expression, dataset)
In this example we are the Multiplelook up will compare the list of values of dataset1 city value with city_id value of Dataset2 and will fetch the corresponding city name in the required column
Join(MultiLookup(Split((Fields!City.Value),","),Fields!City_ID.Value,Fields!city.Value,"DataSet2"),",")

we have used 3 functions -
Split() – To convert the comma separated City value into a value array.
Multilookup() – To find the Name of City value for the matching City_ID.
Join() – Prepare the comma separated string for the names returned by the Multilookup() as array.
No comments:
Post a Comment