Bind data from 2 datasets into one tablix in SSRS

Question

I have to combine data from 2 different datasets to single Tablix which should be sorted on Date Time.

For Example:
Dataset1:

DateTime                      Product       Employee  
2020-08-13 18:10:53.263       ABC             A

Dataset2:

DateTime                      Product       Employee  
2020-08-13 19:10:20.000       XYZ             A  

ResultSet:

DateTime                      Product       Employee  
2020-08-13 18:10:53.263       ABC             A  
2020-08-13 19:10:20.000       XYZ             A 

Note: I cannot combine data from both the datasets at the database level as the datasets refer to 2 different datasources.

I have an idea on LOOKUP but I guess that can be used at column level but my case I need to display the data at row level from 2 datasets based on Datetime sort.

Could someone please suggest if there is a way to achieve this.

Thank you in advance!!

Answer 1

If the databases are on the same server then you can simply UNION the results together something like

SELECT [DateTime], [Product], [Employee]
    FROM [database_A].[mySchema].[myFirstTable]
UNION ALL
SELECT [DateTime], [Product], [Employee]
    FROM [database_B].[myotherSchema].[myOtherFirstTable]

If they are on different servers then Create a linked server (details on how to do this are here)

Then use the same query but preceding the 2nd reference with the server name / link server name

SELECT [DateTime], [Product], [Employee]
    FROM [database_A].[mySchema].[myFirstTable]
UNION ALL
SELECT [DateTime], [Product], [Employee]
    FROM [myLinkedServerName].[database_B].[myotherSchema].[myOtherFirstTable]

Now you'll have a single dataset with all your data and no headache!

---

If you have two existing Stored Procs

---

If you have two SP then you can still do this but in a slightly different way. Something like

CREATE TABLE #t([DateTime] DateTime, Product varchar(50), Employee int)

INSERT INTO #t
    EXEC #myFirstProc

INSERT INTO #t
    EXEC #mySecondProc

SELECT * FROM #t