Join two datasets in SSRS

This article demonstrate, how to join two datasets in SSRS.

Assume that you have two different data sets, lets say one data set fetches the employee personal details, and second dataset fetches the department details.

Now you want to display the complete details of employee in report with associated department details.

These two dataset, may be belongs to different SQL Server instances, or may be same SQL Server instances but different databases, it really does not matter.

Scenario could be anything but the purpose is that, how to join them and display complete information in single Tablix.

Lets see, how to Join two data set in SSRS step by step.

First we will prepare a sample dataset, you can see here we have two tables in SQL Server named EmployeeInfo, and Department, table scripts and sample data are given below.

Create table dbo.EmployeeInfo
(
EmpId Int, 
EmpName Varchar(50),
EmpCode Varchar(30),
Gender Varchar(8),
Dob Date,
DeptId Int
)

Insert into dbo.EmployeeInfo
Values
(101, 'Rohit Shukla', 'XC45GH','M', '02/02/1989', 501),
(102, 'Shubhan Tripathi', 'XC67HH' ,'M', '01/31/1991', 501),
(103, 'Amit Kuamr', 'XC23KH','M', '12/08/1991', 502),
(104, 'Rajni Singh', 'XC87BH','F', '03/21/1992', 503),
(105, 'Manish Kumar', 'XC48MH','M', '09/02/1994',503),
(106, 'Meena Kuamri', 'XC14GH','F', '12/20/1989', 502),
(107, 'Ajay Negi', 'XC89DH','M', '07/19/1986', 501),
(108, 'Rohit Shukla', 'XC68VH','M', '06/16/1982', 504),
(109, 'Reena Mittal', 'XC47AH','F', '05/26/1988', 504),
(110, 'Sujit Grover', 'XC46DH','M', '03/22/1989', 501),
(111, 'Mukesh Rawat', 'XC39NH', 'M', '07/12/1994', 503),
(112, 'Bansal Kumar', 'XC90EH','M', '09/15/1990', 502)

Create table dbo.Department
(
DeptId Int, 
DeptName Varchar(50),
DeptCode Varchar(30) 
)

Insert Into dbo.Department
values
(501, 'Computer Science', 'CS-X10'),
(502, 'Finance Services', 'FS-X11'),
(503, 'Logistics', 'LS-X12'),
(504, 'Human Resource', 'HR-X13')

Lets see the table output and you can see both tables having records.

Now we have two dataset Employee and Department are ready to use in SSRS report, Although we can use SQL Join to combine these two tables based on matching column value (based on department Id) in both table and get a single output. But we will do that in SSRS side, and which is our requirement.

Lets create two embedded data set in SSRS, first one will fetch the Employee details and second will fetch the Department details.

Lets take a new report and add a Embedded Data source first then will add Embedded Datasets.

If you do not know how to add Embedded Data Source and Embedded Datasets in SSRS then you can refer posts : add an Embedded Data Source and add an Embedded Dataset

You can see, here we have created an embedded dataset Ds_Employee for employees and Ds_Department for department.

Embedded data for Department named Ds_Department.

Now we have Data Source and two Dataset in our report as shown below.

 

Lets create a quick report which displays an employee personal details.

You can see, for employee’s personal details we have used only first data set fields only that is Ds_Employee.

Lets see the preview, and you can see the employee’s personal details.

So far so good, now we want to display few more details in this report that is department details.

To display department details in report, one thing that comes in mind just simply drag fields from department dataset into Tablix, but it will give you an error.

Lets take a look what happens when you try to take a field from department dataset and drop just right after DOB column in report Tablix.

You will see when you drop a field from department dataset into Tablix, it gives an error as shown below.

Only fields from the current dataset ‘Ds_Employee’ can be added.

That means, you can not add fields from department dataset, as the report is displaying employee personal details which is currently using Ds_Employee dataset, so you can not add fields from another dataset.

So to display department details you need to create an expression as shown in following setps.

First we add a new column, right click on DOB column then navigate to Insert Column and select Right.

Now once a new column is created, just right click on column then select Expression from context menu.

Now once you click on Expression, a Expression window opens.

Next, in Category section, expand Common functions then select Miscellaneous after that in Item section double click on Lookup function.

Now we will use Lookup function to retrieve the value from the Department dataset based on matching values for department id.

Lets complete the expression as shown below.

=Lookup(Fields!DeptId.Value, Fields!DeptId.Value, 
Fields!DeptName.Value,"Ds_Department")

Once you done with this, just click on OK button.

Once you done with this, just provide the name to new column as Department.

Now, lets the report preview, and you can see the department name is fetched from department dataset based on matching value for Deptid in Employee table.

Now we have employee personal details with department they belongs to.

Lets display the department code in report as well.

Similarly, first we add a new column then create an expression.

After that, just write a following expression. This time we will fetch a DeptCode from Department dataset based on matching values for DeptId in both Datasets.

=Lookup(Fields!DeptId.Value, Fields!DeptId.Value,
Fields!DeptCode.Value,"Ds_Department")

Once done, just click on OK button.

After that give a named to new column as DeptCode.

Now lets see the report preview, and you can see now we have complete details of employee that is Employee personal details as well as department details, which are combined from two different datasets.

Also read..

Create a New SSRS Project Solution

Create a Shared Data Source in SSRS

Create a Shared Data Set in SSRS

Create an Embedded Data Source in SSRS

Create an Embedded Data Set in SSRS

 28,344 total views,  4 views today