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:
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.
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 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 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.
Within each band, you place fields to be displayed. Crystal Reports Pro recognizes three types of 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.
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:
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.
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.
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. |
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. |
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. |
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. |
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. |
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. |
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.
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 | ||
| Font | Ms Sans Serif | |
| Regular, 8 point | ||
| Height | 330 | |
| Left | 7440 | |
| Top | 4080 | |
| Width | 1200 |
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.
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 |
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.
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:
You also learned that Crystal Reports Pro recognizes three types of fields on the report form:
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.
