locked
using 2 data sources RRS feed

  • Question

  • 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?
    Wednesday, November 17, 2010 9:16 PM

Answers

  • 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 database


    Sergei
    • Marked as answer by Tony Chain Thursday, November 25, 2010 9:21 AM
    Wednesday, November 17, 2010 9:56 PM

All replies

  • 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 database


    Sergei
    • Marked as answer by Tony Chain Thursday, November 25, 2010 9:21 AM
    Wednesday, November 17, 2010 9:56 PM
  • 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
    Wednesday, November 17, 2010 10:10 PM
  • 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. 

    Wednesday, November 17, 2010 10:49 PM
  • 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
    • Proposed as answer by Jaggerdude Friday, November 26, 2010 6:25 PM
    Wednesday, November 17, 2010 10:57 PM
  • 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
    Wednesday, November 17, 2010 11:14 PM
  • 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
    Friday, November 26, 2010 6:21 PM
  • 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
    Friday, November 26, 2010 6:25 PM