We can use the Lookup Function in SSRS Report to join the data from two datasets. There should be at-least one matching column on which we will join the datasets.
Let's say we have DataSet1 with column FName,LName and DataSet2 with columns FirstName and Region.
If we would like to display FName,LName (from DataSet1) along with Region Column from DataSet2. We can use Lookup Function and write our expressions as shown below.
Lookup Function with Parameters
LookupSet(source_expression, destination_expression, result_expression, dataset)
Let's say we have DataSet1 with column FName,LName and DataSet2 with columns FirstName and Region.
If we would like to display FName,LName (from DataSet1) along with Region Column from DataSet2. We can use Lookup Function and write our expressions as shown below.
Lookup Function with Parameters
LookupSet(source_expression, destination_expression, result_expression, dataset)
How to use Lookup Function in SSRS Report to Join Data from two datasets
Now let's write our expressions by using Lookup Functions and use FName as source_expression, FirstName as destination_expression,Region as result_expressions and "DataSet2" as Dataset.
=Lookup(Fields!FName.Value,Fields!FirstName.Value,Fields!Region.Value,"DataSet2")
Lookup Function in SSRS Report - Display Multiple DataSets fields in Single Tablix
Our final Report will look like ( FName and LName coming from Dataset1), Region column values are coming from Dataset2 where DataSet1.FName matches with DataSet2.FirstName. We will see blank Region if there is no match.
Watch Step by Step Answer of SSRS Interview Question
No comments:
Post a Comment