How to display data on single tablix from two datasets in ssrs report by joining on Multiple Columns - SSRS Interview Questions & Answers

Lookup Function can be used in SSRS to join two datasets. Let's say we have two datasets

Dataset1 has column FName and LName.
DataSet2 has FirstName,LastName and Region

We would like to Join FName--FirstName and LName--LastName and would like to display
FName,LName( from Dataset1) and Region from Dataset2, if values of columns match.

Here is syntax for Lookup Function.
LookupSet(source_expression, destination_expression, result_expression, dataset)

As we need to join on 2 columns from each Dataset. We need to concatenate the columns and then use in lookup.

Let's drag FName and LName from DataSet1 to Talix as shown and go to expressions for third column.


How to use Lookup Function with Multiple Columns in SSRS Report

Our expressions will be like that

=Lookup(Fields!FName.Value+Fields!LName.Value,
Fields!FirstName.Value+Fields!LastName.Value,Fields!Region.Value,"DataSet2")


If you don't like to concatenate columns in expressions. You could have create FullName1 calculate field in both datasets by using below expressions

DataSet1:
Right click on DataSet2--> Click Add Calculate Field and then write expression like shown below




DataSet2:
Right Click on DataSet2 and go to Add Calculate Field and then Add FullName2 same way we did above. The expressions will be like below

=Fields!FirstName.Value+Fields!LastName.Value

Now you don't have to use concatenate the columns inside expressions for Lookup Function.
You can use calculate fields in Lookup Function to get the Region from DataSet2.

=Lookup(Fields!FullName1.Value,Fields!FullName2.Value,Fields!Region.Value,"DataSet2")


Step by Step Example explained in below Video

No comments:

Post a Comment