I have 10 Postgres Databases linked via ODBC which I use in Crystal Reports. These 10 databases are all the same, same schema and tables.
The 10 databases I need to query from all at the same time, to have one consolidated report. Is this possible? If so, how can i Link the databases to have consolidated data?
I will be using a SQL query to draw the data.
Thanks in advance
The right way will be to handle the datasource outside Crystal reports. I am not familiar with Postgres , and I don't know if you can link tables or databases. In SQL server you can use linked servers, in MS Access linked tables, there should be a similar concept in Postgres. The idea is to link an object outside the databse and use it inside the database. This will allow you to prepare a query like this:
select A,B,C FROM server1.database1.table1 UNION select A,B,C FROM server2.database1.table1 UNION select A,B,C FROM server3.database1.table1 ... UNION select A,B,C FROM server10.database1.table1
This query ( or view) will contain all the information and you can base you Crystal report on it. Crystal will work with just one database , the one which contains the query, and the query will deal with the other databases to collect the data. This will be way faster compared to the method when you add your connections to all 10 database directly to Crystal reports.
I don't think subreports are an option here . Each subreport will connect to a separate database and you will get 10 separate subreports. You may get all the data but it will be sorted for each subreport, so you will have A->Z for the first databse then A->Z for the second and so on. Ther will be 10 separate sets of data , which will be presented together but will still behave like separate recordsets.
If Postgres does not support linked databases you can use an Access database as a proxy , just link your tables and use it as a place , which will combine the data