SQL Server Performance Forum – Threads Archive

Using 2 datasets in one report table ?

I am trying to combine 2 tables from different databases into 1 report. (Can’t do a join).
SO right now I have the data in 2 separate datasets.
There isn’t a key that relates the two datasets, only ‘Date’ fields. IE: I am joining the data that occurs within each month between the 2 datasets. I have not had any luck grouping this data together, as I am always getting the error "Report item expression can only occur within the current data set scope…" BOL doesn’t seem to cover this area, so I’m looking for any help/documentation on this, or if this is even possible. Thanks.

---

I don’t believe this is possible, is there any reason you can’t join on the source system ? HTH Jasper Smith

---

No, the tables are coming from 2 completely different Oracle DB’s on different servers. This leads me to my next question. Let’s say I get the data the way I want in a regular ASP.NET web form by populating a DataSet with the two tables and and joining the data in a 3rd table. Can I access this dataset from Reporting Services somehow ? I think this is possible although I haven’t seen any examples/explanations of how to do it?? thanks

---

You would have to probably use a custom data processing extension. There are some posts further down about using objects as datasources for reports. BOL has a section on querying external datasets http://msdn.microsoft.com/library/en-us/RSPROG/htm/rsp_prog_extend_dataproc_5ug2.asp
HTH Jasper Smith

---

You could do a subreport with a link on the date field. I have not tried this, but it might work. Since you have SQL Server you could setup Linked Servers to your 2 oracle instances and then write an sp that combines the data in SQL Server or just a query in RS that would join the data. Jack Corbett, DBA
Nexfor Fraser Papers

---

In theory you can write SQL for the two tables in SQL server by linking dbs

---

]]>