SQL Database Utilities

Chapter 11 - Using SQL in Crystal Reports
Mastering Crystal Reports 9
by Cate McCoy and Gord Maric
Sybex 2003

Crystal Reports provides several database utilities when working with ODBC, OLE DB, and SQL native driver data sources. You can access these utilities using the Database option on the main Crystal menu. The submenu is shown in Figure 11.20. Let’s look at each option separately.

Figure 11.20. SQL database utilities

Database Expert This option opens the familiar Database Expert for adding and removing data sources from a report in Design mode. This option is available for all data sources and is not limited to ODBC, OLE DB, or SQL native drivers.

Set Datasource Location This option can be used to point Crystal to a different set of tables to report on as long as their database structure is identical to the one currently in use. An example of when this option can be used is in developing reports on a test machine that will be ported to a production machine; the table names, fields, and attributes are all identical, but the name of the server and database are different. For example, you might use this when scaling a Microsoft Access database up to a SQL Server database for use as an enterprise data source. Figure 11.21 demonstrates changing a data source from a SQL Server connection to an ODBC connection, which you might do to build a test database.

Figure 11.21. Changing a database

Log On Or Off Server When you connect to an ODBC, OLE DB, or SQL native driver data source, you have effectively logged on to a database server. A connection to this server is maintained until you either log off or close Crystal Reports. Use this option to log onto or off data sources as you need them or finish with them.

Verify Database Select this option to interactively check whether the data being used by the report is the most current data available in the database.

Verify On First Refresh This option is set to either on or off. When this option is enabled (denoted by a check mark to the left of its name in the menu), it will automatically perform the Verify Database task before a report is printed.

Show SQL Query Use this option to view in read-only mode the actual SQL query being sent from Crystal Reports to the data source.

Perform Grouping On Server Select this option to tell the database management system to group the data before returning it to Crystal Reports, thereby off-loading work to the server that would normally be done in Crystal. Using this option will have a positive effect on the speed of data retrievals between Crystal and a server.

Select Distinct Records Using this option is the equivalent of adding the DISTINCT keyword to the Crystal Reports SQL query before it is sent to the server. The net effect of using DISTINCT is that duplicate values are suppressed. This option cannot be used if a stored procedure is being used as the data source.

Use of content on this site is expressly subject to the restrictions set forth in the Membership Agreement

Conello © 2000-2003     Feedback