I currenty have 2 databases which I would like to use as data sources to create 1 report. How can I get tables from both data bases in one report?

use CREATE SYNONYM in the first database referencing objects from the second database use CREATE VIEW in the first database referencing objects from the second database as [databaseName2].[dbo].[tableName] use CREATE PROCEDURE in the first database referencing objects from the second databaseSergei

you can either join the tables together to return 1 set of data or you can create 2 datasets itn eh report - each one feeding a seperate tableRgds Geoff

you can either join the tables together to return 1 set of data or you can create 2 datasets itn eh report - each one feeding a seperate table Rgds Geoff I have the same dilemma. Actually I have a DB in Access on my workstation and another in company SQL Server. So joining tables from them is a challenge because I don't have sysadmin rights to link servers. I use a query from the Access DB to provide multiple values to a parameter defined as internal. Then I use that parameter values in the IN clause of the query against the second DB. But it executes long due to the nature of the query. So if you can provide some type of workaround to "join" datasets or tables within SSRS would be a big help. TIA.

Hi Jaggerdude, In Access you could link tables to SQL Server database and join native Access tables to linked tables. In this case you don't need sysadmin rights. Also, you could use sql-pass query in Access and again join results together in Access and display from one data source (Access as a single data source). Also, you could SSIS to join data from Access and SQL Server to merge data together and use SSIS as data source.Sergei

In any version prior to SSRS 2008R2, you cannot join datasets and you cannot use more than 1 dataset in a table In SSRS2008R2, you can use a LOOKUP function to loookup data from a 2nd dataset into a table. You can only do this when there is a 1 to 1 relationship between the sets of data however - much like the vlookup / index-match functions in excel See here for details: http://blog.datainspirations.com/2010/03/19/sql-server-2008-r2-reporting-services-look-up-look-down-look-all-around-part-i/Rgds Geoff

Thanks Geoff for the reply. I'm not sure what version we have but nice info to have. Also the relationship between the datasets I'm using is 1 to many so I guess I can't use the LOOKUP function.Jag Gutz

Thanks Sergei. I have not used SSIS but it sounds to me that all of your proposed options would work for me. I have not used sql-pass query in Access but I think I can do option 1. Jag Gutz