SSRS Tablix Report having Data from Multiple Datasets – LookupSet Function SSRS 2008 R2

In SSRS 2008 R2 versions there are some functions introduced to provide extra functionality which we frequently required in BI reporting.

LookupSet is one of the functions which is used to join two dataset on similar key value having one to- many relationships and can be used to show from two different dataset fields in one tablix report.
Syntax and Description can be seen in below screenshot.

Let’s create sample report and see how we can use the function and achieve the functionality.
Let’s prepare tables and data for our learning purpose as shown in below screenshot.

Now we have two different queries fetching results having one key value common as “empid”.
Lets create two dataset each having different query as “EmpDS” and “DeptDS” as shown in below snapshot.

Let’s create one tablix report by dragging tablix from toolbox into report body.  Set the dataset name one of them in above created dataset as shown in below snapshot.                                                                        

Now we need to add the field from other dataset to the current tablix using LookupSet, for that create one column and go to the expression. Now write the expression as
=LookUpSet(Fields!deptid.Value,Fields!deptid.Value,Fields!Name.Value, “EmpDS”) and run the report it will show #Error in the report as shown in the below screenshot.

Edit the above expression and replace with the below expression. In below expression just we used join expression to join the multiple values.
=Join(LookUpSet(Fields!deptid.Value,Fields!deptid.Value,Fields!Name.Value, “EmpDS”),” ” + vbcrlf)
So we are fetching the “EmpDS” dataset field “Name” into the tablix using key value common in both dataset is “Deptid”.

Save the report and run it will appear as shown in below screenshot. Salary column is generated as per the correct employee.