Crystal Reports - Gathering data from multiple servers to consolidate to a single report

Hi

I've been asked to create a report that consolidates data from a number of database servers for a single view using Crystal.  

I've used Crystal Reports for many years but mainly with Visual Studio and I'm used to automating tasks with code (For example, I would create the above report in code by populating a dataset with the data from each server and then passing the final dataset to the report).  

I would like to know if I can create a report using Crystal that gathers data from multiple servers without having to program my way around it.

So my question is, can a Crystal Report collect data from a number of database servers to create a single view or, do I need to either code this up or, do I need to use something like a Crystal Reporting server?

Please let me know if you need more information - thank you
pm620whAsked:
Who is Participating?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

x
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

mlmccCommented:
Crystal can do it but it will probably be a bit slow.  As far as I know you won't be able to change the sources from code since multiple sources is not totally supported.

You can add multiple sources to the report and link them as needed.
A command is probably the best method.  You will need a command for each data source.

Any chance you could use one source and create linked tables in it to the other sources? At least in that way the joining would be on a database server rather than a client workstation.

You could also build the recordset in code and use the PUSH method to give the data to the report.
Here is a link describing the method

http://www.emoreau.com/Entries/Articles/2006/09/Feeding-Crystal-Reports-from-your-application.aspx

mlmcc
vastoCommented:
What kind of database servers - SQLServer, Oracle ... ? All databases can get SQL from another database or server. You can prepare a stored procedure in one of them , which is going to retrieve and consolidate the data from all the servers in one view and then use Crystal reports against this view. This will be a regular report against one view. There are different waysto handle the consolidation. For example in SQLServer you can create linked servers and directly address the tables

select a,b,c FROM server1.database1.schema1.table1
UNION
select a,b,c FROM server2.database2.schema2.table2

Therare are also other options to get data from different servers. What type of database do you have ?
Ido MilletProfessor of MIS at Penn State Erie and Owner, Millet SoftwareCommented:
If you can link to all these servers from tools such as MS Access, or SQL Server you can create a UNION query/view that consolidates the data to a single data set.

Alternatively, at least one of the 3rd-party Crystal Reports desktop schedulers listed at http://kenhamady.com/bookmarks.html allows you to automate the process of:
1. running a report (re-pointing it to a specified server) and exporting via ODBC to a table in a specified database.  That first step would use an option to discard the old data if the target table already exists.
2. Repeating the process with the other servers but now Appending the data to the existing table.
3. Run a different report that uses the consolidated data in the table.
JavaScript Best Practices

Save hours in development time and avoid common mistakes by learning the best practices to use for JavaScript.

Learn More!
pm620whAuthor Commented:
Hi Everyone

Thank you for your replies; it certainly looks possible from what you have all written.  Some additional context would be helpful I think:

The system I am reading data from is closed.  The database is Informix but an old version and the servers were not built to be queried aggressively by remote systems (they're security systems used for opening doors when security access cards are swiped - we're tracking the last time a card was swiped at a given door).  

We have tried historically to perform more complex queries against the servers and the queries time out.  We can't place triggers in the databases as this would invalidate support from the supplier as we would be making unauthorised changes to a closed system.

So whatever I do that's clever needs to be in the report or local to the reporting workstation.  If you have any additional advice or guidance based on this I would be grateful.

Thanks
vastoCommented:
The easiest way is to create an Access database and to link the tables on Informix servers. Then create a view using the linked tables. Access database will be just like a shell and will consolidate the data in one query , which you can use in your report. You can import the tables from your databases in your report but the report performance will be terrible because the report will download all the data and then will try to filter and join it . Crystal reports can do this, but it is not designed to do it. There are no indexes or any other way to optimize the data retrieval. Access database is not the best choise if you compare it with Oracle or SQLServer, but is way better compared to processing data in Crystal reports. Let me know if this makes sense.

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
pm620whAuthor Commented:
Hi Vasto

Thank you for your comment - are suggesting a union (via Access) of the tables and then accessing that query via Crystal?  

I fully understand your approach, just wanted to qualify what I think in the (almost) final step.

Thanks

Paul
vastoCommented:
Yes , Access union will work perform better and will be simpler than Crystal union. Access is not a database fro big amount of data, so if you can replace it with SQLServer or something else will be even better, but Access is a better choice than Crystal to process the data.
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Crystal Reports

From novice to tech pro — start learning today.