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:
Note: I have attached LookupReport.rdl with respect to this post Download
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
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.
(Variant) An expression that is evaluated for each row in a dataset and that specifies the name or key to match on.
(Variant) An expression that is evaluated for the row in the dataset where source_expression = destination_expression, and that specifies the value to retrieve
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
Finally your report output is as in pic. below
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
The MultiLookup function compares a list of values in the current scope to the values in the destination dataset and returns a list of values from the destination dataset based on matches found. MultiLookup is equivalent to calling the Lookup function for a set of key values and is used when there is a 1:1 relationship.
Note: I have attached MultiLookupReport.rdl with respect to this post Download
Multilookup(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. The only difference from the previous lookup functions is that, here this is a VariantArray.
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.
Note, If multiple matches are found, the value from the first matching row will be returned for all the values in the source expression. And we cannot use any aggregate functions in the result_expression.
Dataset – The Dataset in which we want to look the values into.
Returns – A VariantArray, or Nothing if there is no match.
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
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.