As far as I can see this cannot be done, but always happy to be proven wrong on these occasions. I have an SSRS report with 2 tables, each table has a dataset as it's source with the 2 datasets being different (2 different SQL databases). What I am trying to do is merge the 2 together.
Select from SQL database a
+------+------+-------+
| Col1 | col2 | col 3 |
+------+------+-------+
| 1 | Fred | Smith |
| 2 | Tom | Jones |
+------+------+-------+
Select from SQL database b
+------+------+-------+
| Col1 | col2 | col 3 |
+------+------+-------+
| 3 | Bob | Brown |
| 4 | Mike | Hunt |
+------+------+-------+
This would give something like the following;
+------+-------+-------+
| Col1 | col2 | col 3 |
+------+-------+-------+
| 1 | Fred | Smith |
| 2 | Tom | Jones |
| 3 | Bob | Brown |
| 4 | Mike | Hunt |
+------+-------+-------+To further complicate matters the fieldnames are not consistent, these are 2 different systems, e.g. Firstname in 1 and Forename in the other. Secondly not all the fields are in both databases, that is to say there are expressions in some fields using the Lookup function to retrieve these values from another (3rd) dataset. These missing items would still be required to be added via a Lookup, before anyone has a go at me, I didn't design this report but have inherited it.
Given the differences in the 2 datasets and that somehow I'd then need to do a Lookup based on an IIF statement (depending on the source data), I think this would be next to impossible in SSRS.
I have been told that linked servers are a non starter so bang goes that idea, so am struggling to see how this can be done.
I fully expect nobody to be able to help me here with this but one lives in hope. As always any questions regarding this and clarification on any points I have made will be answered as promptly as possible.
The environment is SQL Server 2008 R2 Reporting Services and SQL 2012 database.