Can I "join" across datasources in SSRS?

Question

I've got two datasources, one Oracle and one Sql Server. Due to circumstances that predate me (as in it was like this when I found it) some columns in the Oracle database contain PKs from lookup tables in the Sql Server database.

I am attempting to create a Sql Server Reporting Services report that will combine data from both the Oracle and Sql Server database; where the data to be reported is partially from Oracle but some of the values needs to be looked up in Sql Server.

I've got the datasources. I've got the DataSets. I just can't figure out how to show both datasets in the same tabular report.

Is this possible? If so how so? I'd rather not resort to a db link in one or the other databases as I'd like to handle this on the reporting side.

Answer 1

I don't think you can join directly, but you might be able to add a subreport that would query the second datasource by using the foreign key from the first datasource as a parameter. See: How to: Add a Subreport and Parameters (Reporting Services).

Answer 2

you could also embed a table inside another table and pass the primary key to the embeded table.

Answer 3

You can use heterogenous services or oracle transparent gateway to run the report off the oracle side. Oracle can query the data from the SQL side.