I am currently working on a report where I have multiple Basic SQL Queries. With one of those Queries, I am trying to connect to a second Database. As long as the database matches the one set up from the Project properties I can see all the data. However, the one query that references a different database always returns empty. Is it possible to reference Multiple Databases’s in a report?
Yes you can. I have multiple reports looking at more than one database. Their not even the same database type(msSQL and mySQL). Depending on what your trying to do in the report would say the best way to handle it. Depending on what your trying to do, it may require seperate queries or using the nested query function. When you setup your query, you have to select the database that you want the query to look at. If you don’t set that then it will only look at your default database for the project. Personally though, I never use the Basic SQL Query. I haven’t see any benefit to using it over the SQL Query or a Named Query.
If your report is small you and you want one query to reference another you can use the nested query function, even with two different databases. If your main query is pulling back a lot of results though, I wouldn’t use the nested queries since it does an individual query against every row of the results from your main query. This can cause you to bombard your database with queries as the report runs. If its just a few rows then I don’t see it being an issue but can cause issues if you have a lot of rows to go through. You always have the option of doing separate queries then using a script data source to manipulate the data and return it as a data source your report can pull from.
If we knew more about what your trying to accomplish then your more likely to get better advice on how to proceed with it.
Thank you for your reply I got it figured out. The small things I was trying to bring data into a table and of coarse forgot to add the Data Key