• 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

Chapter Six

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 guide. 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.

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.

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.

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.

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.

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.

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.

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

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.

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.

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:

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:

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:

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:

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 guideS5.MDB database that can be found in the TYSDBVB5\SOURCE\DATA directory of the CD that shipped with this guide. 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.