Understanding the Different Types of Data Sources

Crystal Reports was originally designed as an add-on reporting package for an accounting program. However, it was its expansion into the world of mainstream data sources that led to its current popularity. Today, Crystal Reports supports more than one hundred different types of data sources. Some of these data sources are what would be considered traditional databases such as Microsoft SQL Server, Oracle, IBM DB2, and Microsoft Access. Other Crystal Reports data sources are more abstract forms of data such as log files, email, XML, and multidimensional (OLAP) data.

In order for Crystal Reports to support such a multitude of data sources while preserving a consistent user experience, the support for each data source is provided via a database driver. The role of a database driver is to act as a gateway between Crystal Reports and a specific type of database or data access technology. These database drivers have a pluggable architecture, meaning that each has the same type of user interface and functionality. When creating the report, one or more drivers are selected, along with the location of the actual data. When the report is executed and it comes time to load data from the database to the report, the driver performs the actual query.

To determine which database driver to use to connect to a certain data source, it's best to understand the different types of database drivers. The following sections discuss direct and indirect access database drivers.

Direct Access Drivers

Direct access database drivers are built solely for reporting from a specific type of database such as Oracle. If a direct access driver (sometimes called a native driver) exists for the database that you intend to report from, it is generally the best choice. Although they follow the standard model of a database driver, direct access drivers are tailored for that specific database. For example, if you choose the Microsoft Access direct access driver during the creation of a report, you will be prompted for the filename of the Access MDB file. Whereas if you are using the Oracle direct access driver, you will be prompted for a server name. Not only is the user experience more specific to that database, a direct access driver will often result in better performance than other methods of connecting to the same data. Table 3.1 lists some of the most common direct access database drivers.

Table 3.1. Common Direct Access Database Drivers

Direct Access Driver	Description
Microsoft Access	Used to access Microsoft Access databases
Microsoft Excel	Used to access Microsoft Excel spreadsheets
Oracle	Used to access Oracle database servers
DB2	Used to access IBM DB2 database servers

Indirect Access Drivers

As you might guess from the name, an indirect access driver is one that connects indirectly to an actual data source. Indirect access drivers are not built for any one type of database, but rather are built to read data from a variety of data sources via a standard data access mechanism. The purpose of these drivers is to allow Crystal Reports to use data sources for which direct access drivers do not exist. The two major indirect access drivers provided are ODBC and OLE DB.

ODBC, which stands for Open Database Connectivity, is a long standing technology built to connect various applications to various data sources via a common mechanism called an ODBC driver. Just as Crystal Reports has a concept of database drivers that allow data access to report developers, ODBC has a concept of ODBC drivers that allow data access to any application. The Crystal Reports ODBC database driver communicates with an ODBC driver, which in turn communicates with the actual database. ODBC drivers are generally developed by the database vendors themselves and often come bundled with the database software. Crystal Reports comes with the following ODBC drivers developed by DataDirect Inc.: Oracle, Informix, Sybase, DB2, and XML.

OLE DB, pronounced "OH-lay-dee-bee," is the evolution of ODBC. Like ODBC, OLE DB has a concept of database drivers, but calls them OLE DB providers. Crystal Reports can read most OLE DB providers. Figure 3.1 illustrates the various ways to connect to your data.

Figure 3.1. Crystal Reports data access architecture.

If appropriate, any necessary database client software should be installed and configured prior to installing Crystal Reports. However, if you've installed Crystal Reports before installing the database vendor's client software, follow the directions located in the Crystal Reports Help files to ensure correct configuration of the Crystal Reports system Data Source Names (DSN).