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 or a MySql database. Although Crystal Reports is capable of printing such reports, it doesn’t always run flawlessly. What is happening internally is that Crystal Reports uses a different programming library for each type of database. Due to compatibility differences between data types and connection methods, Crystal Reports can occasionally experience problems providing the exact result set you expect.
Prior to processing a report, Crystal Reports takes the data connectivity information that is saved with a report and generates custom SQL statements for the database. These statements are then delivered to the database server. Although every major database vendor claims to be compliant with SQL standards, they each have their own subtle differences that can cause them to reject the SQL language of another database vendor. 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 and 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 will have problems because the data looks 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 vendor tries to be better than the competition, companies are more concerned about performance than compatibility.
If your database server has a way of connecting to another database natively, it is best to use that method rather than having Crystal Reports connect to the different databases. For example, you can have SQL Server connect to an Oracle database and SQL Server does the work of talking to the Oracle database. When Crystal Reports connects to the SQL Server database, it sees the Oracle data as being part of the SQL Server database. Crystal Reports doesn’t realize that it is retrieving data from an Oracle database. This lets Crystal Reports only use the SQL Server libraries for accessing data and linking the tables and gives you much more reliable results. You also get better performance because the database server maintains the additional connection and links.
Fortunately, with version XI, the internal libraries have been optimized and problems that previous versions of Crystal Reports experienced are no longer an issue. Nonetheless, there are best practices to follow that help ensure you get the results you want.
When linking tables together, 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 as well.
Rather than try to link tables from different data sources together, use subreports to print the data instead. The benefit of using subreports is that their data sources are treated separately from the main table. 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 certain types of reports where using a subreport isn’t an option. An example is when 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 other records.
