SSRS: Join data from different SSRS data sources into data set

2024-01-17

Introduction and Problem

Quite often we consider the task to join 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 joined or SSRS lookup functions to achieve desired result outside of the report data retrieval stage.

In previous two articles we considered merge (union) task with the same conditions:

SSRS: Merge data from different data sources into one data set inside SSRS report
SSRS: Merge data from different data sources into one data set inside SSRS report using XML Connection

In this article we leverage the same technique to join data from two SSRS data sources.

The idea of all the solutions can be described as in the following image:

1: some data source providers has an ability to build data row sets from text and has different options to process this data row sets together - like union, join...
2: to use 1 we transform the source data to text representation. In all the solutions we used auxiliary report parameters to perform the transformation.

So, for the first article we used SQL Server as data processing engine to merge the data, in the second article we used XML Connection to do the same.

In current article we use SQL Server again as far as it seems that XML Connection does not has the ability to join the data.

Alternative solutions (workarounds)

External Data Sources

SQL Server Linked Server
T-SQL OPENQURY statement
SQL Server Integration Services (SSIS)

After data retrieval stage approaches

SSRS lookup functions

Solution

As it is described in the introduction the solution uses the same pattern as in SSRS: Merge data from different data sources into one data set inside SSRS report
with the difference that we use SQL Join construction instead of Union.

Steps to Reproduce

Link to sample report RDL file: JoinDatasets.rdl
Link to SSDT-BI SSRS Project: ssrs-join-datasets

Share via