|  | Click Here!

Search the site:
EXPERT SEARCH

| Programming Languages
| Databases
| Security
| Web Services
| Network Services
| Middleware
| Components
| Operating Systems
| User Interfaces
| Groupware & Collaboration
| Content Management
| Productivity Applications
| Hardware
| Fun & Games

EarthWeb sites
Crossnodes
Datamation
Developer.com
DICE
EarthWeb.com
EarthWeb Direct
ERP Hub
Gamelan
GoCertify.com
HTMLGoodies
Intranet Journal
IT Knowledge
IT Library
JavaGoodies
JARS
JavaScripts.com
open source IT
RoadCoders
Y2K Info

#

- Day 6
  - Creating Reports with Crystal Reports Pro
    - What Is Crystal Reports Pro?
      - Figure 6.1.
      - Figure 6.2.
    - Crystal Reports Pro Bands
      - Crystal Reports Pro Fields
        - Figure 6.3.
        - Figure 6.4.
        - Figure 6.5.
        - Figure 6.6.
        - Figure 6.7.
        - Figure 6.8.
        - Figure 6.9.
        - Figure 6.10.
        - Figure 6.11.
        - Figure 6.12.
        - Figure 6.13.
        - Figure 6.14.
        - Figure 6.15.
        - Figure 6.16.
        - Figure 6.17.
        - Figure 6.18.
        - Figure 6.19.
        - Figure 6.20.
    - Using Crystal Reports Pro Writer
      - File Menu
      - Edit Menu
      - Insert Menu
      - Format Menu
      - Database Menu
      - Report Menu
    - Calling the Report from Within Visual Basic 5
    - - The Crystal Reports Pro Control
        - Figure 6.21.
        - Figure 6.22.
      - Designing the Print Report Dialog Box
        - Figure 6.23.
      - Adding the Print Report Dialog Box Code
        - Figure 6.24.
    - Summary
    - Quiz
    - Exercises

## Day 6

## Creating Reports with Crystal Reports Pro

Today you'll learn how to create reports that can be called from within your Visual Basic 5 programs. To do this, you'll use Crystal Reports Pro, which ships with Visual Basic 5. You'll learn some basic concepts on how a report writer works, including:

- The concepts of report headers, detail lines, and footers

- The three main field types: database, text, and formula

When you have an understanding of the basics, you'll take a quick tour of Crystal Reports Pro to learn how to create list reports. Finally, you'll learn how to use the Crystal Reports Pro Control in Visual Basic 5 programs to run reports directly from your Visual Basic 5 applications.

### What Is Crystal Reports Pro?

Crystal Reports Pro is a complete program that helps you define reports, save their definitions to disk, and then run these reports against databases to create final printouts. Crystal Reports Pro has an added feature that lets you run the final reports from within your Visual Basic 5 application using the Crystal Reports Pro control, which ships with Visual Basic 5.

Throughout this day, you'll use Crystal Reports Pro to illustrate concepts and to work out practice examples. Start Crystal Reports Pro now and follow along through the rest of the day. You can start Crystal Reports Pro in one of two ways: from the Visual Basic 5 main menu by selecting Add In | Report Designer, or by selecting the Crystal Reports Pro icon from the Visual Basic 5 program group. If you have not already done so, start Visual Basic 5 and select Report Designer from the Add Ins menu. (See Figure 6.1.)
Figure 6.1. Starting Crystal Reports Pro from Visual Basic 5.

When you start Crystal Reports Pro, you'll see the Crystal Reports Pro greeting dialog box. Press Proceed to Crystal Reports Pro. You'll now see the main Crystal Reports Pro screen (see Figure 6.2). This is where you create, modify, and run your reports.

> NOTE: You will see the Crystal Reports Pro Registration form the first time you load Crystal Reports Pro. Complete this form and follow its instructions to register your software. After it is completed, you will not see this form again.

Figure 6.2. The Crystal Reports Pro main screen.

####
Crystal Reports Pro Bands

Crystal Reports Pro is a banded report writer. A banded report writer treats all output as "bands" of data. Each band has its own processes (such as functions it performs) and settings (properties) that you can manipulate in order to create the report layout and behaviors you need. Here are the main bands in Crystal Reports Pro:

- The header and footer bands

- The detail band

The header and footer bands contain information that appears at the top and bottom of every page of the report. This could be report titles, page numbers, print date and time, and so on. Every report has a header and a footer band.

The detail band contains the actual print lines. The detail band is the report version of a data table record. You use the report writer to lay out a detail band the same way you use a Visual Basic 5 form to lay out a data entry screen. Detail bands can have more than one physical line. However, detail bands describe only one logical record.

#### Crystal Reports Pro Fields

Within each band, you place fields to be displayed. Crystal Reports Pro recognizes three types of fields:

- Database fields

- Text fields

- Formula fields

Database fields are fields taken directly from data tables in the database you open when you first start Crystal Reports Pro. You can load any database format recognized by the Visual Basic 5 database object (for example, Microsoft Access, FoxPro, dBASE), including ODBC data sources. You add fields by selecting them from a list of available fields and placing them in the desired location on the report form.

Text fields are fields that contain explicit text you want to appear on the report form. This text is not stored in a data table. An example of a text field is Print Date. If you want this text to appear at the top of every page, you create a text field that contains it and then you place it in the header band.

Formula fields are fields that are calculated results of either database fields or text fields (or a combination of both). Crystal Reports Pro requires you to declare a formula field name and then allows you to use any existing text field, database field, or other formula field as part of the new formula field. Formula fields can be numeric or character based. For example, if you want to print the value `Expiration Date:` followed by the database field DataTable.Expire, but do not want to have to place two field objects in the detail band, you can create a single formula field, called ExpDate, that contains the following expression:

    "Expiration Date: "+DataTable.Expire

Crystal Reports Pro has several predefined formula fields available, along with a host of functions and operators that you can use to construct complex formulas, including the use of nested `If` statements to test data.

In the following sections, you'll begin a report definition to illustrate how bands and fields are used in Crystal Reports Pro.

> NOTE: For the following exercise, please make sure that you have instructed Crystal Reports Pro to utilize the Report Gallery when starting a new report. To do this, Select File | Options..., and then select the New Report tab. The option Use Report Gallery for new reports should be checked.

The Detail Band If you haven't already done so, start Crystal Reports Pro. Select New... from the File menu and then select Standard from the Create New Report dialog box. Next, click the Data File icon in the Create Report Expert Dialog. Locate and load the `CRYSRPT.MDB` Microsoft Access database file that ships with this book. It can be found in the `..\\TYSDBVB5\SOURCE\DATA` directory. Your screen should look similar to the one shown in Figure 6.3. Click the database and select Add to load the database into the report. Press Done once the database is loaded.
Figure 6.3. Loading a database with Crystal Reports Pro.

When the database is loaded, Crystal Reports Pro lets you move through the Create Report Expert tabbed dialog to create the report definition. Click the tab labeled 3:Fields to display a list of available tables and fields from the database. (See Figure 6.4.)

First, you'll add a database field to the detail band of the report. Double-click the EntryNbr field in the Database Fields box to add the field to the Report Fields. Now, click the Preview Report button to display the report and then select the Design Tab. Your report form should look like the one shown in Figure 6.5.
Figure 6.4. Displaying fields for your report.

######

Figure 6.5. Placing a database field in the detail band.

Now you'll add the company name and address to the detail band. But before you do this, expand the detail band to accept more than one line of data. Move the pointer over the solid line that separates the Details band from the Page Footer band. When the cursor turns to a double-sided arrow, press the left mouse button and pull down the detail band line to allow for several lines of data (just a rough guess at the size will do). When you are satisfied that the detail area is large enough, release the mouse button to drop the detail band line. (See Figure 6.6 for a reference.)
Figure 6.6. Expanding the detail band.

Now, we will add the following fields to the detail area. This can be done by selecting Insert | Database Field... from the Crystal Reports Pro Menu. A list box of tables and fields appears on your screen. Add the field by clicking it and dragging it to the Details band of the report.

> TIP: It is sometimes easier to work with field names than with character holders in the Details Band. The option to view the field names can be selected by choosing File | Options... from the Crystal Reports Pro menu, and then checking the Show Field Names option on the Layout Tab.

- CompanyName

- Addr1

- Addr2

- City

- StateProv

- PostalCode

Place the CompanyName, Addr1, and Addr2 fields under each other and next to the EntryNbr field. Place the City, StateProv, and PostalCode fields together on one line. As you place each field in the detail band, you'll see the field names appear, one on top of another, in the header section. Delete all the field names except CompanyName. See Figure 6.7 for the sizing and placement of the database fields.
Figure 6.7. Placing the CompanyName and Address fields on the report.

Before going any further, you should save this report definition. Select Save As... from the File menu. Enter `COMAST1.RPT` as the report name and then click OK to save the report. (See Figure 6.8.)
Figure 6.8. Saving the report form as `COMAST1.RPT`.

Now run the report to see whether everything is working. Select Print Preview from the File menu. Crystal Reports Pro automatically opens the data table, loads the records, and sends the report to a display window. You should see something like the example in Figure 6.9.
Figure 6.9. Running the first report to a window.

> NOTE: To adjust your view of the report, you can zoom in and out by selecting Report | Zoom. You can also select between landscape and portrait orientation by selecting File | Printer Setup, and then selecting the desired orientation.

Notice that you can use this window to scroll up and down the page, to "walk" through the pages of the report, to zoom in and out, and to send the report to the default printer. These options are covered in more depth later. For now, select the Design tab to close the preview window. The Header and Footer Bands You can add information that appears at the top and bottom of every page by adding fields to the header and footer bands. Now, we will add a report title and date in the header band and a page number in the footer band.

You'll need to use a text field to create a report title for the header band. Select Text Field... from the Insert menu. Enter the text `Company Master Report` in the dialog box. (See Fig- ure 6.10.)
Figure 6.10. Creating a text field.

Click the Accept button to store the text. Move the rectangle cursor to the top center of the Page Header band and press the left mouse button to drop the text field on the header band. Your report form should look like the one shown in Figure 6.11.
Figure 6.11. Dropping the text field in the header band.

You can also add titles by typing text directly on the report form. This is easier than creating text fields, but it has its drawbacks. Once you type text on the form, you cannot move the text or resize it in any way. If you want to move the field later, you'll have to erase it and re-enter the data in the new location. If you use text fields, as in the preceding example, you can simply select the field and move it or resize it as needed.

To illustrate the process of adding text directly to the report form, move the cursor to the top-left corner of the report form and type `Date:`. Next, select Special Field | Print Date Field from the Insert menu. Now, move your rectangle cursor to a location near Date: and press the left mouse button to drop the report date onto the form. Your report form should now look like the one shown in Figure 6.12.
Figure 6.12. Adding direct text and a date field to the report.

You can also add page numbers to the footer band. This time, create a text field that contains the text `Page:`. Place this text field at the bottom of the footer band. Select Special Field | Page Number Field from the Insert menu, and then place this field next to the text field. See Fig-ure 6.13 for placement and sizing information.

Save and preview the report by selecting Print Preview from the File menu. You'll see the report title, print date, and page numbers appear on each page of the report.

You need to add one more improvement to your report. Notice how the City, StateProv, and PostalCode fields print very far apart? You need to allow enough space for long city names, but you do not want to see lots of empty space on the form. What you need is a formula field that combines all three fields into a single field that has extra spaces removed.

Select Formula Field... from the Insert menu. In the dialog box, enter `CityLine` as the name for the formula field. (See Figure 6.14.)
Figure 6.13. Adding page numbers to the report form.

######

Figure 6.14. Naming a new formula field.

After you click OK, you'll see the formula window. This is where you put together the details of the CityLine formula. You see the following four sections in this window:

- Fields: This is a list of available database fields.

- Functions: This is a list of available Crystal Reports Pro functions.

- Operators: This is a list of arithmetic and logical operators.

- Formula Text: This is where you build your formula.

You can type all the information into the Formula Text window, or you can use your mouse to point-and-click items from the Fields, Functions, and Operators windows through most of the formula-building process. The point-and-click method saves time and reduces typing errors.

You need to remove trailing spaces from the right of the fields, so start the formula by double-clicking the `TrimRight()` function from the Functions list. Notice that when you add a function to the Formula Text window, your cursor is positioned ready to insert the required parameters. Because the cursor is already between the two parentheses of the `TrimRight()` function, double-click City from the Fields list. Crystal Reports Pro places the field name (along with the data table name) inside the `TrimRight()` function. (See Figure 6.15.)
Figure 6.15. Adding the `TrimRight()` function and the City field.

You need to add a similar function that does the same thing to the StateProv database field. First, move the cursor in the Formula text box to the end of the formula string and enter a plus sign (+). Next, add another `TrimRight()` function and insert the StateProv field into the function. Compare your screen to the one shown in Figure 6.16.
Figure 6.16. Adding the `TrimRight()` function and the StateProv field.

Now you need to add the PostalCode field to the formula. You don't need to trim spaces from the PostalCode field, so just add the plus sign and select the PostalCode field. Your formula should look like the one shown in Figure 6.17
Figure 6.17. Adding the PostalCode field to the formula. .

Before you save the field, you can check the syntax by using the Check button. When you click the Check button, Crystal Reports Pro Writer checks the formula for any errors and then reports the results in a message box. (See Figure 6.18.)
Figure 6.18. Checking the formula.

If you have no errors, press the OK button. Crystal Reports Pro Writer returns you to the report form. You are ready to place the newly constructed formula field on the form. Place the new field anywhere on the report form (wherever you have space). Next, delete the City, StateProv, and PostalCode fields from the detail band. You can do this by selecting all three fields with the Shift key and left mouse button and then pressing the Delete key on your keyboard. When all three fields are gone, move the CityLine formula field into place in the detail band. Your form should look like the one shown in Figure 6.19.

Save and run the report. You'll see that there are no spaces between the City, StateProv, and PostalCode fields. But you need some spaces, right? You need to edit the formula field in order to insert a comma and a space between the City and the StateProv fields and to insert two spaces between the StateProv and the PostalCode fields.
Figure 6.19. Placing the CityLine formula field on the report form.

To edit an existing formula field, select the field by clicking it with the mouse. Then, select Formula from the Edit menu. Crystal Reports Pro presents the formula window with the CityLine formula already loaded. Go directly to the Formula text box and make the needed changes to the formula. Your formula should now look like the one shown in Figure 6.20.

Press the Accept button to save the formula; then save and run the report. You now see a much better looking final line on the address.
Figure 6.20. Editing the CityLine formula.

###
Using Crystal Reports Pro Writer

Crystal Reports Pro is a great tool for putting together simple list reports. It is also excellent for creating a wide variety of labels, including mailing labels, name tags, diskette labels, and others. What follows is a quick tour of Crystal Reports Pro. For a more in-depth treatment of Crystal Reports Pro, refer to the documentation that ships with Visual Basic 5.

#### File Menu

The items in the File menu allow you to define new reports, open existing reports, save reports, print the current report, and set program-level options such as default directories, default display formats, and default database formats. Table 6.1 contains a summary of the menu items and their uses.
Table 6.1. Crystal Reports Pro File menu options.

Menu option | Description
New... | Use this option to create a brand-new report. The Create Report Expert prompts you to select a database (even if you already have one open) after you select the type of report to create. Once the database is open, you can assemble a basic report by selecting fields from the Fields Tab in the Create Report Expert, or by manually adding fields by selecting Database Fields... from the Insert menu. You can create Cross Tab reports, Mailing Label reports, Summary reports, graphs, Top N reports, and Drill Down reports from the Create New Report dialog.
Open... | This selection prompts you to open an existing Crystal Report Pro report definition (*.RPT). When the definition is open, you can edit the report and save the changes.
Save | Use this selection to save the report definition to the current report name. If no name exists, you are prompted to supply one. The default file extension is .RPT.
Save As... | Use this option to save an existing report under a new name. It is handy if you want to use an existing report as a "template" for creating a new, slightly different report.
Save Data with Report | Toggle this option on when you want to store the report's data along with the report definition.
Close | This selection closes the current report. If you have made changes, you are asked whether you want to save the report definition before it is closed.
Print Preview | This selection displays the report on screen for your review.
Print | Preview Sample... | Use this option to generate a sample report. When you select this option, you are prompted to enter the number of records to display. This can be a time-saver, especially if you are generating a report that is ordinarily very long.
Print | Printer... | This selection sends the current report to the attached printer. See the Printer menu for more options.
Print | Export... | This selection allows you to print to a file in numerous formats, including Lotus, Excel, and HTML formats.
Print | Mail... | This option allows you to send the printed report by e-mail.
Print | Report Definition | This selection prints an abstract of the current report. Information is displayed regarding the fields, headers, database, formulas, and other items placed on the report.
Printer Setup... | This selection displays a dialog box of the current printer settings.
Page Margins... | This selection allows you to set the top, bottom, left, and right margins of the report.
Options | Use this item to set program-level defaults for all reports. You can set defaults for the directory to which reports are saved and the directory from which databases are read. You can also set the default database and index formats.
| You can set the default display formats for string, numeric, currency, date, and Boolean data formats. You can also set the default fonts for the header, footer, detail, group, and total bands.
| There are also several preference settings that control how Crystal Reports Pro displays menu bars, fields on a report, and so on.
Report Options... | Use this option to define how the current report handles data fields, indexes, print previews, and print engine error messages.
Exit | This selection exits Crystal Reports Pro. If you have made any changes to any open report definition, you are asked whether you want to save the changes before exiting.

####
Edit Menu

The Edit menu contains the usual Cut, Copy, Paste, and Clear options, plus several other options that allow you to edit formulas, text fields, and summary and group bands, as well as undo changes made to your report. See Table 6.2 for a brief summary of the Edit menu.
Table 6.2. Crystal Reports Pro Edit menu options.

Menu option | Description
Undo | This option allows you to reverse changes made to your report. For example, this option can be used to replace a field you inadvertently delete from the Detail band.
Redo | This function becomes available after the Undo function is performed. It performs the exact same process that was reversed by the Undo feature. This function, in conjunction with the Undo function, is a handy tool for "What if?" kinds of layout questions.
Cut | Use this selection to cut out selected text. This only works for text that is placed directly on the report form. It does not work for any field-type objects (database, text, or formula).
Copy | Use this selection to copy selected text from your report form to the Clipboard. This copies text that was placed directly onto a form and does not work for any field-type objects (database, text, or formula).
Paste | Use this selection to paste text from the Clipboard directly on the report form. This does not place the selected text into database, formula, or text fields.
Paste Special... | This selection allows you to use the Windows Clipboard to copy information from other applications and place the information into your Crystal Report. Objects can either be embedded or linked. If they are linked, changes in the source flow through to your report.
Select Fields | This item allows you to use a "lasso" to draw a rectangle around and select an entire group of objects. You can accomplish the same effect by holding the Shift key as you select objects; however, this process is not as quick.
Formula | Use this option to edit an existing formula field. First, you must select the formula field to edit; then you select this menu item to call up the formula editor.
Text Field | Use this option to edit an existing text field. First, you must select the text field to edit; then you select this menu item to call up the Text Field edit box.
Summary Field | Use this option to edit an existing summary operation field. First, you must select the summary field to edit; then you select this menu item to call up the Summary Operation dialog box. Summary options include Sum, Average, Min, Max, Count, Variance, and Standard Deviation.
Browse Field Data | Use this option to view a list of all the possible values in a data field. First, you must select the data field on the report form to browse; then you select this menu item to see a list box containing all the unique values for this field. You'll also see field definition information in the upper-left corner of the list box (string type, length 30, and so on). This is handy if you want to review the data behind the form while you are constructing a report.
Show/Hide Sections | This option displays a dialog box that allows you to hide or display different sections of your report. This option is also available by pressing the right mouse button while on a section heading.
Delete Section... | This option is available only when grouping is used in a report. Use this option to delete a grouping that has been added to a report.
Object | This menu item allows you to edit an OLE object that you have embedded in your report. An object must first be selected before you can choose this item. The types of objects within your report appear at the bottom of the Edit menu. Each object type has submenus of actions that can be performed on that type of object.
Links | This menu item allows you to update and change links to objects embedded within your report.
Query... | Use this option to edit a query used to extract data from an SQL data source. This option is only available when you are attached to an SQL data source such as Microsoft SQL Server.
Query Title... | Use this option to modify the title of the query. Again, this option is available only when you are attached to an SQL data source.
Refresh Data... | Use this option to update data used in this report. This option is only available when attached to an SQL data source.

####
Insert Menu

The Insert menu allows you to add database, text, and formula fields to your report definition. You can also add graphic images, lines, and boxes. Crystal Reports Pro gives you shortcuts to add page numbers, record numbers, print date, and group numbers to the report definition. The Insert menu is the menu you use to add new sections, subtotal bands, summary bands, and the report grand total band. Table 6.3 provides a short summary of the Insert menu options.
Table 6.3. Crystal Reports Pro Insert menu options.

Menu option | Description
Database Field | Use this option to select a field from the attached database. You can select any field in any table. You can select the same field more than once.
Text Field | Use this option to create a text field for your report form. After you create the text field, you can manipulate the format, font, and color the same way you can in a database field.
Formula Field | Use this option to create a new formula field or edit an existing formula field. Select this menu item and you see a list box showing all the formula fields defined for this report. If you double-click one of the fields in the list, you see the formula editor with the selected formula loaded, ready for editing. If you type in a new formula name, you see the formula editor ready for you to create a new formula.
Special Field | | Use this option to create a page number field for your Page
Page Number Field | Number Field report form. This field always reports the current page number.
Special Field | | Use this option to create a record number for your report.
Record Number Field | This field always reports the current sequential record number in the selected records as sorted by the report. This field does not report the position of the record in the physical table, but the position of the record in the sorted report list.
Special Field | | Use this option to create a group number field to place on
Group Number Field | Group Number Field reports. This field can be used to report counts of group breaks within the report.
Special Field | | Use this option to create a Today's Date field to place on
Print Date Field | your report form. This field reports the date on which the report is printed.
Subtotal | Use this option to create a subtotal band for your report form. First, you must select a numeric field to subtotal. When you create a subtotal field, a new section is created automatically (if it does not already exist). You can select the grouping field to use for each subtotal as well as the sort order of the grouping field.
Grand Total | Use this menu item to create a grand total band on your report form. First, you must select a field to total, and then you select this menu item. You do not have to select a numeric field for the grand total band because the grand total band can report a count as well as a numeric total.
Summary | Use this option to insert fields for counts, sums, averages, minimums, maximums, sample variances, sample standard deviations, population variances, or population standard deviations of selected fields.
Group Section | This option allows you to set the points at which your report will break and total.
Group Name Field... | Use this option to insert a field that contains the text that uniquely identifies each group. This is a good tool to use to place a caption at the beginning of each group. A group must be inserted into the report before this option can be executed.
Line | Use this item to draw lines on your report. After a line has been placed on the report, you can resize it using the mouse pointer. You can set the line thickness, type, and color by double-clicking anywhere on the line to call up a dialog box.
Box | Use this item to place a box anywhere on the report form. You can use the mouse pointer to resize the box. When you double-click the selected box, you'll call up a dialog box that lets you set the border style, thickness, and color. You can also set the fill color of the box.
Picture... | Use this item to place a bitmap graphic image on your report. When you select this item you'll be shown the Choose Graphics dialog box, which you use to locate a graphic image file. When you select a file and press OK, Crystal Reports Pro allows you to place and size that image anywhere on the report.
Graph/Chart Expert... | Use this option to invoke a dialog that assists in the creation of a graph that can be placed on the report.
Object... | This option allows you to select and insert an OLE object into your report. You can either embed or link the object.

####
Format Menu

The Format menu gives you options for changing the font, borders, color, and display format of existing fields. You use this menu to edit the graphic, line, and box objects on your report. You can also set formatting options for existing section bands of your report. Table 6.4 provides a short summary of the Format menu options.
Table 6.4. Crystal Reports Pro Format menu options.

Menu option | Description
Report Style Expert... | The Report Style Expert is a set of pre-defined report style templates that can be assigned to the report. Just select this option from the Format menu and choose the desired style from the list box that appears.
Auto Arrange Report | This option, when invoked, arranges the fields and labels into a neat arrangement.
Font | Use this menu item to edit the font attributes of the selected fields. You must first select one or more fields; then you select this menu item.
Field | Use this menu item to change the display format of the selected field. Different dialog boxes appear depending on the field type selected.
Border and Colors | Use this menu option to set field colors, to set borders around fields, to set the width of the borders, to add shadows to the borders, and so on.
Change Line Height... | Use this option to adjust the height of the selected row. Please note that this option is disabled when a field or an object is selected.
Line... | Use this menu item to modify the thickness, type, and color of existing line objects on the report form.
Box... | Use this menu item to modify the attributes of a box object already on the report form.
Picture... | Use this option to set the cropping, scaling, sizing, and positioning of a selected picture.
Graph/Chart... | Use this item to modify the sizing, scaling, and positioning of graphic images loaded from the Insert | Graph/Chart menu item. (Refer to Table 6.3.)
Section | Use this menu item to set attributes of all the sections (bands) of the report. There are several attributes that can be set from this dialog box, but not all apply to all objects. They are Hide Section, Print at Bottom of Page, New Page After, New Page Before, Reset Page Number After, Keep Section Together, Suppress Blank Lines, and Format with Multi-Columns.

####
Database Menu

The Database menu can be used to set and update table links, to add and remove database files, to establish table aliases, to correct naming conflicts, to make sure the data set currently in use by Crystal Reports Pro is updated, and to log on or off ODBC data sources. Table 6.5 provides a short summary of the Database menu options.
Table 6.5. Crystal Reports Pro Database menu options.

Menu option | Description
Visual Linking Expert... | Use this option to view and modify relationships that exist between related tables in the database used in the current report.
Add Database to Report | Use this menu item to add additional database files to your report definition. It is possible to have more than one Microsoft Access database as a source for your report definition.
Remove from Report | Use this menu item to remove a database file from your report definition.
Set Location | This option allows you to set the physical location of the databases being used in your report. This option is extremely helpful if databases are moved or if network mappings vary for the users of your reports.
Set Alias | Use this menu item to establish helpful alias names for the tables in your report. Using meaningful alias names can make it easier to maintain your reports in the future.
Verify Database | Selecting this menu item forces Crystal Reports Pro to refresh all data tables used in the report. This is a one-time action that makes sure you have the most up-to-date data to work with for your report.
Verify on Every Print | This menu item is a toggle on/off option. When the item is toggled on, Crystal Reports Pro performs a refresh each time it runs the report. This is an "automated" version of the Verify Database menu option.
Log On Server... | Use this menu item to attach an ODBC data source to the report definition.
Log Off Server... | Use this menu item to detach an ODBC data source from the report definition.
Show SQL Query... | Use this option to view the SQL query that Crystal Reports Pro is generating and sending to your data source. You can edit the query that is being used by Crystal Reports Pro to extract data from your SQL data source.
Stored Procedures | Use this option to review the details of the procedures
Parameters... | maintained in your SQL data source.

####
Report Menu

The Report menu contains all the options for record selection, grouping, and sorting. Also available are options for database refreshing and report defaults. Table 6.6 provides a short summary of the Report menu options.
Table 6.6. Crystal Reports Pro Report menu options.

Menu option | Description
Select Records | Use this menu item to create record-level selection criteria
Expert... | for your report. You must first select a report field to use as the start of your criteria. You are then prompted to select from a list of criteria that includes >, <, =, as well as other operators.
Edit Selection | This function is similar to the Select Records menu item.
Formula | Record... | The Formula difference is that this function allows you to work in the Crystal Reports Pro script language in order to write your Pick criteria.
Edit Selection | This option allows you to set the selection criterion for the
Formula | Group... | groups to appear on this report.
Change Group | This option makes it easier for you to change how the data
Expert... | is grouped. This allows you to experiment easily with the layout grouping of the current report.
Top N/Sort | Use this option to create reports that extract data based
Group Expert... | upon a defined percentage. For example, you might use this option to generate a report that selects the top 10% of items sold for the first quarter by your company.
Sort Records... | Use this menu item to establish the sort order of the report. When you select this item, you'll see a sort dialog box that lists all the possible sort fields on the left and shows the selected sort order on the right. You can use more than one report field in the sort criteria. Also, you can indicate an ascending or descending sort at the field level.
Search... | Use this option to find records based upon criteria defined in the dialog displayed by this selection. This option can be used only while in Preview mode. Searched for records are highlighted when located.
Search Again... | Redo the search defined by the Search... menu selection.
Zoom | This option allows you to change the size of the report displayed on the screen.
Refresh Report Data | Crystal Reports Pro automatically retrieves data under only a few circumstances while in print preview mode. Use this option to reload your data if you suspect that it changed since the last time you previewed the report.
Report Title... | Use this option to place a title on the current report.
Set Print Date... | Use this option to toggle between the default system date, or to define a date to appear in the date field of this report.

###
Calling the Report from Within Visual Basic 5

When you have developed and saved your report definition using Crystal Reports Pro, you are ready to modify your Visual Basic 5 program to run the report from within your Visual Basic 5 application. Throughout the rest of this day, you'll be modifying the data entry form you built on Day 5, "Input Validation." If you haven't done so yet, start Visual Basic 5 and load the `CompanyMaster` data entry program you created on Day 5.

#### The Crystal Reports Pro Control

It is very easy to run reports defined using Crystal Reports Pro from Visual Basic 5. Crystal Reports Pro for Visual Basic 5 ships with a special control (the Crystal Reports Pro Control) that can be added to any Visual Basic 5 form. Once you drop the control on your form, you only need to set a few properties to get a report printout from your program. The control has several properties that you can alter when setting up or running a report.

First, place the Crystal Reports Pro Control onto the CompanyMaster data entry form. It doesn't matter where you place the control because it's invisible at runtime. Once you have placed the control on the form, set the ReportFileName property to `C:\TYSDBVB5\SOURCE\ CHAP06\COMAST1\COMAST1.RPT` and the WindowTitle property to Company Master Report. Leave CrystalReport1 as the default name for the control. Next, add a command button to the form. Refer to Figure 6.21 for placement and sizing.
Figure 6.21. Adding the Print command button.

Use the information in Table 6.7 to set the properties of the command button.
Table 6.7. Property settings for the Print command button.

Control | Properties | Settings
Command Button | Name | cmdPrint
| Caption | &Print
| Font | Ms Sans Serif
|  | Regular, 8 point
| Height | 330
| Left | 7440
| Top | 4080
| Width | 1200

Now add the following line of code behind the `cmdPrint_Click` event (this code line starts Crystal Reports Pro):

    Private Sub cmdPrint_Click()
       CrystalReport1.Action = 1 `force Crystal Reports Pro to run report
    End Sub

Now save and run the program. When you click the Print button, Crystal Reports Pro creates the report and sends it to a screen window. You can preview this report in the window and, if you like, use the Print button on the report window toolbar to send the report to the printer. (See Figure 6.22.)
Figure 6.22. Viewing the report from Visual Basic 5.

####
Designing the Print Report Dialog Box

There are a handful of report parameters that you can set using the Crystal Reports Pro Control. Instead of setting them in Visual Basic 5 code, you'll create a simple report dialog box that can be used to set the most common parameters. This dialog box is portable, so you can use it in any future Visual Basic 5 program.

Use Figure 6.23 and the information in Table 6.8 to construct a generic Print Report dialog box. Please note that you will be adding text boxes that have their Visible property set to False, meaning that they won't appear at runtime. These controls should simply be placed in a convenient position (such as the open space between the Printer Setup and Exit command buttons).
Figure 6.23. Building a generic Print Report dialog box.
Table 6.8. Control information for the Print Report dialog box.

Control | Property | Setting
Form | Name | frmReport
| BackColor | Light Gray
| Caption | Print Report
| Height | 2475
| Width | 6540
| Save As | Report.frm
Command Button | Name | cmdOK
| Caption | &OK
| Height | 330
| Left | 5100
| Top | 240
| Width | 1200
Command Button | Name | cmdPrnSetup
| Caption | Printer &Setup
| Height | 330
| Left | 5100
| Top | 720
| Width | 1200
Command Button | Name | cmdExit
| Caption | E&xit
| Height | 330
| Left | 5100
| Top | 1560
| Width | 1200
SSPanel | BorderWidth | 1
| Caption | (blank)
| Height | 1815
| Left | 120
| Top | 120
| Width | 4815
Text Box | Name | txtReportName
| Visible | False
Text Box | Name | txtWindowTitle
| Visible | False
Text Box | Name | txtReportDBName
| Visible | False
Common Dialog | Left | 5700
| Top | 1
Crystal Report | Left | 5200
| Top | 1
SSFrame | Caption | Destination
| Height | 1515
| Left | 120
| Top | 120
| Width | 1200
SSOption | Name | opt3dDest(0)
| Caption | Window
| Height | 330
| Left | 120
| Top | 240
| Width | 1000
SSOption | Name | opt3dDest(1)
| Caption | Printer
| Height | 330
| Left | 120
| Top | 660
| Width | 1000
SSOption | Name | opt3dDest(2)
| Caption | File
| Height | 330
| Left | 120
| Top | 1080
| Width | 1000
Combo Box | Name | cboFileTypes
| Left | 2475
| Style | 2 - DropDown
| Top | 240
| Width | 2220
Text Box | Name | txtFileName
| Height | 330
| Left | 2475
| Text | (blank)
| Top | 720
| Width | 1995
Command Button | Name | cmdFileName
| Caption | "?"
| Height | 330
| Left | 4440
| Top | 720
| Width | 260
Text Box | Name | txtCopies
| Height | 330
| Left | 2475
| Text | 1
| Top | 1140
| Width | 600
Label | BackStyle | 0 - Transparent
| Caption | File Type:
| Height | 330
| Left | 1680
| Top | 240
| Width | 900
Label | BackStyle | 0 - Transparent
| Caption | File Name:
| Height | 330
| Left | 1680
| Top | 720
| Width | 900
Label | BackStyle | 0 - Transparent
| Caption | Copies:
| Height | 330
| Left | 1680
| Top | 1140
| Width | 900

####
Adding the Print Report Dialog Box Code

After you have constructed the form, you need to add some code behind the form. First, declare two form-level variables in the declarations section. You'll use these variables to set the properties of the Crystal Reports Pro Control:

    Option Explicit

    Dim cFileName As String
    Dim cReportName As String

The `LoadFileTypes` procedure loads the various report file types recognized by Crystal Reports Pro into a drop-down combo box. Add this procedure to your project:

    Private Sub LoadFileTypes ()
       `
       ` load type selections
       cboFileTypes.Clear
       cboFileTypes.AddItem "Record"
       cboFileTypes.AddItem "Tab Separated"
       cboFileTypes.AddItem "Text"
       cboFileTypes.AddItem "DIF"
       cboFileTypes.AddItem "CSV"
       cboFileTypes.AddItem "*RESERVED*"
       cboFileTypes.AddItem "Tab Separated Text"
    End Sub

The code in the `Form_Activate` event initializes the form caption and the Crystal Reports Pro window caption. It also checks to see that a report name and database name have been passed to the form. This is where you load the combo box, too.

    Private Sub Form_Activate ()
       `
       ` fix up form caption
       If Len(Trim(Me.txtWindowTitle)) = 0 Then
          Me.txtWindowTitle = "Print Report"
       End If
       Me.Caption = Me.txtWindowTitle
       `
       ` check for passed database name
       If Len(Trim(Me.txtReportDBName)) = 0 Then
          MsgBox "Missing Database Name!"
          Unload Me
       End If
       `
       ` check for passed report name
       If Len(Trim(Me.txtReportName)) = 0 Then
          MsgBox "Missing Report Name!"
          Unload Me
       End If
       `
       ` set default copies
       txtCopies = 1
       `
       LoadFileTypes ` fill drop down list box
    End Sub

The following code section handles the selection of the report destination. Notice that this code toggles the enabled/disabled properties of the file-related controls. The controls are kept disabled unless the user select the "file" destination option. Here's the code:

    Private Sub opt3dDest_Click (Index As Integer, Value As Integer)
       Dim nFile As Integer
       `
       ` send report to window
       If opt3dDest(0) = True Then
          CrystalReport1.Destination = 0
          nFile = False
       End If
       `
       ` send report to printer
       If opt3dDest(1) = True Then
          CrystalReport1.Destination = 1
          nFile = False
       End If
       `
       ` send report to file
       If opt3dDest(2) = True Then
          CrystalReport1.Destination = 2
          nFile = True
       End If
       `
       ` enable/disable file controls
       txtFileName.Enabled = nFile
       cboFileTypes.Enabled = nFile
       cmdFileName.Enabled = nFile
    End Sub

The next section of code calls the Visual Basic 5 common dialog box to allow the user to select a filename as the destination for the report output. Notice the use of the &H2 value in the Flags property. This forces the common dialog box to issue a warning message if the user selects a filename that already exists. Once a valid file is selected, it is loaded into a form-level variable for later use. Here's the code:

    Private Sub cmdFileName_Click ()
       `
       ` set some parms
       CMDialog1.DialogTitle = "Save Report File Name"
       CMDialog1.Filter = "Text (*.txt)|*.txt|"
       CMDialog1.Flags = &H2
       `
       ` run the save as dialog
       CMDialog1.Action = 2
       `
       ` load the selected filename into control
       If Len(CMDialog1.Filename) > 0 Then
          cFileName = CMDialog1.Filename
       End If
       Me.txtFileName = cFileName
    End Sub

You need a bit of code to enable the Print Setup command button. Notice that you set the Flags property to &H40. This forces the common dialog box to display the Printer Setup dialog box. Here's the code:

    Private Sub cmdPrnSetup_Click ()
       CMDialog1.Flags = &H40 ` force the printers setup dialog box
       CMDialog1.Action = 5 ` run the printer setup
    End Sub

The code for the OK command button is the most involved of the form. This routine performs input validation, sets final report properties, sets up an error trap, and then runs the Crystal Reports Pro report. The input validation should look familiar. Note that an additional input validation test had to be invented to check the combo box. After the validation pass, a few properties of the report control are set. Afterwards, the report is generated.

The report run is wrapped in an error-trapping routine. Error trapping is covered in greater detail next week. For now, you should note that after the error trap is turned on, the code attempts to erase the output filename, if necessary, and then runs the report. Once the report is done, the error trapping is turned off.

The error routine is simple. It displays a Message box for any error encountered, except for the error that occurs when the user attempts to erase a file that does not exist. Here's the code:

    Private Sub cmdOK_Click ()
       Dim nOK As Integer ` validation results
       Dim nVldErr As Integer ` validation pass/fail flag
       Dim cMsg As String ` report error string
       Dim cTitle As String ` error title
       `
       ` perform validation
       If CrystalReport1.Destination = 2 Then
          nOK = IsValid(txtFileName, "Save File Name", True)
          If nOK = False Then
             nVldErr = True
          End If
          `
          If cboFileTypes.ListIndex = -1 Then
             MsgBox "Missing Print File Type", 0, "Validation Error"
             cboFileTypes.SetFocus
             nVldErr = True
          End If
       End If
       `
       ` did we find an error?
       If nVldErr = True Then
          GoTo OKExit ` leave now!
       End If

       `
       ` set some final parameters
       CrystalReport1.WindowTitle = txtWindowTitle ` set the window title
       CrystalReport1.DataFiles(0) = txtReportDBName ` set the database location
       CrystalReport1.ReportFileName = txtReportName ` set the report location
       CrystalReport1.CopiesToPrinter = txtCopies ` set the copies parm
       `
       ` if it's going to a file
       If cboFileTypes.ListIndex <> -1 Then
          CrystalReport1.PrintFileType = cboFileTypes.ListIndex
          CrystalReport1.PrintFileName = txtFileName
       End If
       `
       On Error GoTo ReportErr  ` set error trap
       Kill txtFileName         ` delete file if it's there
       CrystalReport1.Action = 1       ` run report
       On Error GoTo 0          ` turn off error trap
       GoTo OKExit              ` exit sub

    `
    ` report any error you get
    ReportErr:
       If Err <> 53 Then ` skip file not found msg
          `
          ` see if the error is from CRW
          If CrystalReport1.LastErrorNumber <> 0 Then
             cMsg = Str(CrystalReport1.LastErrorNumber)
             cMsg = cMsg + ":" + CrystalReport1.LastErrorString
             cTitle = "Crystal Reports Pro Error"
          Else
             `
             ` error was from VB
             cMsg = Str(Err) + ":" + Error$(Err)
             cTitle = "Visual Basic Error"
          End If
          `
          ` show the error # and text
          MsgBox cMsg, 0, cTitle
       End If
       Resume Next

    `
    ` end of this procedure
    OKExit:
    End Sub

Finally, you need a bit of code behind the Exit button:

    Private Sub cmdExit_Click ()
       Unload Me
    End Sub

Save the form as `REPORT.FRM` before you continue. You now need to make a few changes to the code behind the Print button on the CompanyMaster data entry form. You'll get rid of the Crystal Reports Pro Control on the main form because you have one on the Print Report dialog box now. You'll also set two parameters behind the Print button before you call the Print Report dialog box.

> TIP: Notice in the following code that you load the form (without showing it), set the values of the form's controls, and then show the form modally. This is a good way to pass parameters between forms--load it, pass them, show it.

>     Private Sub cmdPrint_Click ()
>        `
>        ` load the next form
>        Load frmReport
>        `
>        ` set values on the next form
>        frmReport.txtReportDBName = "c:\tysdbvb5\source\data\Crysrpt.mdb"
>        frmReport.txtReportName = "c:\tysdbvb5\source\chap06\comast1\comast1.rpt"
>        frmReport.txtWindowTitle = "Company Master Report"   `
>        ` show the form modally
>        frmReport.Show 1
>     End Sub

Now save and run the project. You should see the Company Master Report dialog box prompting you to set parameters for your print job. (See Figure 6.24.)
Figure 6.24. Running the Company Master Report dialog box.

Not only have you finished a report routine for the `CompanyMaster` project you have been working on this week, but you now have the Print Report dialog box, which can be used in any future Visual Basic 5 project that uses the Crystal Reports Pro report writer.

### Summary

Today you learned how to use the Crystal Reports Pro report writer to create a simple list report using the data tables you created earlier in the week. You also learned that Crystal Reports Pro is a banded report writer. Here are the main bands in a report:

- The header and footer bands: These bands appear on every page.

- The detail band: This band contains the equivalent of a data table record.

- The section band: This band contains subtotals or groupings of the data.

You also learned that Crystal Reports Pro recognizes three types of fields on the report form:

- Database fields: These fields are from attached data tables.

- Text fields: These fields are made up of literal text created by the user.

- Formula fields: These fields are calculated fields created by the user.

You also learned how to use the Crystal Reports Pro Control to run a report from within your Visual Basic 5 program. Finally, you created the Print Report dialog box, which lets you control the report destination, the file type, and the number of copies printed.

### Quiz

  1. List and describe each of the three bands in a Crystal Report.
  2. To which database types can Crystal Reports Pro attach?
  3. How do you insert text directly on a Crystal Reports Pro design form?
  4. How do you produce mailing labels in Crystal Reports Pro?
  5. In Crystal Reports Pro, can you browse data contained in a database that you are using for a report?
  6. How do you insert selection criteria in a Crystal Reports Pro report?
  7. How do you join tables in Crystal Reports Pro?

### Exercises

  1. Write a formula that can be used in Crystal Reports Pro to count the number of records in a list of last names. Assume a field name of NameLast.
  2. Write a formula to display a list of vendors that have not supplied their federal tax ID numbers to your accounting manager. This information is stored in a field named EmployerID.
  3. Build a Crystal Reports Pro report using the `BOOKS5.MDB` database that can be found in the `TYSDBVB5\SOURCE\DATA` directory of the CD that shipped with this book. Before starting, select File | Options, select the New Report tab, and de-select the Use Report Gallery for new reports option. Include the following items in your report:
  - Insert the fields PubID, Publisher, and Comments from the PublisherComments table.

  - Modify the page to print in landscape mode.

  - Insert the Name field from the Publishers table. Make sure that a link exists between the PubID field in the Publisher Comments table to the PubID field in the Publishers table.

  - Set a descending sort order on the Publishers.Name field.

  - Insert the report title Comments on Publishers. Format this title with Arial, 14 point bold text.

  - Insert a grand total record count.

  - Insert a page number at the bottom right of the page footer.

  - Insert the current date at the bottom left of the page footer.

  - Print the report and the report definition when you have finished the layout.

| Use of this site is subject certain Terms & Conditions. Copyright (c) 1996-1999 EarthWeb, Inc.. All rights reserved. Reproduction in whole or in part in any form or medium without express written permission of EarthWeb is prohibited. Please read our privacy policy for details.