Using Multiple Data Sources
Complex reports often require printing data of different origins. This can happen when you have tables in a SQL Server database and they need to be linked to tables in an Oracle database. There can also be SQL Server databases on different servers on the network. Although Crystal Reports is capable of printing such reports, it doesn’t always run flawlessly. Using multiple data sources has the potential for a variety of problems. There is no easy fix.
Crystal Reports, by design, takes the data connectivity information that is saved with a report and generates SQL statements with it. The statements are then delivered to the database server. Although every major database vendor claims to be compliant with the SQL standard, they each have their own subtle differences that can cause them to reject the SQL language of another database. But Crystal Reports doesn’t have the capability to, within a single report, generate SQL that is compatible with multiple variations of the SQL specification. It will inevitably create non-compatible SQL for one of the databases. You will have a compatibility issue.
That is not all. Different database servers manage their standard data types differently, chiefly in the way they store them internally. The way one database represents an Integer can be totally different from another database. When Crystal Reports tries to link these two fields together, it won’t be able to because they look like different data types.
While it is true that Crystal Reports is designed to work with the majority of database servers, it doesn’t imply that the database servers themselves will work with one another. Since every database tries to be better than the next database, companies are more concerned about performance than compatibility. There are limitations with reports using different databases.
Fortunately, there are some general rules you can follow to reduce the headaches caused by these problems. You should be able to generate the reports you need if you follow the following rules (with a little trial and error).
When linking tables together, only use fields that are of the String data type. String is the most consistent data type among different database servers and has the least likelihood of causing problems. This is no guarantee against all problems, however. For example, a string can be represented by variable and fixed length data type and this can create incompatibilities.
Rather than try to link tables from different data sources together, use subreports to print the same data. The benefit of using subreports is that they are treated individually. When Crystal Reports generates the SQL statements for the sub-report, it does so independently of the main report. Also, it has only to worry about working with a single database driver. This independence eliminates a lot of potential problems. Using subreports also allows for more flexible linking options between a main report and subreport. You aren’t limited to linking with strings and you can also use formulas to perform data type conversions when necessary.
There are reports where using a subreport isn’t an option. For example, if all the detail fields need to be printed side by side, you can’t use subreports because the fields will have to be printed underneath the others. In this circumstance, find out if your database lets you create links to outside databases. Putting the links within the database server itself puts the responsibility of managing this data within the server. Crystal Reports benefits because now it only has to use one database driver to retrieve the data. You also get better performance because the database server maintains the additional connection and links.
