Ssrs Join Two Datasets

 admin
  1. Introduction and Problem Quite often we consider the task to merge data from different data sources inside SSRS report. Usually we are advised to use workarounds like SQL Server linked servers or SSIS which uses non ssrs datasources to extract data to be merged or SSRS lookup functions to achieve desired result outside of the report data retrieval stage.
  2. SSRS Lookup functions allow you to combine data from two datasets in a single data region. The Lookup function is used for an 1-1 relation between two dataset. The difference between previous functions is that Fields!datasetAIDs.Value is an array of values.
  3. How to create SSRS Report from Scratch; How to create Multiple DataSets in SSRS Report; How to Join two Datasets in SSRS Report and Display fields on Single Tablix from two Datasets; Understand Syntax of Lookup Function in SSRS Report =Lookup(Fields!id.Value,Fields!SalePersonId.Value,Fields!City.Value,'DSETSales2016').

In this post i will explain you how to use multiple datasets in a single table in SSRS.
The same method can be applied on matrix,charts etc.

Although in SSRS you can use fields of only one dataset but you can use the aggregated value of the another dataset if the field in the another dataset is numeric.

Under Expression, click Dataset then DataSet2 and double click Sum (MaxJob) as shown below. Right click below the TotalCompletedJobFromMAX column and go to expression as shown below. Under the Expression write the expression as shown below.In this expression i have used the fields from DataSet1 as well as DataSet2.

This i will show you with the help of a demonstration

Let us consider a table as shown below which has details of contractors and the jobcompleted details

Now i have created a dataset named DataSet1 which has aggregated data representing total number of jobs by each contractor as shown below.

I have also created Dataset2 representing the Maximum number of jobs among all the contractors as shown below.

Now right click on the Table and go to the Tablix Properties
as shown below

Under Tablix Properties , General Tab you will find Tablix is bound with Dataset1. It means only the fields of DataSet1 can be used within this Tablix report as shown below.

Right click below the MaxJobComplete column and go to expression as shown below.

Under Expression , click Dataset then DataSet2 and double click Sum(MaxJob) as shown below.

Right click below the TotalCompletedJobFromMAX column and go to expression as shown below.

Under the Expression write the expression as shown below.In this expression i have used the fields from DataSet1 as well as DataSet2.

Now select the last column and go to the properties window and change the Format option to P2 as shown below. (P2 represents Percentage with 2 decimal places)

Now go to Preview tab and see the result as shown in the image below

JoinWe 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)

Ssrs Join Two Datasets From Different Databases


How to use Lookup Function in SSRS Report to Join Data from two datasets

Ssrs Combine 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

Ssrs Union 2 Datasets

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.

Ssrs Combine Two Datasets In One Chart

Watch Step by Step Answer of SSRS Interview Question