Adding Database Objects to Your Report

The term database objects is used to describe the various forms of data that can be added to a report. Specifically, Crystal Reports can use the following types of database objects as data sources for a report:

• Tables or system tables

• Views

• Synonyms

• Stored procedures

• SQL commands

Database objects are listed underneath connections in the Database Expert and are grouped by object type. In Figure 1.8, the various database objects are shown for the Xtreme Sample Database. In this case, there are tables, views, system tables, and stored procedures. The Add Command node gives you the ability to add SQL commands to this report.

Figure 1.8. The Database Expert presents database objects in their logical categories.

Tip

You can control the objects that are displayed in the Database Expert by setting selection, description, and filtering options accessed from either the Database tab of the Options dialog under the File menu or the Options menu option of the database's right-click context menu. This can be particularly useful when you are reporting off databases with hundreds of tables.

The following sections describe the most common database object types in further detail.

Reporting on Tables

Tables are the most basic form of a data structure. Simply put, a table is a set of fields bound together to represent something in the real world. A Customer table might contain fields that describe all the customers of a given business. An Employee table might store information about a corporation's employees such as name, title, or salary.

To add a table to a report, select the table in the Database Expert and click the arrow (>) button. The table is added to the Selected Tables list on the right side of the dialog below its corresponding connection. Most database administrators give the tables meaningful names; however, sometimes tables can have quite archaic names, such as RM564_321. A name like this isn't very descriptive, so it would be useful to rename this table to something more meaningful. To rename a table, select it in the Selected Tables list and press the F2 button (F2 is a standard convention for renaming things in Windows). In Crystal Reports, renaming a table is referred to as aliasing a table.

Reporting on Views

A view is a query stored by the database that returns a set of records that resemble a table. Views often perform complex query logic, and good database administrators create them to simplify the job of people (like report developers) extracting data out of the database. For example, the Top Customers view in the Xtreme Sample Database returns all customers who have sales of more than $50,000. From a report developer's perspective, views act just like tables and can be added to the report in the same way.

Reporting on Stored Procedures

Stored procedures, in the context of Crystal Reports, are similar to views in that they are predefined queries in the database that return a set of records. The major difference is that a stored procedure can be parameterized. This means that rather than having a preset query that returns the same data every time it is run, stored procedures return different data based on the values of parameters passed in.

Adding a stored procedure to a report works much the same way as tables and views. However, if the stored procedure has a parameter, a dialog appears when you attempt to add the stored procedure to the report. This is shown in Figure 1.9. The dialog asks you to provide values for each of the stored procedures' parameters. After you complete this and click the OK button, focus returns to the Database Expert and the stored procedure is shown in the list of selected tables. At this time, a parameter is created in the report that corresponds to the stored procedure parameter, and any values that parameter is given are passed to the underlying stored procedure.

Figure 1.9. Adding a stored procedure with a parameter invokes the Enter Values dialog.

Reporting on SQL Commands

When reporting from tables, views, and stored procedures, Crystal Reports generates a query behind the scenes using the Structured Query Language (SQL). This is beneficial because the report developer does not need to understand the complexity of the SQL language, but rather can just drop fields onto the report and get data back that matches those fields. However, sometimes report developers are quite experienced with databases and, specifically, the SQL language. Because of this, they sometimes prefer to write their own SQL query rather than have Crystal Reports generate it for them. For an introduction to the SQL language, refer to the XI Downloads section at www.usingcrystal.com and download Appendix A: "Using SQL Queries in Crystal Reports."

SQL Commands enable you to use your own prebuilt SQL query and have the Crystal Reports engine treat that query like a black box. This means that any query, whether simple or very complex, that returns a set of records can be used as a data source for a Crystal Report. To create a SQL Command, select the Add Command item under the database connection, and then click the arrow (>) button. This initiates a dialog that enables the user to type in a SQL query. Figure 1.10 illustrates a typical query.

Figure 1.10. Adding a typical SQL Command to a report.

After the query is typed in and the OK button is clicked, focus returns to the Database Expert, and the newly created command is represented as 'Command' underneath its corresponding connection in the Selected Tables area. As with all database objects, selecting the command and pressing the F2 button enables the user to rename the object.

One key feature of SQL Commands is parameterization. If you had to create a static SQL query, much of the power of SQL Commands would be lost. Fortunately, SQL Commands in Crystal Reports support parameters. Although parameters can be used in any part of the SQL Command, the most common scenario would be to use a parameter in the WHERE clause of the SQL statement to restrict the records returned from the query. To create a parameter, click the Create button in the Modify Command dialog. This initiates a dialog that enables the user to specify a name for the parameter, text to use when prompting for the parameter value, a data type, and a default value. After the OK button is clicked, the parameter appears in the Parameter list. To use this parameter, place the cursor where the parameter should be used in the SQL query, and double-click the parameter name.

When a SQL Command is created with a parameter, the report developer is prompted for a parameter value. This works much the same way as parameterized stored procedures in that a parameter is created automatically in the report that maps to the SQL Command parameter.

Caution

Unlike previous versions (9 and before), SQL Commands can no longer be centrally stored and accessed in a centralized Crystal Repository without the BusinessObjects Enterprise product. In fact, the Repository and all its reusable objects are now only available through BusinessObjects Enterprise. A Repository Migration Wizard is distributed with the Enterprise product to facilitate a quick migration from the V.9 Crystal Reportsbased Repository.