Multiple Datasets for a report

I was wondering if there is a way we can join two different datasets and use that as a result for a report. These two datasets would be a part of the same report, but say they are pulling data from two different databases and I want to perform a join on them. Is that possible?
maybe write a stored procedure inside one the the databasees that does the join using a openquery if it is a linked server, and that way when you pull in your dataset, you will only have to set the conection string up for the one database.|||If I am reading this right, you want to use data from two different datasources on one report.

One of your options is to have two datasets for the report, and you can drag and drop fields from eqach dataset onto the same report. You may want to have them both use the same parameter.

I hope this helps.|||You read it right, that is what I want. I have already tried the approach you have mentioned, the only problem being, I don't know how I would join the two datasets once I have them on the report. There is a field called, order id which I need to join the datasets on.|||

what is wrong with the approach joining the data from the database and bringing it in as a single dataset?

|||Amnogeek,

When you have multiple datasets on one report, you haev the option of selecting fields from each dataset, and dropping them on the report designer. When you are using multiple datasets, wether you are using stored procedures or SQL statements, you can have each dataset pulling information using the same criteria.

Salameh is also correct on just joining the two databases if there is a relation between them.

Again, I hope this helps somehow...|||There is nothing wrong with it, in fact it was my first thought also. The thing is there are way too many records to pull and it takes the join much longer on the server. That was why I was wondering if I could perform a join on the client after I get the data. That might speed the things up a little bit. But anyways, thanks for all your responses.|||

If there are too many records to join then there might be something wrong with your selection criteria - maybe not - but worth looking at again. As an alternative, why not use a stored procedure to extract the records that you need to temporary tables and then join them. This will give you fewer records to join. When you use temporary tables, its worth noting that often an update of a column is quicker than joining tables - this means that if you need to join tables just to populate a description column (for example) that its quicker to create the temporary table, populating the other columns and then populate the empty description column by means of an update. You need to play around with this approach in Query Analyser to see whats most efficient for the number of records that you have to process.

Hope this helps...