Can I "join" across datasources in SSRS?

开发者_高级运维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.

---

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

---

You could also try using the Lookup and Lookupset functions within your tablix.

Lookup is a 1 to 1 join while Lookupset is 1 to many and may need you to have your data concatenated if you want a set of strings out.

For Lookup the following is from the MSDN site with some tweaks for my simple mind

Lookup(Field you are joining from, Field you are joining to, Field you want back, Dataset of the field you want back)

The tablix should be linked to the dataset of your source (joining from).

And just realised this is from 2010, not 2014...so a necro-post!

---

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

---

You could create a linked server that would contain data from both instances. From the SSRS point of view you would have one single datasource.

---

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.