Chapter 16: Reporting from SQL Databases

Overview

As newer leading-edge database applications are released, fewer and fewer make use of PC-style databases. The performance and capacity limitations of local PC databases often limit their usefulness for larger, performance-dependent applications. Instead, most newer applications rely on client/server database systems. A client/server system includes two parts : a client, typically a PC running software such as Crystal Reports or a data-entry application, and a server, typically a larger, high-end PC running Windows NT/2000, a midrange , or even a mainframe computer. The server maintains the database, and the client makes requests of the server for database access. Many different client/server databases exist, with Microsoft SQL Server, Oracle, IBM DB/2, and Informix being among the more popular ones.

Caution  

Some SQL databases and database connectivity methods can be used only with the Professional, Developer, or Advanced Developer editions of Crystal Reports. The Standard Edition reports only on a limited set of databases. If you want complete database flexibility, you should probably purchase the Professional, Developer, or Advanced Developer edition.

It s important to understand and contrast the differences between a client/server database system and a PC-style database system that is installed on a shared local area network (LAN). For reporting purposes, in particular, a LAN-based database system presents a much more serious performance hurdle than a client/server database. Figure 16-1 shows a PC reporting on two database environments. The first depicts a Microsoft Access database on a LAN server. The second depicts the same database on a SQL Server system.

Figure 16-1: Retrieving 1,000 specific records from a LAN-based database vs. a client/server database

The LAN-based scenario places heavy burdens on both the network and the PC making the reporting request. In this scenario, the PC must read the entire 100,000-record Access database across the network, picking and choosing the records that meet the report-selection criteria. This requires large amounts of data to be passed across the network, and the PC has to perform all the selection logic itself.

The client/server environment is much more efficient. The client PC simply makes a request to the database server via a Structured Query Language (SQL) request. The database server, presumably a larger, high- powered PC, Unix computer, or mainframe, runs database software designed to process such queries very efficiently . It directly queries the 100,000- record database and sends only the required 1,000 records back across the network. This places less demand on the client and the network, and the whole process typically takes less time.