Logging On to SQL Databases

The first step to creating a Crystal Report is to select the SQL database that you want to base your report on. There are three general communication methods you can use to connect to a client/server database with Crystal Reports : direct database drivers, ODBC, and OLE DB (pronounced oh-LAY-dee-bee).

Direct Database Drivers

Crystal Reports provides direct database drivers that work with many industry-standard client/server databases, including, among others, Oracle and IBM DB2 (the Microsoft SQL Server Direct Database driver has been eliminated from Crystal Reports, however). A direct database driver uses the native communication methods provided by the server vendor to communicate with the database server. This typically requires installing the specific client software provided by the database vendor on the PC, such as SQL*Plus for Oracle.

Crystal Reports will recognize the existence of these packages and provide a direct database driver to connect to the database. In addition, Crystal Reports provides other direct drivers to allow you to report from Microsoft Outlook folders, Microsoft Exchange folders, Lotus Domino databases, Internet web server activity logs, and the Windows NT/2000/XP event log. You can even write reports based on the local file system, which consists of the file and directory structure of your C drive or a network drive. More details on these specialized types of reports can be found in Chapter 20.

Using direct database drivers to connect to the database server has two general advantages:

• Because fewer layers of communications protocols are being used, there may be a slight performance improvement when reporting.

• The direct database driver may allow more flexibility for creating more server-specific SQL statements or other query features for reporting.

ODBC

Although many companies standardize on database servers that Crystal Reports provides direct database drivers for, many other database systems and specialized data systems exist that you may want to report on. Some standard method of communication is needed to connect standard PC clients with the myriad specialized servers and systems that exist. Microsoft designed open database connectivity (ODBC) to accomplish this communication.

As a general rule, any server or proprietary data platform that is ODBC-compliant can be used with Crystal Reports. If the database or system vendor provides a Windows ODBC driver for its system, Crystal Reports should be able to report against that server or system. Because it has been accepted as an industry standard, ODBC is in widespread use.

Tip  

Crystal Reports installs some generic ODBC data sources for common database files and formats, including an ODBC data source for using the sample XTREME.MDB database. Before you can use Crystal Reports to report against other ODBC data systems, you must set up an ODBC data source. Use the ODBC Administrator from Windows Control Panel to set up the data source.

OLE DB

Microsoft has added an additional OLE DB standard to extend its previous universal data connectivity method, ODBC. OLE DB provides data access in much the same manner as ODBC. A data provider acts as an interface between disparate client and server systems. Data providers are available not only for typical relational database systems, but also for more nontraditional data sources, such as spreadsheets, web servers, and multidimensional OLAP data sources.

Crystal Reports supports OLE DB data sources that are installed on your client PC. Various client applications, such as OLAP client software, will automatically install OLE DB data providers.

Choosing the Database

When you first start Crystal Reports, you can immediately choose and log on to a client/server database before you open an existing report or create a new report. If you don t log on but open a report based on a client/server database, you will be prompted to log on as soon as you try to refresh the report or choose any other function that requires the database to be read.

If you want to create a new report based on a client/server database, you can use a report wizard or the Blank Report option. If you use a report wizard, the first section of the wizard will display your database connection choices.

If you chose the Blank Report option to create a new report, the Database Expert will appear immediately.

The Database Expert combines in one place all data sources, including direct database drivers, ODBC, and OLE DB. You may choose from one of these connection types from several places, depending on whether you ve used data sources in the past (recently used data sources will appear in the History category), whether you re already logged into a data source from using another report (currently connected data sources will appear in the Current Connections category), or whether you ve added a database connection to the Favorites folder. To maintain connections in your Favorites folder through the Database expert, simply right-click on the desired database entry and select Add to Favorites from the pop-up menu.

You ll also find a Data Explorer folder entry for the repository, where you can choose a SQL Command or new Crystal Enterprise Business View as a data source (SQL Commands are covered later in this chapter and Business Views are covered in Chapter 17). Click the plus sign next to the repository category to log on to Crystal Enterprise repository and choose the desired SQL Command or Business View.

If you don t see the data source you wish to connect to in any of these categories, you can create a new connection to a database by clicking the plus sign next to the Create New Connection category. This will display an additional set of database categories that you can choose from. Again, click the plus sign next to the desired category to create a connection using that connection type. Particular categories that you ll probably use to connect to standard client/server databases include ODBC, OLE DB, and More Data Sources.

Tip  

As if ODBC and OLE DB aren t enough alphabet soup in the Database Expert, you are also presented with the abbreviations RDO and ADO after the ODBC and OLE DB categories. These are abbreviations for yet more Microsoft standards for database connectivity. RDO (which stands for Remote Data Objects) and ADO (which stands for ActiveX Data Objects) are technical terms to describe the internal method Crystal Reports uses to connect to ODBC and OLE DB data sources.

Clicking the plus sign next to the ODBC will display a separate dialog box listing all the predefined ODBC data sources (use the ODBC Administrator from Windows Control Panel to add a data source if you don t see the desired data source here). Choose the desired data source and click the Next button in the dialog box. Depending on the data source you ve chosen , as well as the database type it connects to, you ll probably see additional dialog boxes asking you to supply valid logon credentials.

Clicking the plus sign next to the OLE DB category will also launch a separate OLE DB dialog box. You ll initially see a list of installed OLE DB providers, or database-specific connection drivers, that have been installed on your PC. Choose the provider you wish to use and click the Next button. As with ODBC, the remaining options in the OLE DB dialog box, such as those to choose a specific database and supply logon credentials, will vary according to the provider you choose.

If you wish to use a direct database driver, click the plus sign next to the More Data Sources category. This will expand the Database Expert with yet another level of subfolders . Look for a description that matches the type of direct driver you wish to use, such as Exchange Folders or Lotus Domino (other non-SQL data sources will appear in this category as well ”these other data sources are covered in more detail in Chapter 20). As with other connection methods, these drivers will provide database-specific dialog boxes asking you to provide logon credentials.

If the entry no items found appears below the chosen direct driver folder, it may be that you haven t properly installed necessary database client software on your PC. For example, you may see this message underneath the Oracle category if you haven t installed the Oracle SQL Plus client software on your PC.

Tip  

Not all database drivers will be installed by default when you install Crystal Reports. The first time you choose one of these data sources in the Database Expert, you may be prompted to insert the original Crystal Reports CD to install the specific driver. Also, if you add additional data sources from Crystal Reports Setup, you ll often see an additional related category appear within the Create New Connection category.

Once you ve successfully logged on to the database, the Database Expert will expand the folder you originally selected and display a list of databases, schemas, tables, views, or stored procedures available in the database (the display will vary depending on the type of database chosen). Figure 16-2 shows the Database Expert with a list of available SQL Server database tables with two tables already having been added to the report.

Figure 16-2: The Database Expert

Select the table, stored procedure, or view that you want to include in your report, either by double-clicking it or by selecting it and clicking the single right arrow. You can also CTRL-click on more than one table and add them at once with the right arrow, or you can select one or more tables and simply drag them to the Selected Tables box. And if you want to add all the tables to the report (probably not a very common occurrence), you can click the double-right arrow. As you add tables, they will appear under the Selected Tables list on the right side of the Database Expert. If you accidentally add the wrong table or tables, you may highlight them in the Selected Tables list and remove them with the left arrow. You may remove all previously added tables with the double-left arrow.

Tip  

You may also right-click different entries in the Available Data Sources list to display a pop-up menu to perform many of the operations discussed in this section.

If you don t see all the tables you expect, or you wish to limit the set of available tables to a certain database owner or certain table name pattern, right-click while pointing to the Available Data Source list and choose Options from the pop-up menu. This will display the Database Options dialog box (discussed later in the chapter under Changing SQL Options ). After choosing options in this dialog box, you may then choose from any additional entries that appear in the Available Data Sources list. Once you ve added the desired tables to the report, click the Links tab in the Database Expert to link the tables together on common fields (more detailed information on linking fields is found in the Linking Tables section later in this chapter). Once you ve chosen and linked tables, close the Database Expert by clicking the OK button.

Keep in mind that once you log on to a SQL database from the Database Expert, you remain connected to that database even if you close any reports that are based on that database connection. If you then begin a new report and display the Database Expert, the database you are already connected to will appear in the Current Connections category of the Database Expert. If you don t want to use these tables in your report, you may merely ignore them and choose another database entry. If you d prefer to release your connection to this existing database and not see it in the Current Connections category, you need to log off the original server, either by closing and restarting Crystal Reports or by using the Log On/Off Server option. If you have a report open, choose Database Log On/Off Server. If you don t have a report open, choose File Log On/Off Server. In either case, choose the database you wish to log off from in the Current Connections category and click the Log Off button.

Changing SQL Options

Depending on the data source you choose, you may not see all the database elements you re looking for in the Database Expert. In particular, if your database supports SQL stored procedures (precompiled SQL statements that may contain parameters) or views (virtual tables that may combine several actual database tables together into one group ), you will want to make sure you can see these in the Database Expert if your reports require using them. Or, you may want to limit the tables that you see in the Database Expert to only the table names that match a certain pattern or that are owned by a certain database user .

To make these choices, right-click anywhere in the Available Data Sources list on the left side of the Database Expert. Choose Options from the pop-up menu. The Database Options dialog box will appear, as shown in Figure 16-3.

Figure 16-3: Database Options dialog box

Tip  

You may also make changes to this dialog box when the Database Expert isn t being displayed. Choose File Options from the pull-down menus and make changes to the Database tab of the Options dialog box.

The Tables and Fields section lets you determine how tables and fields appear in the Database Expert and Field Explorer. You have several choices for how you want table and field names sorted, such as the ability to list tables in the Database Explorer and fields in the Field Explorer alphabetically .

The Data Explorer area of this dialog box lets you limit the tables that appear in the Database Expert when you log on to a database. Check the table types (Tables, Views, Synonyms, Stored Procedures, and System Tables) to determine the types of database elements you want to appear. You can also add a table name or table owner pattern to limit the list of tables to those that are named like the pattern or owned by a database user who matches a pattern.

Advanced options determine various database behaviors, depending on the type of database you re using and how you want it to behave. These advanced options are

• Use Indexes or Server for Speed Choose this option to use index files for PC-style databases (such as Microsoft Access and Paradox), and use a SQL WHERE clause with SQL databases. In most cases, choosing this option dramatically improves reporting performance.

• Perform Grouping on Server Choose this option to have Crystal Reports push as much of the subtotaling and aggregation as possible to the database server, to improve reporting performance. Certain conditions required to take full advantage of this feature are discussed later in the chapter under Enabling Server-Based Grouping.

• Database Server is Case-Insensitive Choose this option to ignore case when doing record selection with a database (this option may not affect your particular database, as all databases cannot be made case insensitive).

• Select Distinct Data for Browsing This option will continuously read the database until it has retrieved the first 500 unique values of a field when you browse the field in the Field Explorer or Formula Editors. If you leave this option turned off, Crystal Reports reads only the first 500 records in the table, even if there are a few (or no) unique values. Although you ll have bigger browse lists with this option turned on, you may also suffer from slower performance.

• Perform Query Asynchronously This option allows you to stop a query from processing on the database server before database records are returned to Crystal Reports. In some cases, queries submitted to the SQL database can take minutes (sometimes hours) to run. By selecting this option, you can click the Stop button (the black square) at the right of the Preview tab to cancel the query on the database server. Note that this option applies only to certain databases and database drivers ”not all databases and drivers support this option.

• Verify Options These options determine when Crystal Reports reads the database layout to determine if additional fields have been added, field names or data types have changed, and so forth. If database fields have changed, a message will appear and the Field Explorer will reflect the changes once you respond to the message. If you leave these options turned off, you must manually verify from the Crystal Reports Database pull-down menu if you want database changes to be recognized.

Changing to a Different Database

You may initially create a report based on a PC-style database, such as Microsoft Access, and then decide later to convert the report to use a similarly organized SQL database. Perhaps the Access database has been upsized to SQL Server, or you may initially be developing reports against a test database in Btrieve, but the reports will eventually have to run against an identical Oracle database. Or else you may encounter other situations in which you initially develop a report against a specific database type, such as an ODBC database, which now needs to be pointed to an alternate ODBC database.

In any case, Crystal Reports provides a single, streamlined choice for choosing a different database. Choose Database Set Datasource Location from the pull-down menus, which will display the Set Datasource dialog box, shown in Figure 16-4.

Figure 16-4: Set Datasource dialog box

If you wish to replace a single table with a new table, select the particular table you wish to replace in the Current Data Source List. Or, you can replace an entire database, including all its tables, by selecting the actual database name above the Properties and individual table entries. Then, expand the database category in the Replace With list where the new database or table resides. Select a like object type (if you selected the entire database above, select a database name below ”if you chose a single table above, select a single table below). If you choose a database in the Replace With list that requires logon credentials, supply proper credentials. Once you ve chosen the desired tables or database, click the Update button.

If table or field names have changed in the new database, you ll be prompted to remap old fields to their new names. This is covered in more detail in Chapter 18.

Adding Additional Tables to the Report

Once you ve initially chosen and linked tables and continued with report design, you will often find that you need to add and link additional tables to your report afterward. To do so, click the Database Expert toolbar button in the Expert Tools toolbar, or choose Database Database Expert from the pull-down menus. The Database Expert will return, where you can add additional tables from the Available Data Sources list. Once you ve added the additional tables, click the Database Expert Links tab to link the tables together (linking is discussed in detail later in the chapter under Linking Tables ).

Any tables that you ve previously added to your report will already appear in the Selected Tables list. If you attempt to add one of these existing tables again, you ll be prompted to give the second occurrence of the table an alias, because each table used in the Database Expert Links tab must have a unique name.

Tip  

You may want to intentionally add the same table to a report more than once. If, for instance, you have a common lookup table that is used with several master or transaction tables, you won t be able to retrieve lookup information by linking all the transaction or master tables to the same lookup table. You ll need to add the lookup table to the report multiple times, using a different alias each time. You can then link each transaction or master table to the different aliased versions of the lookup table.

Removing Unused Tables from the Report

You may inadvertently add too many tables to your report, or you may no longer need tables that you used earlier in the report design process. If the Database Expert is displayed, just select the table you want to remove from the Selected Tables list and click the left arrow. If you want to remove all tables from the Selected Tables list and start table additions over from scratch, click the double-left arrow. If any of the tables you attempt to remove are in use on the report, you will be so notified and prompted to confirm their removal.

If you have proceeded to report design and are currently working in the Design or Preview tabs, redisplay the Database Expert with the toolbar button or Database menu option and delete tables from the Selected Tables list.

Caution  

Make sure you really want to remove the table before you click the left arrows and close the Database Expert. You can t undo a table removal. Also, if you remove a table that is referenced in any formulas, the formulas will no longer work after the table is gone. If you remove a table by mistake, redisplay the Database Expert to re-add and link the table. Or if you had a known good copy of the report saved on disk, you may wish to close the existing report without saving changes and open the known good copy.