Chapter 15, "Displaying and Printing Reports," explains how to send information to a printer from within a Visual Basic application. However, creating great looking reports requires a unique program designed specifically for that purpose. Crystal Reports is a complete reporting tool from Seagate Software Company. A single-use copy of the Crystal Reports product, Version 4.6, is included on the CD-ROM that comes with Visual Basic 5. By using this reporting tool, you can create professional looking reports to display on-screen with your application or send to a printer.
In this chapter, you learn how to use the Crystal Reports application to produce usuable, professional looking reports with very little effort. In addition, you are going to see how to add Crystal Reports to your Visual Basic application by making use of the OCX control that comes with it.
This section describes Crystal Reports and then takes you on a quick tour of the tools, facilities, and options available through Crystal Reports. Crystal Reports is a powerful yet easy-to-use program for creating custom reports, lists, and labels from data in your existing databases. Crystal Reports works by connecting to one or more of your databases. Using these connections, Crystal Reports draws in the values from database fields you select and uses them in the report, either in their original form or as part of a formula that generates more complex values. Crystal Reports is designed to work with all kinds of data, such as the following:
There is a wide range of built-in tools that you can use to manipulate data to fit the requirements of the report. These tools enable you to:
The data from your database can be placed wherever you need it on the report and can be highlighted with special fonts and font sizes. Using Crystal Reports, your reports can be as simple or as complex as your needs require. Once you have designed a report for your application, you can use it within the application or as a template to create other similar reports. Crystal Reports was created to enable technical and non-technical users to create customized reports quickly and easily by using a variety of databases. While most database programs include their own report generators, they are usually too difficult for non-technical people to use, and they generally require a good understanding of how that database program works.
NOTE: No matter how much you modify the data in a report, Crystal Reports does not write any data back into your original database.
Crystal Reports connects to almost any database available on the market. There are actually two methods of connecting to a database, Data File and SQL/ODBC. The first method is designed for the simpler, PC-based databases, such as dBASE and Microsoft Access. The other method is to use ODBC (Open DataBase Connectivity) to connect to any database that has an ODBC connection. Some of the databases in this group are Microsoft Access, Oracle, Sybase, and Microsoft SQL Server.
When you start Crystal Reports, you see the plain application window shown in Figure 16.1. After you begin working with a report, Crystal Reports contains two separate tabbed windows that you use to design and test your reports: the Design window and the Preview window. Neither of these windows has its own menus. Instead, they use the commands from the main menu. They also have scroll bars at the bottom and right side of the window. Most of the Crystal Reports workspace is used by the Design and Preview windows (see Figure 16.2). These windows have their own unique features and functions, which are discussed in the following sections.
FIG. 16.1
The Crystal Reports main window is used to control all reporting features
in the product.
The Design Window The Design window is divided into two separate sections that provide on-screen information that aids in the designing of the report. The Edit box is the large white area of the Design window. This is where you actually build your report. The horizontal lines in the edit box separate the report into sections. As you add new groupings to your report, more sections will appear in the edit box. The gray area to the left of the Edit box provides additional information to assist you in placing the data and other objects into the report. The horizontal lines just mentioned extend into the gray area, enabling you to identify which section is which. As with most Windows-based development tools, Crystal Reports has several tools incorporated into the interface. The Edit box has a ruler that can be displayed. It also has a Snap-to-Grid feature that assists in lining up objects on the report. Access to the Crystal Reports functions and features are via the toolbar at the top of the main window, the main menu, or by using the right-click pop-up menus directly on the report. Depending on what you are doing, Crystal Reports provides a wizard to help you in the process of creating:
FIG. 16.2
The Design tab displays the report in the development mode. No data is displayed
on this tab.
In addition, the right-click pop-up menus provide access to the related functions for a selected object. Some of these options are:
This chapter only touches on some of these options. To see how to make use of them, see the section "Using Crystal Reports to Create a Report."
After you select your database, the Design window is displayed. You then use this window to insert and format the data that you need in your report. When you start a new report, five sections are created automatically in the Design window (see Table 16.1).
Section Name | Description |
Title | Displays the report title, data, and any other information that needs to appear at the top of the report. Information displayed here is shown only once. |
Page Header | Similar to the Title section; this information is displayed at the top of every page. |
Details | Displays the detail information from the query. |
Page Footer | Usually displays the page number and any other information that you want at the bottom of each page. |
Summary | Displays information only on the last page of your report. |
Reports are built by inserting data fields, formulas, and other information (record counts, record numbers, and so forth) into the Details section of the Design window. You use the Insert menu to select or create the fields you want to insert on the report. Subtotals and other group values are added by selecting a field and then building the conditions to generate the new subtotal or group value (in other words, change of customer number, change of state, and so forth). These group sections are created as needed, and the values are placed in that section. If you want the value to be some other place on the report, you simply select it and drag it to where you want it located.
Although you are not really adding any totals using the Formula Editor, the Total tab that is covered in "Defining Totals in the Report" later in this chapter take you through the process of selecting fields to sum or total on the report.
The Preview Window To see how your report will look when printed, switch over to the Preview window. Whenever you select this option, Crystal Reports gathers the data, performs any defined calculations, and then displays the report (see Figure 16.3). Once the data is displayed, you can review the positioning and formatting of all information on the report. Additionally, you can see the results of all summaries, calculations, and record and group selections. In effect, this is what the final report looks like. A nice feature of the Preview window is that you can still modify the format of your report without having to return to the Design window.
The Preview Window's Look and Feel Working in the Preview window does have a different look and feel than working in the Design window. Each field in the database can contain hundreds of values, depending on the number of records in the database. When you place a field in the report on the Design window, a single field box is displayed that represents all of those values. When you select this field, sizing handles appear and the border changes color (see Figure 16.4).
FIG. 16.3
Use the Preview window to see how a report will look printed, without actually
printing it.
FIG. 16.4
Although the new border color doesn't show up in this figure, it is yellow
when you see it on-screen.
In the Preview window, on the other hand, you are working with the actual data. Instead of a field box, the values themselves are displayed. When you select a field, a sizing box appears around every value from the selected field. Despite the difference in look and feel, however, the process of building and modifying the report is the same in both windows.
Crystal Reports contains experts that assist you with the process of creating reports. When you choose File, New, the Create New Report dialog box displays, as shown in Figure 16.5.
FIG. 16.5
This dialog box is also referred to as the standard Report Gallery, and it
shows each Report Expert you can choose from.
The Report Gallery displays a series of icons representing the different types of experts that are at your disposal. Each report expert takes you through the steps needed to build a report. If you simply want to build a new report based on one that already exists, click the Another Report button. The program will make a duplicate of the original report. You then can modify it as needed to create the new report. If you want to build a report from scratch, click the Custom button. Several Report Type and Data Type buttons appear at the bottom of the Report Gallery (see Figure 16.6).
FIG. 16.6
The Custom Report buttons enable you to create a report from scratch.
Depending on the type of report you want to create, click the corresponding button and follow the instructions if you selected an expert, or simply build the report if you choose to start from scratch.
Adding Calculations Crystal Reports uses formulas and functions to help you create reports more quickly and easily. By using these formulas and functions, you can do the kind of number crunching and data manipulation that is needed for an advanced database report. To create a calculated field, use the Edit Formula dialog box, shown in Figure 16.7. The Formula Editor enables you to work with both formulas and functions. A formula is a set of instructions that are used to calculate information you can't get directly from the database. If a database record has two fields, Unit Price and Quantity Sold, but does not have a field that multiplies both fields to calculate Sales Price, then you need a formula to perform this function to display it in a report. This type of formula is called a simple formula, which uses the standard arithmetic operators. However, not all of your calculations can be reduced to simple formulas. Some will require complex calculations or manipulations of the database fields.
FIG. 16.7
Use the Formula Editor to build simple and complex calculations.
If you want to display the average monthly unit sales for the previous year, rounded to the nearest unit, you need a mathematical formula. The functions required to perform these activities involve a fair amount of data manipulation. While some of this can be done by using Crystal Reports operators alone, many of them can't be done without the use of functions. In fact, even those that can be done with Crystal Reports can be done more quickly and efficiently by using functions.
A function is a predefined procedure or subroutine that is used to evaluate, calculate, or transform data from the database. An example of a function is the use of the UpperCase function to transform all characters in a string to uppercase characters. When you use a function, Crystal Reports performs the set of instructions that are built into the function, without your having to specify each operation separately. By performing multiple operations with a single command, this kind of function is similar to shorthand writing in that it makes it easier to create reports.
The Formula Editor is divided into four sub-windows that can be used to build the calculation (refer to Figure 16.7). The Fields window lists all of the database fields that are available to you, based on the tables you have previously attached to the report. The Functions window lists all of the functions that Crystal Reports has available for you to use in a calculated field. The Operators window lists all of the operators that you can use when creating a calculation. The largest and last window is the Formula Text window, where the calculation is actually built. You can choose to use the different windows to select items or you can type directly into the formula text area.
NOTE: You can select an item from the Formula Editor windows (Fields, Functions, or Operators) by double-clicking it, or by selecting it and then clicking the Select button at the bottom of the editor to add it to the calculation.
You can combine fields, functions, and operators to create complex calculations from database fields. This enables you to create a very complex calculation in pieces of the final calculation and then combine them together to form the finished calculation.
Filtering Your Data When you select a field to appear on your report, Crystal Reports prints field values from every record in the selected database. However, in many cases, you may want to include only a specific group of rows from the database rather than including all the rows. For example, you may want only a specific group of customers from the database, or you may want to include only rows from invoices that fall within a particular range of dates. With Crystal Reports, this filtering process is easy. The program includes four options, listed in Table 16.2, on the Report menu for restricting or filtering your report to specific records or groups of records.
Menu Option | Description |
Select Records | Enables you to limit the number of records in your report based on a condition or conditions specified using a selection formula dialog box. |
Edit Record Selection Formula | Uses the Formula Editor to enable you to modify the selection condition. |
Select Groups | Enables you to limit your report to a specific group or groups, based on a condition or conditions specified using a selection formula dialog box. |
Edit Group Selection Formula | Uses the Formula Editor to enable you to modify the selection condition. |
Even though you will be able to create professional-looking reports after completing this chapter, there are many more features included in Crystal Reports. In the next section, you see how to create a report by using the Crystal Reports program, and then learn how to access the report from a Visual Basic application.
The first step in designing your reports is to start up Crystal Reports. You can run Crystal Reports in either of two ways. First, you can select the program from the Visual Basic folder on the Start menu of Windows 95. By using this option, you don't even need to have Visual Basic loaded to work on your reports. The second method of starting Crystal Reports is to choose Visual Basic's Add-Ins, Report Designer. Either method starts Crystal Reports and places you in the startup screen that appeared earlier in Figure 16.1.
After you start Crystal Reports, you can create a new report by choosing New, File or by clicking the New Report button on the toolbar. In the ew Report dialog box that opens, you can specify the type of report that you want to produce (see Figure 16.8).
FIG. 16.8
In the Create New Report dialog box, you can select options for your report.
Crystal Reports supports the creation of the following basic report types:
When you select the type of report you want to create, the Report Expert automatically starts. The report type that is used in this example is the Standard report type. The Standard Report Expert has seven steps to create the report. Each of these steps is shown on a tab in the Create Report Expert dialog box, shown in Figure 16.9.
FIG. 16.9
The Create Report Expert guides you through the steps of creating a report.
The first step in creating the report is to select the type of data source to use for the report. Crystal Reports supports the following two data sources for the reports (and a third one, also listed here, if you buy the full retail product):
NOTE: The Data Dictionary feature of Crystal Reports is only available by purchasing the professional edition of Crystal Reports.
To select the data source for the demo report, select the Data File button to display the Choose Database File dialog box, as shown in Figure 16.10.
FIG. 16.10
Choose the database with which to work.
For this report, you will be using the Northwind database (.../Devstudio/vb/nwind.mdb) that comes with Visual Basic 5. After the database file is selected, all of the tables and queries in the database are added to the report. If you see the expert in the background, you will see the tables and queries listed. Click the Done button to return to the expert. You are then automatically taken to the dialog box's second tab.
The second tab of the Expert enables you to review and modify the relation information that is already present in the database. Because the Northwind database already has the relationship information defined, you will see the relationships, as shown in Figure 16.11. When this tab is first displayed, the message about Tables is to allow you to add additional tables to the relationship diagram.
Click the OK button on the message displayed and then click the Next button to continue to the third tab.
NOTE: Some databases, such as Access, enable you to define the table relationships within the database itself.
FIG. 16.11
Crystal Reports accesses the relationships from the database.
The next step in the Create Report Expert is to select the individual fields that you want to have appear on the report. This tab of the dialog box is shown in Figure 16.12.
FIG. 16.12
Select the data fields to include from a list of available fields.
Fields are added by double-clicking the individual fields in the Database Fields list or by highlighting the fields that you want to select and then clicking the Add button. The Fields tab of the dialog box also enables you to set the order of the fields in the report. For each field in the Report Fields list, you can also specify a custom column heading in the text box below the list. Select the fields listed in Table 16.3 and add them to the fields list.
Table | Field |
Customers | CustomerID CompanyName ContactName Address City Region PostalCode |
Orders | OrderID OrderDate |
Order Details | ProductID Quantity UnitPrice Discount |
Products | ProductName |
There also may be times when you will want to combine fields or use them in a calculation. The Fields page also has a button that enables you to enter formulas for calculated fields. The report that you are creating needs two calculated fields, so click the Formula button to add the fields included in Table 16.4.
Field Name | Calculation |
Extended Price | Order Details.Quantity * Order Details.UnitPrice |
Discounted Price | Extended Price - Extended Price * Order Details.Discount |
Notice that each calculated field name has a "@" added to the beginning of the name. This denotes that the field is a calculated field. You will also see that they are listed in a new section called Report Fields in the Database Fields window. They still need to be added to the report like any other field.
NOTE: Each calculated field must be added to the list separately.
After you have specified all of the fields required for the report, you can preview the report or continue to set other options. Before you preview the report, the other available options on the other tabs of the Create Report Expert will be explained. These options are summarized in the following list:
Tab | Purpose |
Sort | Specifies an order by which the lines of the report are sorted. |
Total | Determines whether subtotals and grand totals of numerical data are included on the report. |
Select | Specifies a selection criteria for the report. Choose it to produce only a subset of the recordset that is the source of the data. Use of a subset greatly increases the speed of your report. |
Style | Specifies a specific style of the report, such as placing lines after each record, or making the entire report appear as a table; also enables you to add pictures to the report. |
Next, you will add a few sorts and totals to the sample report. To specify the sorts needed, click the Next button.
Using the Sort Tab The Sort tab enables you to select the fields to sort, the order in which they are sorted, and in which direction they are sorted (see Figure 16.13).
FIG. 16.13
Set the sort fields and their properties.
Select the following fields to sort in the listed order:
Click Next to add the subtotals to the report.
Defining Totals in the Report The Total tab displays a tab for every field that you select to be sorted (see Figure 16.14). Create the following totals, listed in Table 16.5, by removing any unwanted fields from the Total Fields list.
Tab Name | Field to Sum |
Customers.CustomerID | @Discounted Price |
Orders.OrderID | Order Details.Quantity @Discounted Price |
Order Details.ProductID | None |
Click the Next button twice to go to the Style tab.
FIG. 16.14
Specify the totals for a report.
Narrowing Down Your Report Criteria
Although the Select tab is not used in this example, there are a couple of significant things to mention about this tab for the time when you do need it.The Select tab enables to you to easily set up conditions that Crystal Reports will use when retrieving data from the database. By using this tab, you can build standard conditions. However, if you need more complex conditions, you should create your own selection formulas.
Using the Style Tab The Style tab enables you to select a style for the report and enter a Title that is displayed at the top of the report. Select the Standard style and then enter Demo Customer Invoice as the Title (see Figure 16.15). You are now finished with the initial setup of the report.
After you have selected the report fields and any other information needed for the report, you can preview the report to see how the actual data will look. Click the Preview Report button on the Expert to display the initial report, which is shown in Figure 16.16.
As you can see from this preview, the report needs some formatting to make it look more professional. Some of the numeric fields need to be formatted and the following items need to be added to the report:
FIG. 16.15
Select the style for the report from a list of available templates.
FIG. 16.16
Display a preview of the report you just created.
These fields can be added to the report by using the S_pecial Fields option on the Insert menu. Right-clicking any field or label gives you a pop-up menu that lists all of the editing options available for the selected object. In addition to these changes, you probably want to move the fields around to arrange them in the style of an invoice. After all of these changes are completed, the report should resemble the one shown in Figure 16.17.
After you have completed the design of the report, you need to save the report file so that you can access the report from your Visual Basic program. Saving the report can be done either by clicking the Save button on the toolbar or by choosing File, Save or Save As. Any of these actions display the Save dialog box so that you can enter a filename for the report, as shown in Figure 16.17.
FIG. 16.17
Here is the completed Customer Invoice report, which can be saved by using
a long filename instead of an old eight-character name.
NOTE: Because Crystal Reports is Windows 95/Windows NT 4.0-compliant, you can use a long file name to name the report.
Although you can run the reports you create from within Crystal Reports, you'll probably want to access them from within your Visual Basic program. You can do so by using the Crystal Reports custom control, which is one of the controls installed with Crystal Reports. The Crystal Reports control provides a link between the Crystal Reports engine and the reports you create with the report designer.
The first step in accessing Crystal Reports is to make the control available to your program. If Visual Basic is not started, start it and then create a new project. Now you need to add the Crystal Reports control to your Visual Basic Toolbox. To do so, choose Components from the Project menu of Visual Basic. The Components dialog box appears, which is shown in Figure 16.18. In this dialog box, you can specify which custom controls are available in your project.
FIG. 16.18
You must make the Crystal Reports control available to your project by selecting
it in the Components dialog box.
TIP: You also can access the Components dialog box by clicking the right mouse button on the Toolbox and then choosing Components from the pop-up menu, or by pressing Ctrl+T.
TROUBLESHOOTING: I Can't find the Crystal Reports custom control in the Components dialog box. What happened to it? If you elected to perform a custom setup of Visual Basic, you may have left Crystal Reports out of the setup process. You need to rerun the Visual Basic setup program and install Crystal Reports.
After the Crystal Reports control is available in your toolbox, you can use it in your program. To gain access to the control, simply select it from the toolbox and place it on the form where you will access the reports from. Because the Crystal Reports control is not visible at runtime, it appears only as an icon on your form. After the control is on the form, you can set the properties that access the reports you create with the report designer.
Specifying the Report to Run The key property that you need to specify is the ReportFileName property. This property specifies the actual report that you will run from your program. You can easily set this property by clicking the ellipsis button that appears to the right of the property in the Properties window. The Crystal Reports Property Pages, shown in Figure 16.19, then appears. From this page, you can specify the name of the report and whether the report should go to the printer, a preview window, a file, or to a message through the MAPI interface.
FIG. 16.19
Select the report name and destination from the Property Pages of Crystal
Reports.
On the General tab of the Property Pages, either type the name of the report into the field for the ReportFileName property, or select the report from a file dialog box by clicking the ellipsis button on the Property Pages.
Selecting the ReportFileName is the minimum setup for Crystal Reports. The other tabs on the properties page enable you to customize the report to specific requirements. These tabs are:
Print Window | Sets the properties of the Print display window |
Printer | Sets the number of copies to print |
Print to File | Sets the file name and file type when printing a report to the printer |
Database | Enables you to enter the UserId and connection info for a database |
Selection | Enables entry of the SelectionFormula and GroupSelectionFormula |
Data-Bound Report | Sets Heading for a database report |
At this point, you can write the line of code necessary to run the report and test it by running your program.
Setting Optional Properties Although only the ReportFileName is required for a report, you may want to use several optional properties with the report. The first of these properties is the SelectionFormula property. This property enables you to limit the number of records that will be included in the report. The SelectionFormula property is similar to the Where clause of an SQL statement but it uses its own particular format to enter the information. To specify the SelectionFormula, you must specify the name of the recordset and the field to be compared. You must express this recordset/field combination in dot notation and enclose it in curly brackets. After specifying the recordset and field, you must specify the comparison operator and the value to be compared. The final result is an expression like the following:
{MemberShipList.OrgCode}=1
You also can use multiple expressions by including the And or Or operators.
CAUTION: If you enter a SelectionFormula when you're designing your report, any formula you enter in the SelectionFormula property of the Crystal Reports control provides an additional filter on the records.
Another optional property is the CopiesToPrinter property. This property enables you to print multiple copies of your report easily at one time. You can set this property to any integer value.
After you add the Crystal Reports control to your form and set its properties, you are ready to start printing, right? Well, not quite. You still have to tell Crystal Reports when to print the report. You do this by writing a line of code to initiate the report. The line of code shown next sets the Action property of the Crystal Reports control to 1.
rptMember.action = 1
In the new project, add a command button to the form and place the preceding line of code in its Click event routine. Run the program and click the command button to run the report.
The report is then displayed using the report file and other properties that you set, as shown in Figure 16.20.
FIG. 16.20
Displaying the desired report to the screen from within a Visual Basic program.
This was just a simple example of displaying a report from a program. There are many more options, properties, and methods that you can access that enable you to customize what the report looks like when it is displayed.
What you just tried is the easiest way of accessing a report from Crystal Reports. However, you probably will have a number of reports that you need to display or print, so you need to be able to change the Crystal Reports control's properties at runtime; otherwise, you would need a separate report control for each of your reports. All the major properties of the Crystal Reports control, such as ReportFileName and SelectionFormula, are available at run time. You set these properties, like any other properties, to new values by using an assignment statement. The following code sets up the Crystal Reports control for a new report and specifies a selection criteria based on user input:
rptMember.ReportFileName = "CntyMbr.rpt" rptMember.SelectionFormula = "{MemberShipList.OrgCode}=" & OrgID rptMember.action = 1
The other property that you may need to set at runtime is the DataFiles property. This property is not available at design time. The property specifies the name of the database file to be used with the report. Now you may be thinking, "I told the report what file to use when I created it." That is true, but when you created the report, the database file was stored with a path based on your directory structure. Your path may not be the same as the directory structure of your users.
The DataFiles property is actually an array with the first element number of 0. If you're using more than one database in your report, you need to set the value of each DataFiles array element. For most of your reports, however, you will be using only a single database. The following line of code shows you how to set the value of the DataFiles property for the database. This line assumes that the database file is in the same folder as your application:
rptMember.DataFiles(0) = App.Path & "\Members.mdb"
What you have seen so far is the simple way of using Crystal Reports. In complex Visual Basic applications, you really need to give the user a good interface to select and modify the report options when running and printing a report. In an application that I developed, a Visual Basic form was used with several controls in conjunction with an input text file to create the report manager shown in Figure 16.21.
FIG. 16.21
Here is the completed report manager from a Visual Basic application.
This report manager displays report descriptions in a TreeView control with headings for each different group of reports that are added to the list. It also makes use of the CommonDialog control to display the Printer dialog box. This allows the users to set printer options. In this program, however, the only option they can set is the number of copies to print. The following section shows you how to build this interface for your own application.
NOTE: This form was created by using the TreeView control. For more information on how to add subfolders and items to this control, see Chapter 10, "Using the Windows Common Controls."
See "Using the TreeView Control," Chapter 10
The form shown in in Figure 16.21 has several different controls on it to produce the finished results. Create a new form in your Visual Basic application and then add the controls in Table 16.6 to the form.
Button |
Control | Name | Caption |
|
Frame | Frame1 | Display |
|
OptionButton | Printer | Printer |
|
OptionButton | Window | Screen |
|
CommandButton | But_Print | |
|
CommandButton | But_Setup | Print >> |
|
CommandButton | But_Close | &Close |
|
TreeView | Treeview1 | <none> |
|
CrystalReport | Report1 | <none> |
|
CommonDialog | CommonDialog1 | <none> |
|
ImageList | ImageList1 | <none> |
After these controls are on the form, you can add the code to perform the required processing.
The TreeView control is used to display the reports in different groups. During the load routine for the form, you must initialize the TreeView nodes so that they are displayed properly. The first section of code that must be added is the declaration section for the variables that will be used in the code. The variable declarations for this demo form are shown in Listing 16.1.
Option Explicit Dim i As Integer Dim reports(15) As String Dim MyNode As Node Dim Total_Reports As Integer
The next section of code that has to be added is the code that initializes the form when it is loaded. Listing 16.2 shows the Form_Load routine for this example.
Private Sub Form_Load() Window.Value = True But_print.Caption = "Display" Report1.WindowTitle = "Demo Reporting" Report1.WindowMinButton = False `Initialize the TreeView Structure TreeView1.Nodes.Clear `Set the Report List Header Set MyNode = TreeView1.Nodes.Add(, , _ "T", "Demo Crystal Reports Manager", 2) MyNode.EnsureVisible Total_Reports = 1 `Loop until there are no more reports to add `Use an input file to hold the report names and levels For i = 2 To Total_Reports + 1 `Set the Report Names Set MyNode = TreeView1.Nodes.Add("T", _ tvwChild, "R1", "Crystal Report Demo Report", 1) reports(i) = "CRWDEMO" Next i End Sub
This routine is actually separated into two sections of code. The first section initializes several properties of the Crystal Reports control and also sets the default display selection. In this first section, you see a line of code that sets the caption for the But_Print command button. This is done because when the user changes the display type by using the Option buttons, the caption of the But_Print button is changed. The code shown in Listing 16.3 is used to perform this action when the user clicks either option button.
Private Sub printer_Click() But_print.Caption = "Print" End Sub Private Sub Window_Click() But_print.Caption = "Display" End Sub
The second section of code in the Form_Load routine is used to initialize the TreeView node structure. For this example, only the top header and one child-level node are added. However, for an actual application, you want to have a table or file that contains the following information:
Report Description | Description that the user sees in the TreeView list. |
Report File Name | If this record is for an actual report, then the Filename goes here. If it is not a report, then put spaces here. |
Relative | The key value of the parent node for this record. |
Relationship | The relative placement of the node in the list. |
Key | The key value that is unique for this record. It will be used if this record is a folder name. |
If you compare the information in this table to the syntax of the Add method for a TreeView node, you will see a very similar structure. This will help you dynamically build the TreeView list whenever the application is started. The next order of business is to finish the command button code. Listing 16.4 displays the code for the Close and Printer Setup buttons. The Close button is self-explanatory. However, the Printer Setup button calls the CommonDialog control to display the Printer Setup common dialog for the user to modify. You can see from the code that the only property that can be changed is the number of copies to print. The Crystal Reports property CopiesToPrint is set to this value.
Private Sub But_setup_Click() CommonDialog1.Action = 5 Report1.CopiesToPrinter = CommonDialog1.Copies End Sub Private Sub but_close_Click() Unload Me End Sub
The final button on the form is the one that actually pulls everything together. This is where the selection from the TreeView control is used to set the Crystal Reports control properties for the report file name, whether the user wants to display the report or send it to the printer. Listing 16.5 shows the code for the But_Print_Click routine.
Private Sub but_print_Click() If reports(TreeView1.SelectedItem.Index) = "NA" _ Or TreeView1.SelectedItem.Index = 1 Then MsgBox "You must first select a Report.", _ vbCritical, "Demo Message" Exit Sub End If On Error GoTo errorhandle Report1.SelectionFormula = "" Report1.ReportFileName = _ "C:\temp\" & reports(TreeView1.SelectedItem.Index) & ".rpt" For i = 0 To 15 Report1.DataFiles(i) = "" ext i Report1.DataFiles(0) = "C:\program files\devstudio\vb\nwind.mdb" Report1.WindowTitle = "Demo Reporting" Report1.WindowMinButton = False If Window.Value Then Report1.Destination = 0 Else Report1.Destination = 1 End If Report1.Action = 1 Exit Sub errorhandle: MsgBox "An error has occurred during Reporting with Error Number " _ & Err & Chr(13) & Error$, vbInformation, "Demo Message" Resume Next End Sub
If you want the user to be able to select only certain records to print, give them a form that allows them to enter the selection criteria, and then set the SelectionFormula property of the Crystal Reports control.
Run the program and click the command button that displays the report manager. Then select the report that is in the list and click the Display button to see the final report. Next, select the Printer option button and you will see that the Display button has changed to the Printer button. Click the Printer button to send the report to the printer.
In this chapter, you saw how to use Crystal Reports to create professional looking reports with very little effort. In addition, you added the Crystal Reports Custom Control to a Visual Basic application and called the Crystal Reports print engine to run, and then display, a report in a window. Finally, you saw how to build a Report manager to allow the user to select a report to run and then print it. During the process, you have used several other features and controls of Visual Basic; the following chapters provide more information on these.
© Copyright, Macmillan Computer Publishing. All rights reserved.