With Visual Basic 6 there are two methods for developing reports. Reports can be created within the Visual Basic development environment by using Microsoft Data Reports, or by using the version of Crystal Reports included with Visual Basic by accessing Crystal Reports' .RPT file via the Crystal's ActiveX control. (Of course, you also can use another version of Crystal Reports or another third-party reporting program; however, Data Reports and Crystal Reports 4.6.1 both come with Visual Basic 6.)
Microsoft Data Reports
With the new Microsoft Data Reports, a developer can add a report directly to a Visual Basic project. The report can be designed, saved, and compiled with a project just as a form or class would be. This feature integrates well with the Visual Basic IDE, and you can create reports natively and intuitively as you would create any other form.
There are two major differences between creating reports with Microsoft Data Reports and creating other forms within the project:
- The Data Report dimension is based on the printed page, whereas a form is based on twips.
- The Data Report does not use the intrinsic controls or ActiveX controls that regular Visual Basic forms use; they have their own report intrinsic controls.
With Microsoft Data Reports, a report can be displayed to the screen in the form of a Print Preview window. From this window the user can print the report to a printer or export the report to a file (including HTML). The report can be printed to a printer or exported to a file without any user intervention by using Visual Basic code.
Crystal Reports
Crystal Reports is still included with Visual Basic 6. It includes the Crystal Reports Designer, ActiveX control, and necessary runtimes. Visual Basic 6 is backward-compatible with projects created with Crystal Reports with earlier versions of Visual Basic.
Any Crystal Reports report must be created using the report designer. Although certain report elements can be changed at runtime through the Crystal Reports OLE custom control, the control does not have the ability to create a report from scratch.
Running and Configuring Crystal Reports Designer
The Crystal Reports Designer works as a separate process from Visual Basic and keeps the report definition in a report definition file with a .rpt file extension. You should try to save your report definition files in your Visual Basic project directory to make project management and program distribution easier.
If you installed Visual Basic with the defaults, you'll find a Crystal Reports icon in the Microsoft Visual Basic 6.0 program group. You can also start Crystal Reports by selecting Add-Ins|Report Designer from the Visual Basic main menu.
All the How-To's in this chapter assume that your Crystal Reports Designer program is set up the way ours is. Select File|Options from the Crystal Reports main menu. On the Layout tab, check the Show Field Names option. On the New Report tab (see Figure 9.1), check the Use Report Gallery for new reports option. You can also enter a report directory as the default location for report definition files.
Figure 9.1. The Crystal Reports Designer File Options dialog box.
Printing Reports
You have two printing approaches with Crystal Reports:
- Using the Crystal Reports report designer. This program must be used to design all reports, and it can print them as well. This means you don't need to run a Visual Basic application to print reports on data maintained by your application. The program, however, can't be distributed to users of your application.
- Using Crystal Reports OLE custom control (OCX). This OCX is included in the package and can be dropped into a Visual Basic form to control report printing. The control gives your application access to most of the more useful features of the Crystal Reports program. With this control, you can have the report displayed in a Print Preview window, printed directly to a printer, or exported to a file.
Which Report Engine Should I Use?
With both Microsoft Data Reports and Crystal Reports included with Visual Basic 6, you have alternatives not previously available with Visual Basic. The tool you use will depend on your particular situation and personal preferences. You need to consider the reporting engines' different approaches to designing reports and how Visual Basic interfaces with the respective reporting engines.
When to Use Microsoft Data Reports
Data Reports integrates very well into the Visual Basic environment. The reports are generated directly in the Visual Basic IDE. There are no separate report files, and the report definitions are stored in an ActiveX designer form with the Visual Basic project. This means when a project is compiled, the report definition is compiled within the executable file (EXE). The report retrieves the data to be used from a Data Connection within a Data Environment. This enables the report to retrieve data from this powerful new feature in Visual Basic 6.
Microsoft Data Reports integrates well with Visual Basic by using the new powerful Data Environment and enabling the programmer to create reports with similar methods in which forms are created. Data Reports is great for basic reports; however, it does not have the options or advanced report features of the mature Crystal Reports and is not backward compatible with Crystal Report .RPT files.
When to Use Crystal Reports
Crystal Reports takes a totally different approach to creating reports to use with Visual Basic. A separate application creates the report definitions, and these reports are stored in a separate .RPT file that must be distributed with the EXE file. Also, additional DLLs and an OCX control must be included for the Visual Basic application to use a Crystal Reports report. The distribution requirements are therefore much heavier than those for Microsoft Data Reports. Furthermore, a Crystal Reports report uses ODBC to connect to a data source, not to the new powerful Data Environment.
Crystal Reports is much more mature that Microsoft Data Reports. Many applications have already been written using Crystal Reports as their reporting engine. Also, Crystal Reports has a wider feature set than Data Reports. It can generate cross-tab reports and sub reports, and it has much more powerful formula and many number crunching features. Crystal Reports also has an upgrade path that provides new features and more power.
Crystal is a mature and proven product. It might be heavier than Data Reports, but it offers a richer feature set and has upgrade options for scalability.
CRYSTAL REPORTS UPGRADE OPTIONS
Crystal Reports Professional 6
This version of Crystal Reports includes an updated feature set to Crystal 4.6.1, which is included with Visual Basic. An updated report designer and an ISAPI module is included, which allows reports to be placed on a IIS Web server. Java, ActiveX, and pure HTML viewers can be used to display a report on the Web server. This version of Crystal Reports enables you to export a report in various formats including HTML, MS-Excel, MS-Word, and Lotus 1-2-3.
Crystal Info
Crystal Info is a server-based solution that enables multiple users to view, schedule, and manipulate shared reports. It can be used as a central storage for reports, for multiple users to view. It can be used to schedule when complex and time consuming reports will be run so data can be viewed without having to run the report each time. What SQL Server is to Microsoft Access, Crystal Info is to Crystal Reports.
Crystal Report Designer
This is an ActiveX designer that works in the same vein as Microsoft Data Reports. It gives you 100% functionality of Crystal Reports 6 with the Visual Basic IDE. Take the way Microsoft Data Reports integrates into Visual Basic and add the power of Crystal Reports 6. WOW! Prior to the release of Visual Basic 6, the only version of this tool available was for Visual Basic 5, but keep an eye on the Seagate Software Web site (www.seagatesoftware.
com) for a Visual Basic 6 version.
Sample Databases in the How-To's
The How-To's in this chapter use the Microsoft Access .MDB file BIBLIO.MDB that was shipped with Visual Basic. If you selected the default program locations when you installed Visual Basic, the Visual Basic files are located in the \Program Files\Visual Basic directory. If these files are not located in the default directories, you'll need to tell Crystal where to find them.
Table 9.1 lists the tables in the BIBLIO.MDB database. You might want to add one index (duplicates are OK) on the Au_ID field in the Title Author table to speed reporting. Use either Microsoft Access or the VisData sample application that is included with Visual Basic to add the index.
Table 9.1. Tables from BIBLIO.MDB.
| TABLE NAME | Description |
| Authors | Book author names |
| Publishers | Names, phones, and other information about each publisher |
| Title Author | Records linking the Author and Titles tables |
| Titles | Books published by each company |
You can find the next set of tables, shown in Table 9.2, in the file CRYSTAL.MDB, which is included on the CD-ROM. These files make up a basic order-entry system.
Table 9.2. Tables from CRYSTAL.MDB.
| TABLE NAME | Description |
| Company | Company name and information |
| Header | Order invoice header |
| Detail | Order detail records |
The last database is Mailing List, in the file MAIL.MDB. Create this new table using the fields shown in Table 9.3. It is easiest if you place the file in the same directory as the .RPT file that uses it.
Table 9.3. Mailing List table in MAIL.MDB.
| FIELD NAME | Data Type | Size |
| Contact | Text | 30 |
| Addr1 | Text | 40 |
| City | Text | 30 |
| State | Text | 20 |
| Zip | Text | 10 |
| Week Day | Text | 10 |
Enter some sample data in the Mailing List table. Table 9.4 lists a few sample lines of the table in MAIL.MDB on the CD-ROM enclosed with this book. Enter various Contact names and Week Days. Include a number of records from each City, State, and Zip. There are more than 600 records in the MAIL.MDB on the CD-ROM.
Table 9.4. Sample data in Mailing List table in MAIL.MDB.
| CONTACT | Addr1 | City | State | Zip | Week Day |
| Resident | 4 Goodyear Street | Boulder | Colorado | 80302-0302 | Monday |
| Occupant | 2291 Arapahoe | Irvine | California | 92711-2002 | Monday |
| Resident | 8 Hazelnut | Irvine | California | 92711-3810 | Monday |
| Occupant | 2 Orion | Aliso Viejo | California | 92656-4200 | Sunday |
| Medical Practitioner | 1 Jenner | Tacoma | Washington | 98402-8402 | Thursday |
| Medical Practitioner | Civic Center | Anaheim | California | 92805-2805 | Tuesday |
| Occupant | 2 Park Plaza | Irvine | California | 92714-271Thursday |
9.1 Create a report using Microsoft Data Report Designer
Using the Data Report Designer, you will lay out a report and place report intrinsic controls on the form to create a simple Data Report. This How-To will get your feet wet with the new Data Report Designer and how it relates to the Visual Basic IDE.
9.2 Add Calculated Fields to a Microsoft Data Report
Using the rptFunction report control you will add calculations to a Data Report. You will become familiar with various functions the control can offer when creating reports.
9.3 Select if the Microsoft Data Report will be displayed, printed, or export using Visual Basic code
Using Microsoft Data Report and some Visual Basic coding, this How-To will demonstrate how to display a report to the screen, print the report directly to the printer, or export the report to a file. The format of a file when exported can be text or HTML. The ability of exporting a report to HTML opens up multiple possibilities to Web enable your application.
9.Have a Report Displayed Based on Criteria I Choose Using a Microsoft Data Report
Have a Data Report display data based on a parameter passed to a SQL-based query contained in a Data Environment object.
9.5 Determine Which Records Will Be Printed Using Crystal Reports
Using the Crystal Reports custom control, you can specify at runtime the records to be printed by sending a Crystal Reports formula to the print engine. In this How-To, you'll create a report using three tables linked together and a simple Visual Basic program to show how you can control the records printed at runtime.
9.6 Create Subtotals and Other Calculated Fields Using Crystal Reports
Crystal Reports has a rich variety of built-in capabilities for creating very complex reports. This How-To describes how to create a bulk mail report based on an address file, sorting, grouping, and performing calculations needed for completion of a mailing's paperwork. You certainly can't get too much more complex than a system created over 200 years by the federal bureaucracy!
9.7 Control the Order in Which Records Will Be Printed Using Crystal Reports
Although the Crystal Reports design program provides a very flexible report design and creation environment, you can change the record sort order from a Visual Basic application, giving you essentially unlimited flexibility to print different reports, as you'll see in this How-To. You'll also see how to change the group sort order, giving you another level of flexibility using database reports.
9.8 Print Labels Using Crystal Reports
Crystal Reports makes it easy to produce almost any type of label using a database. In this How-To, you'll create mailing labels complete with attractive graphics and a return address.
9.9 Create and Print Form Letters with Crystal Reports
By using formulas and the flexible formatting in Crystal Reports, you can use your data to produce form letters. But how do you print different page headers and footers? And how do you customize the text for each recipient in your mailing list? This How-To gives you all the information you need to use Crystal Reports for form letters.
9.10 Print Field Data Without Extra Spaces Between the Fields with Crystal Reports
Although it is not as complete a set of tools as Visual Basic, Crystal Reports does have a number of useful string manipulation and conversion functions and operators. In this How-To, you'll design a customer directory showing names, addresses, and the page number as a single formula field, giving the report a more natural and finished look.
9.11 Prevent Blank Lines from Being Printed when a Field Contains No Data Using Crystal Reports
Crystal Reports provides two options that conserve space when reports are printed: the Suppress Blank Lines property for report sections and Print on Multiple Lines property for text boxes. Both options are put to good use in this How-To, creating a report from frequently incomplete data.
9.12 Create Cross-Tab Reports with Crystal Reports
Using Crystal Reports to produce cross-tab reports is very easy when you use the Cross-Tab layout window. In fact, the hardest part of creating the report is developing a clear picture of how to analyze the data, but Crystal Reports makes it easy to try different options until the report gives the information needed. This How-To demonstrates how to create a summary of customers by city and day of the week that they receive service.
9.13 Generate Reports Using User-Entered Variables with Crystal Reports
Many of the design elements of a Crystal report can be changed on-the-fly in a Visual Basic application. Although there aren't enough control properties to completely change an existing report or create a new report, there are enough changeable properties available that a Visual Basic application can create entirely different reports using the same data. This How-To creates a Visual Basic application and a Crystal report that allows records to be printed in different orders while filtering the records and customizing the report heading and page numbering.
9.1 How Do I...
Create a report using Microsoft Data Report Designer?
Problem
I need to create a report using data from a database that can be displayed to the user, easily printed or exported to HTML.
Technique
Using Visual Basic and the included Data Report Designer, a report can be created to represent the data as desired. Using the BIBLIO.MDB database included with Visual Basic, you will create a report that displays a list of authors. The preview window used to display the report will enable the user to print and export the report directly from the preview.
Steps
Start Visual Basic, load then run the AuthorsDR.vbp project. Figure 9.2 displays how the report will look. The report can be printed by clicking the printer icon, or can be exported to text or HTML by clicking the book icon.
Figure 9.2. AuthorsDR form shown at runtime.
- 1. Start Visual Basic and select a new Standard EXE project. Select Add Data Environment from the Project menu. The window in Figure 9.3 will appear. Right click on the Connection1 item in the Data View window. Select the Properties item from the popup menu. The following screen is used to set the database and connection type. This How-To will use the BIBLIO.MDB database; therefore, select the Microsoft Jet 3.51 OLE DB Provider, then click on the Next button. Enter or select the location of the BIBLIO.MDB database; it should be in the same folder as Visual Basic. Click on the Test button to verify the connection. This handy option enables you to verify the database connection without having to write test codes. Run the project. Click the OK button.
- 2. Right-click on the Commands folder in the Data Environment window. Select the Add Command item from the popup menu. An item called Command1 will appear under the Commands folder. Right click on it and select the Properties item from the popup menu. The Properties dialog box that appears is used to select a table or query. Click the on Connection drop-down list and select Connection1. From the Database drop down select the Table object. From the Object drop-down list, select the Authors Table. The dialog should look like Figure 9.4 after the items have been chosen.
Figure 9.3. The Data View Window.
Figure 9.4. The Command1 Properties dialog box.
- 3. From the Visual Basic main menu, select the Add Data Report item from the Project menu. Set the properties of the Data Report as in Table 9.5. Then, right-click on the report and select Retrieve Structure from the popup menu. Retrieve Structure will make the sections of the report match the database connection layout.
Table 9.5. Properties for drBasic.dsr AuthorsDR.
OBJECT Property Setting DataReport Name drBasic DataMember Command1 DataSource DataEnviroment1
- 4. The fields to be displayed can be added in two ways. The first method is to draw the fields on the report, then assign the proper DataMember and DataField properties to them. The second method is to open the Data Environment window, and drag and drop the fields directly on the report. After a field is dropped onto the detail section of the report, the field and its caption are displayed on the report. Arrange the items as shown in Figure 9.5.
Figure 9.5. Arranged report objects.
- 5. Set the properties of the objects on the report form as in Table 9.6.
Table 9.6. Objects on drBasic.dsr.
OBJECT Property Setting RptCaption Name lbTitle Font Arial Size 12 Caption "List of Authors" RptCaption Name lbAuthorTitle Font Arial Caption Author: RptCaption Name lbYearTitle Font Arial Caption "Year Born:" RptText Name txtAuthor Font Arial DataField Author DataMember Command1 RptText Name txtYearBorn Font Arial DataField "Year Born" DataMember Command1 DataSource DataEnviroment1
- 6. Remove the form called Form1 that was automatically created when you started Visual Basic. Set the Startup Object to the report form. Run the project.
HOW IT WORKS
First, a Data Environment must be defined. This is the gateway through which the report retrieves its data. Then, the report is assigned to a Data Connection. The structure of the Data Connection is mirrored by the report. Dragging and dropping the fields into the report creates the report layout. By running the Visual Basic project the report and data is displayed. At run-time the user can easily print or export the report.
Comments
Creating a Data report consists of three basic steps. First, a data connection must be created. This is the object the report will use to retrieve the data. With Visual Basic 6 the new Data Environment is very powerful and flexible as it enables the Data Reports to retrieve data from various data types. You are not limited to just Access MDB files, but can get data from any ODBC DataSource or use the built in functionality to connect directly to an Microsoft SQL server or an Oracle database.
The second step is to assign the report to a Data Connector created in the Data Environment and use the "Retrieve Structure" option so the report section matches the data connection.
The third step is to add the report controls to the report. These controls will display the data the report retrieves. These controls can range from displaying the names of the fields to calculated items that summarize data in the report.
9.2 How Do I...
Add Calculated Fields to my Microsoft Data Report?
Problem
I have a report that generates a list of items. I need to have the report count the number of items in the list.
Technique
Using the Data Report designer included with Visual Basic 6, you can create reports that can do various calculations based on the data in the report.
This How-To will demonstrate how to make a group based on the Authors field and add a calculated field that will display the number of books each author has written. This How-To will be a base demonstration how to add calculated fields to a Data Report.
Steps
Open and run DataProject.vbp, which displays the Figure 9.7. This displays each author's list of book and a count of the number of books per author. By using the toolbar at the top of the report you can print or export the report.
- 1. Start Visual Basic and select a new Data Project. This will add the Data Environment and Data Report to the project. Remove the form that was added to the project and set the Data Report as the Startup object.
- 2. From the Project menu select the Add Data Environment. Double-click on the DataEnvironment1 object that was added to the Project window. Right-click on the Connection folder and select the Add Connection item from the popup menu. Right-click on the Connection1 object and select the Properties item from the popup menu. Select the Microsoft Jet 3.51 OLE DB provider. The next dialog box that appears is used to find the BIBLIO.MDB file on your computer. Click the OK button. Right-click on the Command folder and select the Add Command item from the popup menu. Command1 will be added under the Commands folder. Right click on Command1 and select the Properties from the popup menu. Using the Data Object dropdown select the Tables item. Using the Object Name dropdown select the Titles item.
- Steps 1 and 2 were to set up the Data Environment. Please refer to the chapters in this book for a more detail on the Data Environment.
- 3. Under the Grouping tab, check the Group Command Object checkbox. This will enable the items in the tab. Click on the Author field and click the ">" button. This will cause the Data Environment to group the data by Author. Click the OK button.
- 4. Set the DataMember and DataSource of the Data Report. Right-click on the bottom of the report and select the Retrieve Structure item from the popup menu. This will arrange the sections of the Data Report to match the data used.
- 5. Place the controls on the data report form as in Figure 9.6. Set the properties of the controls as in Table 9.7.
Figure 9.6. Objects arranged on the report.
Figure 9.7. The project at runtime.
Table 9.7. Objects and properties for drBasic.dsr.
OBJECT Property Setting DataReport Name DataReport1 DataMember Command1_Grouping RptLabel Name lbTitle Font Arial FontSize 14.25 Caption "Number of Books per Author" RptText Name "txtAuthor" Font Arial FontSize 12 DataField Author DataMember Command1_Grouping RptLabel Name Label2 Caption Title: RptLabel Name Label3of Caption "Year Published:" RptText Name txtTitle DataField Title DataMember Command1 RptText Name txtYearPublished DataField "Year Published" DataMember Command1 RptFunction Name Function1 DataField Title DataMember Command1 FunctionType 4 - rptFunRCnt RptLabel Name lbTotalBooks Caption "Total Books by Author" RptLabel Name Label1 Caption "%p" RptLabel Name lbPageTitle Caption Page RptLabel Name lbof Caption of RptLabel Name Label4 Caption "%P"
- 6. Run the project. It might take a few minutes to retrieve the data from the database. The result should be displayed as in Figure 9.7.
How It Works.
The report retrieves the data from the Data Environment. The Data Environment queries the database and groups the data by author. The rptFunction control that has been placed in the Group footer displays the total number of books per author. The rptFunction control can do various functions as in Table 9.8.
Table 9.8. rptFunction control functions description.
| FUNCTION | Setting | Description |
| Sum | 0 - rptFuncSum | Adds the values of the field |
| Average | 1 - rptFuncAve | Averages the values of the field |
| Minimum | 2 - rptFuncMin | Displays the minimum value of the field |
| Maximum | 3 - rptFuncMax | Displays the maximum value of the field |
| Row Count | 4 - rptFuncRCnt | Displays the number of rows in a section |
| Value Count | 5 - rptFuncVCnt | Displays the number of rows containing non-null values |
| Standard Deviation | 6 - rptFuncSDEV | Displays the standard deviation |
| Standard Error | 7 - rptFuncSERR | Displays the standard error |
The rptFunction can be used to do various calculations based on the type of data in the field. By placing the rptFunction control in either a group or report footer, you can perform calculations on fields contained in the report.
Comments
Grouping is a very powerful function when generating reports. Grouping enables the report to list similar items together in the report.
For Example, the books were not added sequentially in the database, so when retrieving a list of authors and their books from the database all the authors are mixed up in the list. Grouping allows report to present all the books for one author then another. Another example is when creating a report to list the books per publisher. The grouping would be done on publishers not authors.
9.3 How Do I...
Select whether the Microsoft Data Report will be displayed, printed, or exported using Visual Basic code?
Problem
I need to create an application that enables the user to choose the output method for the report. I would like the user to be able to choose whether the report is previewed to the screen, printed to the printer, or exported to a file.
Technique
The data report has various methods defined to output a report. By using these methods, the programmer can determine the output method for the report. This How-To will demonstrate the ways in which the application can enable the user to choose the output method for the report.
The PrintReport method will be used to print the report directly to the Printer. This method can be set to print directly to the printer without any user intervention, or the method can be used to display a dialog box to enable the user to select the print range and the number of copies to be printed.
The ExportReport method will be used to generate a file of the report data. This method can be set to generate the file without any user intervention or a dialog can be displayed so the user can select the file type and the page range.
The Show method will be used to display a print preview window of the report. This method is the same used to display any other Visual Basic form.
Steps
Open and run the OutputType.vbp project. This project enables the user to display, print, or export the report. When printing or exporting a report, the user can select the page range to be used. This is useful in large reports.
- 1. Start Visual Basic and choose a new Data Project. Have the Data Environment point to the BIBLO.MDB database.
- 2. Lay out the form as in Figure 9.8 and set the properties of the objects as in Table 9.9.
Figure 9.8. Arrangement of form frmOutPut.frm.
Table 9.9. rptFunction control functions description.
OBJECT Property Setting Form Name frmOutPut Caption "Data Report Output" Frame Name frExport Caption "Export Options" CheckBox Name chkExport Caption "Show Export Dialog" TextBox Name txtStartPageEx TextBox Name txtPrintToEx ComboBox Name cbExportType Label Name lbExportType Caption "Export Type" Label Name lbStartPageEx Caption "Start Page:" Label Name lbPrintToEx Caption "Print To:" CommonDialog Name CD Frame Name frPrint Caption "Print Options" TextBox Name txtPrintTo TextBox Name txtStartPage CheckBox Name chkShowDialog Caption "Show Print Dialog" Label Name lbPrintTo Caption "Print To:" Label Name lbStart Caption "Start Page:" CommandButton Name cmdExit Caption Exit CommandButton Name cmdExport Caption Export CommandButton Name cmdPrint Caption "Print" CommandButton Name cmdPreview Caption Preview
- 3. Add the following code in the Load event of the Form:
Private Sub Form_Load()
cbExportType.AddItem "1 - HTML"
cbExportType.AddItem "2 - Text"
cbExportType.AddItem "3 - Unicode HTML"
cbExportType.AddItem "4 - Unicode Text"
cbExportType.ListIndex = 0
End Sub
- This procedure will add the export types to the list box so the user can choose the format in which the report will be exported.
- 4. Add the following code to the Click event of the cmdPreview button:
Private Sub cmdPreview_Click()
DataReport1.StartUpPosition = 0
DataReport1.WindowState = 2
DataReport1.Show
End Sub
- This procedure will maximize the size of the report windows to the full screen, then display the report.
- 5. Add the following code to the Click event of the cmdPrint button:
Private Sub cmdPrint_Click()
Dim fReturn As Long
If txtStartPage.Text <> "" Or txtPrintTo.Text <> "" Then
If IsNumeric(txtStartPage.Text) = False Or _
IsNumeric(txtPrintTo.Text) = False Then
MsgBox "The start or end pages to print is invalid.", _
64
Exit Sub
End If
End If
If txtStartPage.Text = "" And txtPrintTo.Text = "" Then
fReturn = DataReport1.PrintReport(chkShowDialog.Value * _
-1, rptRangeAllPages)
Else
fReturn = DataReport1.PrintReport(chkShowDialog.Value * _
-1, _
txtStartPage.Text, txtPrintTo.Text)
End If
If fReturn = 2 Then
MsgBox "Print Job Sent to Printer."
Else
MsgBox "Print Job Cancelled"
End If
End Sub
- This procedure prints the report to the printer. There are three variables that can be passed to the PrintReport method. The first value is a Boolean value that determines if the Print dialog box is displayed. The second determines if all the pages are to be printed. If the second value is not set to rptRangeAllPages, then a third value can be used to determine the range of pages that will be printed.
- 6. Add the following code to the click event of the cmdExport button:
Private Sub cmdExport_Click()
Dim Overwrite As Boolean
If txtStartPageEx.Text <> "" Or txtPrintToEx.Text <> "" Then
If IsNumeric(txtStartPageEx.Text) = False Or IsNumeric(txtPrintToEx.Text) = False Then _
MsgBox "The start or end pages to print is invalid.", _
64
Exit Sub
End If
End If
CD.ShowSave
If CD.FileName <> "" Then
If Dir(CD.FileName) <> "" Then
Ans% = MsgBox("Do you want to overwrite this file ?", _
vbQuestion Or vbYesNo)
If Ans% = 6 Then
Overwrite = True
Else
Overwrite = False
End If
Else
Overwrite = False
End If
If txtStartPageEx.Text = "" And txtPrintToEx.Text = "" _
Then
DataReport1.ExportReport DataReport1.ExportFormats(CLng(Left$(cbExportType.List _
(cbExportType.ListIndex), 1))).Key, CD.FileName, Overwrite, _
chkExport.Value * -1, rptRangeAllPages
Else
DataReport1.ExportReport DataReport1.ExportFormats _
(CLng(Left$(cbExportType.List(cbExportType.ListIndex) _
, 1))).Key, CD.FileName, Overwrite, chkExport.Value * -1, _
txtStartPageEx.Text, txtPrintToEx.Text
End If
End If
End Sub
- The ExportReport method is used to export a report to a file. There are two primary file types, HTML and text. Unicode versions of both types can also be exported. The ExportReport method has four (five if all pages are not selected) variables which can be passed to the method. The first is the format type in which the report will be exported. If this value is left blank, a dialog box will appear, asking the user which format is preferred. There are various ways to pass the export type using code. This example uses the ExportFormats collection of the report. These are predefined types as outlined in Table 9.10. The second value is the filename of the report to be written. In this example, the filename is set from the Common dialog box. The third variable determines if a file should be overwritten. The fourth variable determines if the Export dialog box should be displayed. The next two variables are identical to those used in PrintReport method and determine the page range to be exported.
Table 9.10. Export file types.
EXPORT TYPE Constant Description HTML rptKeyHTML Export in HTML Format HTML Unicode rptKeyUnicodeHTML_UTF8 Export in HTML Unicode Format Text rptKeyText Export in Text Format Text Unicode rptKeyUnicodeText Export in Text Unicode Format
- Note that if the overwrite variable is set to False and if an attempt is made to overwrite a file, the Export dialog box will appear.
- 7. Add the following code to the Click event of the cmdExit button:
Private Sub cmdExit_Click()
End
End Sub
- Clicking this button will cause the application to end.
- 8. Lay out the report as in Figure 9.9. Set the properties as in Table 9.11.
Figure 9.9. The arrangement of the report DataReport1.dsr.
Table 9.11. Object and Properties for DataReport1.
OBJECT Property Setting DataReport Name DataReport1 DataMember Command1_Grouping rptLabel Name lbTitle Font Arial FontSize 14.25 Caption "Number of Books per Author" RptTextbox Name txtAuthor Font Arial FontSize 12 DataField Author DataMember Command1_Grouping RptLabel Name Label2 Caption Title: RptLabel Name Label3 Caption "Year Published:" RptTextbox Name txtTitle DataField Title DataMember Command1 RptTextbox Name txtYearPublished DataField "Year Published" DataMember Command1 RptFunction Name Function1 DataField Title FunctionType 4 - rptFuncCnt DataMember Command1 RptLabel Name lbTotalBooks Caption "Total Books by Author" RptLabel Name Label1 Caption "%p" RptLabel Name lbPageTitle Caption Page RptLabel Name lbof Caption of Rptlabel Name Label4 Caption "%P"
- 9. Run the project. Try the various combinations of report output.
How It Works
The Show method works as it does with a Visual Basic form. It has the report display to the screen. Before displaying the report to the screen settings like StartupPosition and WindowState can be set. The same properties used to display Visual Basic forms can be used to tailor the placement and position of the report on the screen when it is displayed.
The PrintReport is used to print the report directly to the printer. Setting the ShowVariable of the PrintReport determines if the report will be automatically routed to the printer or if a dialog box will appear to ask the user the page range and number of copies to be printed. Setting the range determines the pages that will be printed. If a value for the range is not set, all the pages will be printed.
The ExportReport method is a very powerful function. It can be used to export reports as text, HTML, or a user-defined HTML format. This method has six variables to set to control the way the report will be exported to a file.
ExportReport(ExportFormat, filename, Overwrite, ShowDialog, Range, PageFrom, PageTo)
The ExportFormat variable (ExportFormats collection item) is used to set the type of file that will be exported. This variable is a member of the ExportFormats collection. The ExportFormats is a collection that stores the type of report formats that can be exported. The ListBox was populated with the default items of the ExportFormats collection. The first character of each item in the ListBox is the index of that export type in the ExportFormats collection. The Left$ function is used to grab the number from the item displayed in the ListBox.
The filename variable (a String value) is used to set the name of the file that will be generated. If a full path (such as C:\data\mynewfile.html) is not defined, the current working folder will be used.
The overwrite variable (a Boolean value) is set to determine if a file already exists as defined by the filename variable should be overwritten. If this value is set to False and a file does exist, then the Export dialog box will appear as if the ShowDialog variable was set to True.
The ShowDialog variable (a Boolean value) determines is the Export File dialog box is shown. If this value is True then the filename variable does not need to be set.
The Range variable(s) (a Long value) is set to determine the range of pages that will be exported.
Comments
By using Visual Basic code, a programmer can create an application that generates reports with or without any user intervention. This How-To could be a primer to create an application that prints large reports at night or generates HTML pages to be displayed on the Web.
9.4 How Do I...
Have Visual Basic generate a Microsoft Data Report based on criteria I choose?
Problem
How do I pass parameters to the query in which my report is based?
Technique
Using Visual Basic and the Microsoft Data Reports, build an application that passes a variable as a parameter to a query contained in a Data Environment. The database used will be the BIBLIO.MDB. The recordset will be based on an SQL statement entered into the connection.
Steps
Open and run DataProject.vbp, which displays the screen in Figure 9.13. From this screen the report can be printed to a printer or exported to a file.
Adding and configuring the Data Environment in this How-To is similar to adding one in How-To 9.2. In this How-To, the Data Connection uses a SQL statement instead of retrieving data directly from a table in the BIBLIO database.
- 1. Start Visual Basic and select a new Data Project. This will add a Form, Report, and Data Environment automatically to your project.
- 2. Double-click the DataEnvironment1 item in the Project Explorer window. This causes the Data View window to appear. Right-click on the Connection1 connection and select the Properties item from the popup menu. The dialog box in Figure 9.10 will appear. Select Microsoft Jet 3.51 OLE DB Provider. Click on the Next button and select the BIBLIO.MDB database, then click the OK button. The BIBLIO.MDB database should be in the same folder as Visual Basic.
- 3. Right-click on the Command folder in the Data View window and select the Add Command item from the popup menu. You will see Command1 appear under the Commands folder. Right click on the Command1 command and select Properties from the popup menu. Click the SQL Statement radio button and enter the SQL statement found in Figure 9.10 and as shown below.
SELECT Author, �Year Born� FROM Authors WHERE (�Year Born� = ?)
Figure 9.10. Data View window displaying SQL Statement.
- 4. Click on the Parameters tab and set the values of the parameter as in Figure 9.11. Then click on the OK button.
- The variable will be assigned to this parameter, and the report will be generated based on the selected criteria.
- 5. Lay out the form as shown in Figure 9.12. Set the properties of the objects on the form as in Table 9.12.
Figure 9.11. The Parameter Tab.
Figure 9.12. Form frmDataEnv.frm.
Table 9.12. Objects and Properties of the frmDataEnv.frm Form.
OBJECT Property Setting Form Name frmDataEnv Caption "What Year ?" CommandButton Name cmdRunReport Caption "Run Report" TextBox Name txtYear
- 6. Add the following code in the Click event of the cmdRunReport button.
Private Sub cmdRunReport_Click()
If IsNumeric(txtYear.Text) = True Then
DataEnvironment1.Command1 txtYear.Text
DataReport1.Show
Else
MsgBox "Please enter a valid year, YYYY", 64
End If
End Sub
- This code passes the variable from the text box to the parameter contained in Data Environment. This means the end of messy parsing and building SQL statement in code. The Data Environment is built and the parameters are defined, which makes passing variables to SQL statements as simple as using methods.
How It Works
By having the database connection based on a SQL statement with a parameter, the Visual Basic application can easily pass a value to the parameter to be used to query the database. No longer is there a need to create SQL in Visual Basic code on the fly for passing to a recordset. A database connection can be created, and a value can be passed cleanly to it, on which the recordset can be based.
Comments
A major difference between the Data Report designer and Crystal Reports is the way parameters are passed to the report engine.
Using the Data Reports, all the record selection is done by the Data Connection of the Data Environment. The report requests the data from the Data Connection, and it in turn does all the work of retrieving the proper data; the Data Report does not worry about that at all.
Crystal Reports does this in a completely different manner (as seen in How-To 9.5).
To set the criteria for Crystal Reports formula, follow these steps:
- 1. A formula must be defined when creating the report with the Crystal Reports' designer.
- 2. A Crystal Reports formula (a string) is set to the Selection Formula property of Crystal OLE control.
9.5 How do I...
Determine which records will be printed using Crystal Reports?
Problem
The recordset I need to print changes each time a report is run. How can I let the application user specify at runtime which records to print?
Technique
Many of the parameters used to print a Crystal report through a Visual Basic application can be set using the Crystal Reports custom control. In this How-To, you'll create a simple report of authors and the computer books they've written. Because our BIBLIO.MDB file contains several of the authors' birth years, you can write a Visual Basic program that enables users to set a range of birth years to be printed, set a minimum or a maximum birth year, or set no limits at all, printing all the authors.
Steps
Load and run the Visual Basic application Authors.vbp. The form shown in Figure 9.13 appears. Enter a starting or an ending year, or both, and click the Run Report button to print the report to a preview window. The BIBLIO.MDB contains more than 16,000 authors, so job this might take a little while.
Figure 9.13. The Author Birth Range selection form.
Start by creating a simple report that can be modified through Visual Basic during printing. Start the Crystal Reports program.
- 1. Click the New Report toolbar button or select File|New from the main menu. The New Report Gallery appears, as shown in Figure 9.14. Click the "Standard" option button.
Figure 9.14. The New Report Gallery.
- 2. When the Create Report Expert appears, click the Data File button, and use the common dialog to select the location of your Biblio.MDB file.
- 3. Click the Next button to proceed to tab 2: Links. Notice that Crystal has automatically created a set of table Smart Links based on fields with the same names in different tables (see Figure 9.15) .
Figure 9.15. Automatic table links defined by Crystal Reports.
- 4. Click the Next button to proceed to tab 3: Fields. Add the fields listed in Table 9.13 to your report by double-clicking on the field name or selecting the field name and clicking Add.
Table 9.13. Computer author report tables and fields.
TABLE Field Authors Author Authors Year Born Titles Title Titles Year Published
- 5. On tab 4: Sort, add the Authors.Author field as a grouping field, and specify ascending order.
- 6. On tab 5: Total, remove Authors.Year Born and Titles.Year Published from the Total Fields box. Add the Titles.Title field and specify count as the function in the pull-down list, as shown in Figure 9.16. Remove the checkmark next to Add Grand Totals.
- 7. Ignore tab 6: Select, and proceed to tab 7: Style. Enter the text A Time for Computer Authors as the report title.
- 8. Click Preview Sample to view the results. Enter First 500 for the number of records to view. Experiment with the Report Zoom button (three different-sized squares), page navigation (upper-right), and scrollbars. Hint: Crystal's button hints float in the status bar on the bottom right of the screen.
Figure 9.16. Author report total fields.
- 9. Click on the Design tab to return to design mode. Your report design should look like Figure 9.17. Move the Year Born data field from the Details band to the Group #1 Header band. Delete the Author field from the Details band.
Figure 9.17. Author report design view.
- 10. Modify the number formats for Year Born and Year Published by right-clicking each field and choosing Change Formats. The dialog box shown in Figure 9.18 appears. Uncheck the Thousands Separator box.
Figure 9.18. The Crystal Format Number dialog box.
- 11. Save the report as Authors.rpt for use from the Author Birth Range form.
- 12. Create a new Standard EXE Visual Basic project in your work area. Save the default form as Authors.frm and the project as Authors.vbp.
- 13. From the Project Components menu, select Crystal Report Control and Microsoft Common Dialog Control 6.0, and click OK.
- 14. Place controls on the form as shown previously in Figure 9.13, setting the properties as shown in Table 9.14. Note that the common dialog and Crystal Report controls are invisible at runtime, so place them anywhere that is convenient.
Table 9.14. Objects and properties for Authors.frm.
OBJECT Property Setting Form Name frmAuthors Caption "Author Birth Range" TextBox Name txtEnd TextBox Name txtStart CommandButton Name cmdQuit Caption "Quit" CommandButton Name cmdReport Caption "Run Report" CommonDialog Name cdOpenReport CrystalReport Name crptAuthors Label Name Label2 Caption "End Year:" Label Name Label1 Caption "Start Year:"
- 15. Select the Crystal Report control. Invoke the custom property pages for the control by clicking the Custom property in the property box and then the ellipsis button (...). The Property Pages dialog box shown in Figure 9.19 appears.
Figure 9.19. Crystal Report control property pages.
- 16. Be sure that the Crystal Report General properties are set as shown in Table 9.15. The ReportFileName text box is left blank because that value will be set using the common dialog control.
Table 9.15. Custom properties.
PROPERTY Setting ReportFileName "" Destination To Window
- 17. Add the following code to the form's Load event procedure. This code simply moves the form to the lower-right portion of the screen, out of the way of the report window when it appears.
Private Sub Form_Load()
`Move the form to the lower-right of screen
Me.Move Screen.Width - 1.1 * Me.Width, _
Screen.Height - 1.25 * Me.Height
End Sub
- 18. Insert the following code in the Click event procedure of the cmdReport command button. This code performs two main functions: validating the data that has been entered in the two text boxes and setting the properties of the Crystal Report control.
- Validation is performed for simple errors in the start and end years--checking that the start year is before or equal to the end year and that the years are in a reasonable range for computer book authors (being conservative: 1850 to the present!).
Private Sub cmdReport_Click()
Dim strSelectCritera As String
Dim strDbName As String
Static strSaveDir As String
`Check for errors in the input year boxes
If (Val(txtStart.Text) > Val(txtEnd.Text)) And _
Val(txtEnd.Text) Then
MsgBox "Start year must be before End year."
Exit Sub
End If
If Val(txtStart.Text) And Val(txtStart.Text) < 1850 And _
Val(txtStart.Text) > Year(Now) Then
MsgBox "Please enter a start year in the range 1850 to " & _
Year(Now)
Exit Sub
End If
If Val(txtEnd.Text) And Val(txtEnd.Text) < 1850 And _
Val(txtEnd.Text) > Year(Now) Then
MsgBox "Please enter an ending year in the range 1850 to " _
& Year(Now)
Exit Sub
End If
`Get the file to print using Common Dialog
cdOpenReport.InitDir = strSaveDir
cdOpenReport.ShowOpen
`Let's be nice and "remember" the directory for the next use
strSaveDir = cdOpenReport.filename
If Len(cdOpenReport.filename) Then
`Adding the data to the control
crptAuthors.Destination = 0 `To Window
crptAuthors.ReportFileName = cdOpenReport.filename
If Len(txtStart.Text) And Len(txtEnd.Text) Then
`Year range entered
strSelectCritera = "{Authors.Year Born} in " & _
txtStart.Text & " to " & txtEnd.Text
ElseIf Len(txtStart.Text) And Len(txtEnd.Text) = 0 Then
`Only starting year selected
strSelectCritera = "{Authors.Year Born} >= " & _
txtStart.Text
ElseIf Len(txtStart.Text) = 0 And Len(txtEnd.Text) Then
strSelectCritera = "{Authors.Year Born} <= " & _
txtEnd.Text
Else
`Both boxes are emtpy; don't limit range
strSelectCritera = ""
End If
crptAuthors.SelectionFormula = strSelectCritera
` Get the Biblio.mdb database location
strDbName = strBiblioDb()
` Assign the data file location for the report
crptAuthors.DataFiles(0) = strDbName
`Run the report
crptAuthors.Action = 1
Else
`User pressed Cancel in Common Dialog
MsgBox "No report file selected."
End If
End Sub
- After activating a common dialog Open File window to get the name of the report to use (select AUTHORS.RPT), the program sets several properties of the Crystal Report control: Destination, ReportFileName, and SelectionFormula. Remember that you set a few properties in the control as well. The program checks to see that a combination of start and end were selected (all records will print if nothing was entered) and creates the selection string used to set the SelectionFormula property. Finally, the report is printed by setting the Crystal Report control's Action property to 1.
- 19. Add the following code to the Click event of the cmdQuit command button, to provide an exit point from the program:
Private Sub cmdQuit_Click()
Unload Me
End Sub
How It Works
All the actions of the Crystal Reports control are controlled by the way various properties are set. A number of properties can specify exactly how the report is printed, as listed in Table 9.15 at the end of the chapter. There are additional properties, but those listed in the table are the most useful in controlling the print behavior of the report.
Setting the Action property of the control to 1 causes the report to print. The Crystal Reports control uses this property as a pseudo-control method. It is very important to note that printing the report does not tie up the program at the point where the Action property is set to 1. In most cases, after the report writer has begun, the Visual Basic program continues executing, so you can't perform any actions that are dependent on the completion of the report. On the other hand, your program can continue executing and performing other tasks while the report prints.
Crystal Reports Formula Formats
For the control properties that require formulas, such as SelectionFormula and GroupSelectionFormula, the formulas specified must be in the Crystal Reports format, which is quite different from the format of a Visual Basic statement. The formula itself is used to set the property as a Visual Basic string, so any literal strings needed in the formula must be enclosed in single quotation marks. In the case of the Sub procedure, cmdReport_Click, in this How-To, this formula is used when both a starting year and an ending year are specified:
"{Authors.Year Born} in " & txtStart.Text & " to " & txtEnd.TextIf the start year is 1940 and the end year is 1950, the actual formula sent to Crystal Reports is this:
"{Authors.Year Born} in 1940 to 1950"The following example shows how a string in the Visual Basic variable stateName would be coded:
"{Market.State} = `" & stateName & "`"Note the inclusion of the single quotation marks, because the string literal must be enclosed by single quotation marks. If California is the contents of the stateName variable, Crystal Reports receives this statement as this:
"{Market.State} = `California'"It is very important to avoid extraneous spaces in the string sent to the report. If the preceding Visual Basic string were instead set to
"{Market.State} = ` " & stateName & " `"the following formula would be sent to the report:
"{Market.State} = ` California `"As a result, only records with a leading space before "California" would print in the report, because of the extra space at the beginning of the criteria string.
More information about Crystal Reports formula formats can be found in the documentation for Crystal Reports.
Comments
Crystal Reports selects records differently than Microsoft Data Reports. With Crystal Reports, a String is created and assigned to the SelectionFormula properties of the Crystal Reports OLE control. If you are creating a formula to pass to the SelectionFormula property, all the error-checking must be done by the programmer. If the string is not valid, then Crystal Reports will generate a runtime error but will not tell you what part of the string is incorrect.
9.6 How do I...
Create subtotals and other calculated fields using Crystal Reports?
Problem
How do I make Crystal Reports calculate subtotals and make other calculations that I need? All of my data is in an Access .MDB file, but several of the fields I need aren't data fields at all, but are calculated from the fields in the file.
Technique
Crystal Reports supports a rich set of calculation tools and functions that enable you to make almost any type of calculation on database field data. It usually takes some work to get everything working properly, but when you are finished, you will have a powerful tool that can be used repeatedly.
In this How-To, you'll use those tools to create a bulk mail report, which can be used as the basis for completing the post office paperwork for bulk mailings. Getting the figures needed for the postage calculation involves sorting the zip codes, counting them in various groups, and checking to see which groups meet the minimum quantity requirements for the lowest postage rates.
Bulk Mailing Basics
This chapter won't be a primer on bulk mailing (the rules change constantly anyway), but here are a few basics so that the report created in this How-To will be clearer. The premise of the bulk mailing system is that if you are willing to do some of the work for the post office, you should get a break on postage. The breaks are attractive enough that a whole mailing industry has arisen around preparing mailings to qualify for those breaks.
This How-To uses a subset of all the different bulk mail categories. The categories you'll design into the report are five-digit presort, three-digit presort, state presort, and first class (the "catch-all" category). When you sort bulk mail, follow these steps:
- 1. Sort all the mailing pieces into groups that have 10 or more pieces going to the same first five digits of the zip code. Bundle those by the five-digit zip code, count them, and multiply the total by .191, the lowest postage cost of the categories used here. This gives you the total cost of that category of mail.
- 2. From the remaining pieces of mail (those that don't have at least 10 pieces per five-digit zip code), sort and extract the pieces that have at least 10 pieces going to the same first three digits of the zip code. Bundle those groups, multiply the postage by .191, and set them aside.
- 3. Again from the remaining pieces, select and sort all the pieces that have at least 10 pieces going to the same state. Bundle, calculate the postage using .238 per piece, and set the pieces aside.
- 4. Finally, gather all the remaining pieces and place first-class stamps on them. They can go with the mailing, but you aren't saving any money on them.
Before you start planning to pay your bills using bulk mail, you must have several hundred pieces mailed at the same time, and the same item must be mailed to every address. You can't even include a note in that letter, unless you include the same note to all the other people.
There is actually another category after the state level for multi-state pieces, but each additional layer complicates things at an increasing rate. So, to avoid having an entire book about a single Crystal report for bulk mailing, this How-To is limited to these categories.
Steps
The steps in this How-To show in detail how to create a bulk mail report that calculates postage and sorting order. On completion, the report, which will look as shown in Figure 9.20, will show two windows: one showing the report header and the other showing the report footer.
To open and run a report in Crystal Reports, select File|Open from the Crystal main menu, and select the BULKMAIL.RPT report. To print the report, click the Print button on the toolbar, or select File|Print from the main menu. To preview the report onscreen, click the Print Preview button on the toolbar, or select File|Print Preview from the Crystal Reports main menu. The general design details are shown in the various tables throughout this How-To. You'll go through the individual steps needed to create the bulk mail report. Figure 9.21 shows the main report elements in the Crystal Reports design window.
- 1. This How-To uses the MAIL.MDB database described in the introduction to this chapter. Start Crystal Reports, and start a new report by clicking the New Report toolbar button or selecting File|New from the main menu. Click on Standard when the Create New Report Gallery appears.
- 2. Click on Data File when the Create Report Expert appears. Use the dialog box to find the MAIL.MDB Access database file. Click Done to close the dialog box.
- 3. From the Fields tab, add the Contact, City, State, and Zip fields from the MailingList table.
- 4. On the Style tab, enter Bulk Mail Calculation Report as the title.
Figure 9.20. Print preview of bulk mail report.
Figure 9.21. Crystal Reports design window for bulk mail reports.
- 5. Insert a formula field by either clicking the Insert Formula toolbar button or selecting Insert formula Field from the main menu. Name the formula @Zip5Increment. Be sure to leave off the @ when typing the name. Enter the following code in the Edit Formula window:
WhilePrintingRecords;
NumberVar Zip5Count;
Zip5Count := Zip5Count + 1;
- The Edit Formula window should look like Figure 9.22 when you have entered the @Zip5Increment formula text. Click the Check button to have Crystal Reports evaluate the formula and check for errors. Click Accept when you're finished. After the Edit Formula window closes, your cursor is dragging a dotted box around the design window. Move your cursor to the Page Footer section of the screen, and click your left mouse button. This click "drops" the new field onto the report page. Don't worry about the exact location because we will hide the field later.
Figure 9.22. The Zip5Increment formula entry.
- 6. In the same way, enter the @TotalCountIncrement formula:
WhilePrintingRecords;
NumberVar Zip5Count;
Zip5Count := Zip5Count + 1;
- 7. Format the newly added fields by right-clicking each one and choosing Change Format from the popup menu. Check the Hide when printing box. (Notice the other format choices available, and try to keep the formatting options in mind when you develop your reports.)
- 8. Shorten each of the fields and headers by approximately one-half inch, and move all the fields to the right side of the page. Click on the Contact header, and then Ctrl-click on the contact field. Click over one of the black "ears" on the one Contact header, and shorten the field. You can also select multiple fields by choosing Edit|Select Fields from the main menu or by clicking the Select Fields toolbar button. After you make all the detail fields smaller, place them toward the right side of the detail line to leave room for section headers on the left side of the page.
- 9. Add a section to the report to group information by state. Select Insert|Group Section from the Crystal Reports main menu. From the first list box, select Mailing List.STATE. Note that the outermost group sections must be entered first, because subsequent sections will be inserted within the preceding innermost section. Make sure that the sorting field is set to ascending order, which is the default. Click OK to create the report section.
- 10. Insert the STATE field in the #1 State group by using Insert|Database Field from the main menu or the Insert Database Field toolbar button.
- 11. Insert the formula @StatePrint, which prints the total number of state addresses if they number at least 10. Place the field to the far right in the #1 State group footer band.
WhilePrintingRecords;
NumberVar StateCount;
NumberVar StateTotalCount;
NumberVar StateCost;
NumberVar StateUnitCost;
NumberVar FirstClassCount;
NumberVar FirstClassCost;
if StateCount >= 10 then
StateCost := StateCost + (StateCount * StateUnitCost)
else
FirstClassCount := FirstClassCount + StateCount;
if StateCount >= 10 then
StateTotalCount := StateTotalCount + StateCount;
if StateCount >= 10 then
StateCount;
- 12. Insert @StateTotalLine, which prints a text prompt for the total number of state addresses. Place the field just to the left of the StatePrint formula field.
WhilePrintingRecords;
NumberVar StateCount;
if StateCount >= 10 then
"Total Count for " + {Mailing List.STATE} + ": "
else
""
- 13. Insert the formula @StateReset, which resets the State count to zero. The field will be hidden, so place the field anywhere in the #1 State group header band, make it a small width, and hide it by changing its format. It is usually convenient to place hidden fields to the far right. This formula is placed in the header area to ensure that the StateCount variable is reset to zero at the beginning of every state. The WhilePrintingRecords statement ensures that the Crystal Reports will calculate this formula during printing and not before reading records.
WhilePrintingRecords;
NumberVar StateCount;
StateCount := 0;
- 14. Format the @StateReset field to hide it while printing. Format the @StatePrint field to have zero decimal places. Check the Suppress if Zero box. The Format Number dialog box should look like Figure 9.23.
Figure 9.23. Formatting for zero decimal places.
- 15. Insert the formula field @Zip_3, which takes the first three digits of the zip code. For the moment, place the field on the report somewhere that is convenient. You'll move it in a moment, after creating the next group section.
{Mailing List.ZIP}[1 to 3]- 16. Add the three-digit Zip group section. Select Insert|Group Section from the Crystal Reports main menu. From the first list box, select the formula @Zip_3. Make sure that the sorting field is set to ascending order, which is the default.
- 17. Move the @Zip_3 formula field to the far left of the #2 @Zip_3 group header band.
- 18. Insert the formula @Zip3Print, which prints the total number of three-digit zip code addresses if there are at least 10. Place the field to the far right of the #2 @Zip_3 group footer band.
WhilePrintingRecords;
NumberVar Zip3Count;
NumberVar Zip3TotalCount;
NumberVar Zip3Cost;
NumberVar Zip3UnitCost;
NumberVar StateCount;
if Zip3Count >= 10 then
Zip3Cost := Zip3Cost + (Zip3Count * Zip3UnitCost)
else
StateCount := StateCount + Zip3Count;
if Zip3Count >= 10 then
Zip3TotalCount := Zip3TotalCount + Zip3Count;
if Zip3Count >= 10 then
Zip3Count;
- 19. Insert the formula @Zip3TotalLine, which prints the text for the total number of three-digit zip addresses.
WhilePrintingRecords;
NumberVar Zip3Count;
if Zip3Count >= 10 then
"Total Count for " + {Mailing LIst.ZIP}[1 to 3] + ": "
else
""
- 20. Insert the formula field @Zip3Reset, which resets the three-digit Zip count to zero. The field will be hidden, so place the field anywhere in the #2 @Zip_3 group section header band, and make it a minimum width. Placing hidden fields out of the way to the far right is usually most convenient.
WhilePrintingRecords;
NumberVar Zip3Count;
Zip3Count := 0;
- 21. Format the @Zip3Reset field to hide it while printing. Format the @Zip3Print field to have zero decimal places and to suppress if zero.
- 22. Insert the formula field @Zip_5, which takes the first five digits of the zip code. For the moment, place the field somewhere that is convenient on the report. You'll move it in a moment, after creating the next group section.
{Mailing List.ZIP}[1 to 5]- 23. Now add the five-digit Zip group section. Select Insert|Group Section from the Crystal Reports main menu. From the first list box, select the formula @Zip_5. Make sure that the sorting field is set to ascending order, which is the default.
- 24. Move the @Zip_5 formula field to the far left of the #3 @Zip_5 group section header band.
- 25. Insert the formula @Zip5Print, which prints the total number of five-digit zip code addresses if there are at least 10. Place the field to the far right of the #3 @Zip_5 group section footer band.
WhilePrintingRecords;
NumberVar Zip5Count;
NumberVar Zip5TotalCount;
NumberVar Zip3Count;
NumberVar Zip5Cost;
NumberVar Zip5UnitCost;
if Zip5Count >= 10 then
Zip5Cost := Zip5Cost + (Zip5Count * Zip5UnitCost)
else
Zip3Count := Zip3Count + Zip5Count;
if Zip5Count >= 10 then
Zip5TotalCount := Zip5TotalCount + Zip5Count;
if Zip5Count >= 10 then
Zip5Count;
- 26. Insert the formula @Zip5TotalLine, which prints the text for the total number of five-digit zip addresses.
WhilePrintingRecords;
NumberVar Zip5Count;
if Zip5Count >= 10 then
"Total Zip (5) Count for " + {Mailing LIst.ZIP}[1 to 5] + ": "
else
""
- 27. Insert the formula field @Zip5Reset, which resets the five-digit Zip count to zero. The field will be hidden, so place the field anywhere in the #3 @Zip_5 group section header band, and make it a minimum width. Placing hidden fields to the far right is usually convenient.
WhilePrintingRecords;
NumberVar Zip5Count;
Zip5Count := 0;
- 28. Format the @Zip5Reset field to hide it while printing. Format the @Zip5Print field to have zero decimal places and to suppress printing if zero.
- 29. Set the sorting order of the fields in the report by selecting Report Sort Records from the Crystal Reports main menu. The three group sections should already appear in the right Sort Fields list, because by default the order of group sections in the report are sorted by the group field. Add the zip code field by selecting that field in the Report Fields list on the left, and either double-click on that field or click the Add button. Make sure that the order setting is set to ascending, which is the default. The Record sort Order window should now appear as shown in Figure 9.24.
Figure 9.24. Setting the record sort order.
- 30. Insert the formula field @TotalCountReset, which sets the total count to zero at the beginning of the report. The field will be hidden, so place the field anywhere in the page header band, and make it a small width. Placing hidden fields to the far right is usually convenient. For convenience, this field also sets the postage amounts for the different classes of mail.
WhilePrintingRecords;
NumberVar Zip5UnitCost;
NumberVar Zip3UnitCost;
NumberVar StateUnitCost;
NumberVar FirstClassUnitCost;
Zip5UnitCost := .191;
Zip3UnitCost := .191;
StateUnitCost := .256;
FirstClassUnitCost := .320;
- 31. Next, add the fields to the page footer. This is where you'll place the various counts and total postage for the different classes of mail. Start by adding the @FirstClassPrint formula field, which is the total count of the "miscellaneous" category of mail. Place the field in the top line of the page footer section, as far right as possible.
WhilePrintingRecords;
NumberVar FirstClassCount;
NumberVar FirstClassCost;
NumberVar FirstClassUnitCost;
FirstClassCost := FirstClassCount * FirstClassUnitCost;
FirstClassCount;
- 32. Add the @FirstClassTotalLine, which is the heading for the total count of the "miscellaneous" category of mail. Place the field in the top line of the page footer section, just to the left of the @FirstClassPrint field.
WhilePrintingRecords;
NumberVar FirstClassCount;
"Total First Class Count: ";
- 33. Now add the various running and final total formula fields. Add the @Zip3CostPrint formula field, which is the total count label of the three-digit zip category of mail. Place the field in the top line of the page footer section, at the far left.
WhilePrintingRecords;
"Zip (3) Totals:"
- 34. Add the @Zip3Count formula field. This is the running count of the number of pieces of mail that qualify for three-digit zip bulk rates. Add the field just to the right of the @Zip3CostPrint field. Format the field to have zero decimal places.
WhilePrintingRecords;
NumberVar Zip3TotalCount;
Zip3TotalCount;
- 35. Add the @Zip3TotalCost formula field. This field prints the running cost of the three-digit zip mail. Add the field just to the right of the @Zip3Count field. The cost of each category is calculated as you go, so all you need to do here is print the total. Format the field to have a currency symbol by checking the Currency Symbol checkbox in the Format Number dialog box, shown previously in Figure 9.23.
WhilePrintingRecords;
NumberVar Zip3Cost;
Zip3Cost;
- 36. Add the @Zip5CostPrint formula field, which is the total count label of the five-digit zip category of mail. Place the field in the second line of the page footer section, to the far left.
WhilePrintingRecords;
NumberVar Zip5Cost;
"Zip (5) Totals:";
- 37. Add the @Zip5Count formula field. This is the running count of the number of pieces of mail that qualify for five-digit zip bulk rates. Add the field just to the right of the @Zip5CostPrint field. Format the field to have zero decimal places.
WhilePrintingRecords;
NumberVar Zip5TotalCount;
Zip5TotalCount;
- 38. Add the @Zip5TotalCost formula field. This field prints the running cost of the five-digit zip mail. Add the field just to the right of the @Zip5Count field. The cost of each category is calculated as the mail is processed, so only the total is printed here. Format the field to have a currency symbol by checking the Currency Symbol checkbox in the Format Number dialog box.
WhilePrintingRecords;
NumberVar Zip5Cost;
Zip5Cost;
- 39. Add the @StateCostPrint formula field, which is the total count label of the state category of mail. Place the field in the third line of the page footer section, to the far left.
WhilePrintingRecords;
"State Totals:";
- 40. Add the @StateCount formula field. This is the running count of the number of pieces of mail that qualify for state bulk rates. Add the field just to the right of the @StateCostPrint field. Format the field to have zero decimal places.
WhilePrintingRecords;
NumberVar StateTotalCount;
StateTotalCount;
- 41. Add the @StateTotalCost formula field. This field prints the running cost of the state mail. Add the field just to the right of the @StateCount field. The cost of each category is calculated as the mail is processed, so only the total is printed here. Format the field to have a currency symbol by checking the Currency Symbol checkbox in the Format Number dialog box.
WhilePrintingRecords;
NumberVar StateCost;
StateCost;
- 42. Add the @FirstClassCostPrint formula field, which is the total count label of the "miscellaneous" category of mail that is charged full fare. Place the field in the second line of the page footer section, to the right of the five-digit zip information.
WhilePrintingRecords;
"First Class Totals:";
- 43. Add the @FirstClassCount formula field. This is the running count of the number of pieces of mail that don't qualify for bulk rates. Add the field just to the right of the @FirstClassCostPrint field.
WhilePrintingRecords;
NumberVar FirstClassCount;
FirstClassCount;
- 44. Add the @FirstClassTotalCost formula field. This field prints the running cost of the first-class mail. Add the field just to the right of the @FirstClassCount field. The cost of each category is calculated as the mail is processed, so only the total is printed here.
WhilePrintingRecords;
NumberVar FirstClassCost;
FirstClassCost;
- 45. Add @TotalCostPrint formula field, which is the total count label of all the categories of mail. Place the field in the third line of the page footer section, below the first-class mail information.
WhilePrintingRecords;
"Mailing Totals:";
- 46. Add the @TotalCount formula field. This is the running count of the number of pieces of all the mail. Add the field just to the right of the @TotalCostPrint field.
WhilePrintingRecords;
NumberVar TotalCount;
TotalCount;
- 47. Add the @TotalCost formula field. This field prints the running cost of all the mail. Add the field just to the right of the @TotalCount field. The cost of each category is calculated as the mail is processed, so only the total is printed here.
WhilePrintingRecords;
NumberVar Zip5Cost;
NumberVar Zip3Cost;
NumberVar StateCost;
NumberVar FirstClassCost;
NumberVar TotalCost;
TotalCost := Zip5Cost + Zip3Cost + StateCost + FirstClassCost;
TotalCost;
- 48. When you are finished inserting the various fields and group sections, the design should look something like Figure 9.25. This screen shows the design window with the main menu File Options Show Field Names option checked and all the hidden fields unhidden so that they appear more clearly.
- 49. Select File|Print Preview to preview the report, or click on the Print Preview button in the toolbar. To print the report, select File|Print Printer in the main menu, or click on the Print toolbar button.
Figure 9.25. The completed bulk mail report design.
How It Works
Crystal Reports gives you all the tools you need to create complex reports. The bulk mail report created in this How-To approaches the upper limit of complexity of a typical database report.
By using formulas in Crystal Reports, you can create customized fields to present data in almost any form, including a wide variety of database formats to which you can connect with the Open Database Connectivity (ODBC) standard. See Chapter 6, "Connecting to an ODBC Server," for a discussion about ODBC.
Many formulas start with the WhilePrintingRecords function. Although it is probably overkill in some cases, this function forces the formula to be evaluated while records are being printed. This is the normal order of evaluation:
- If no database or group field is included in the formula, the formula is evaluated before the program reads database records.
- If a database is included in the formula, the formula is evaluated while the program reads database records.
- If a group field, page # field, subtotal, and so on is included in the formula, the formula is evaluated after database records are read and while the data from the records are being printed in the report.
Including WhilePrintingRecords ensures that formulas are evaluated as the report is being printed. Other functions, BeforeReadingRecords and WhileReadingRecords, can be used to perform formula evaluations at different times. For example, you might want to record the system time at the beginning of a report for use throughout. WhileReadingRecords lets the Crystal Reports formula check to make sure that you haven't included elements in the formula that need to be evaluated while printing, such as group calculations or report elements like a page number.
One important note must be made about the placement of the @FirstClassCount and @FirstClassTotalCost fields. The @FirstClassTotalCost field must be placed on a lower line than the @FirstClassCount field; otherwise, the total cost will be incorrect. Crystal Reports generally performs its calculations in a row order, so placing @FirstClassTotalCost on the same line to the left of @FirstClassCount calculates the cost before the final count is updated.
Subtotals and Other Group Calculations
Crystal Reports provides the capability to "band" the report, which means to group similar records for grouping, sorting, and calculating. Virtually any field or portion of a field can be used to group data at various levels. In this How-To, you grouped by state, five-digit zip code, and three-digit zip code. In the latter two groups, you used the first five or three digits of the zip code, using the following Crystal Reports substring array notation:
{database.field}[1 to 5]Crystal Reports sorts records at various levels, providing options for sorting the different groups you designate, the groups themselves, and the records within the groups. This capability made it simple to put the records in the right sort order for the bulk mail groupings and to put them into the right groups. Formulas then determined whether the post office's requirement for a minimum number of mail pieces was met.
Crystal Reports provides a set of grand total functions that make it easy to provide subtotals and counts of data, as well as statistical analysis, at any group level. That way, complex formulas aren't needed in many cases. Everything in this How-To was done without any outside database processing.
Comments
Crystal formulas provide tremendous flexibility to meet your reporting needs, but they can become cumbersome, as they did in this How-To. It might make more sense to write complex reports through the use of intermediate database tables. Create the table with complex formulas in code and SQL, and then write a Crystal report to display the summarized information. The use of a private class module to build the reporting table helps make code maintenance easier.
9.7 How do I...
Control the order in which records will be printed using Crystal Reports?
Problem
I want to be able to print the same Crystal report in different sort orders, but this task is a pain--and it is time-consuming to leave my Visual Basic application to make a change to the report in the Crystal Reports design program. How can I set a report's sort order from my application?
Technique
Many of the parameters used to print a report through a Visual Basic application can be easily set using the Crystal Reports custom control. In this How-To, you'll create a list of computer book publishers. Through a Visual Basic program, you'll change the sort order of the report at runtime.
Steps
Load and run the Visual Basic application PUBLISH.VBP. Click one of the Report buttons to view the report in a preview window in zip, name, or city sort order. See Figure 9.26.
Figure 9.26. The Print Publishers' Names selection window.
Start by creating a simple report that can be modified through Visual Basic during printing. Start the Crystal Reports program.
- 1. Click on the New Report toolbar button, or select File|New from the main menu. The New Report Gallery appears. Click the Listing option button.
- 2. When the Create Report Expert appears, click the Data File button, and use the common dialog to select the location of your Biblio.MDB file.
- 3. On the Fields tab, add the fields listed in Table 9.15 to your report by either double-clicking on the field name or selecting the field name and clicking Add.
Table 9.15. Computer author report tables and fields.
TABLE Field Publishers State Publishers Zip Publishers Telephone Publishers City Publishers Name
- 4. On the Style tab, enter the text Computer Book Publishers as the report title.
- 5. Preview the report.
- 6. Now add the State group section. Inserting this section keeps the records grouped by state, so the individual records are sorted by zip, name, or city within each state. Select Insert|Group Section from the Crystal Reports main menu. From the first list box, select Publishers.State. Make sure that the sorting option is set to ascending order, which is the default. You won't enter any other fields in this group section.
- 7. This is the report you'll use. Remember to save the file, calling it PUBLISH.RPT.
- 8. Start Visual Basic and create a new Standard EXE project in your work area. Save the default form as PUBLISH.FRM, and save the project as PUBLISH.VBP. Select Project Components from the Visual Basic main menu, and make sure that the Crystal Reports control is selected. Add the READINI.BAS file to the project to find your copy of the BIBLIO.MDB file.
- 9. Place controls on the form as shown in Figure 9.26, and set the properties as shown in Table 9.16. Note that the Crystal Reports control is invisible at runtime, so place it anywhere on the form that is convenient. Note also that the three report command buttons make up a control array.
Table 9.16. Objects and properties for. PUBLISH.FRM.
OBJECT Property Setting Form Name frmPublishers Caption "Print Publishers' Names" CommandButton Name cmdQuit Caption "Quit" CommandButton Name cmdReport Caption "Report by Zip" Index 0 CommandButton Name cmdReport Caption "Report by City" Index 1 CommandButton Name cmdReport Caption "Report by Name" Index 2 CrystalReport Name crptPublishers ReportFileName "D:\Waite\Chapter.11\How-to.113\Publish.rpt" Destination 0 `To Window
- 10. Add the following code to the Click event of the cmdReport command-button control array. This procedure sets the ReportFileName to "PUBLISH.RPT" in the application's path, sets the SortFields property of the Crystal Reports custom control to the sort order desired, and assigns the Crystal Reports print preview window title. Then it sets the Action property to 1 to print the report.
Private Sub cmdReport_Click(Index As Integer)
Dim strDbName As String
` Get the Biblio.mdb database location
strDbName = strBiblioDb()
` Assign the data file location for the report
crptPublishers.DataFiles(0) = strDbName
` Assign the report file name
crptPublishers.ReportFileName = App.Path & "\Publish.rpt"
`Set up the Report control
Select Case Index
Case 0 `Print by Zip
crptPublishers.SortFields(0) = "+{Publishers.Zip}"
crptPublishers.WindowTitle = "Publishers by Zip Code"
Case 1 `Print by City
crptPublishers.SortFields(0) = "+{Publishers.City}"
crptPublishers.WindowTitle = "Publishers by City"
Case 2 `Print by Name
crptPublishers.SortFields(0) = "+{Publishers.Name}"
crptPublishers.WindowTitle = "Publishers by Company Name"
End Select
crptPublishers.Action = 1
End Sub
- 11. Add the following code to the Click event of the cmdQuit command button, to provide an exit point from the program:
Private Sub cmdQuit_Click()
Unload Me
End Sub
- 12. Add the following code to the form's Load event procedure. This code simply moves the form to the lower-right portion of the screen, out of the way of the report window when it appears.
Private Sub Form_Load()
`Move the form to the lower right of screen
Me.Move Screen.Width - 1.1 * Me.Width, _
Screen.Height - 1.25 * Me.Height
End Sub
How It Works
This is all it takes to create a report in Crystal Reports and an application in Visual Basic that controls the sort order of the report. Entering a state group section causes the overall sort order of the report to always be by state. Then the individual publisher records are sorted within each state. Leaving out the group section would cause all records to be sorted by zip, city, or name, without regard to state.
This How-To used the properties of the Crystal Reports custom control. You had to change only a single property, SortFields, to set the sort order. SortFields is an array, so you can enter as many sort fields as you want. In fact, the following groups of Visual Basic code would also keep all the records sorted by state and then by the secondary sort order:
CrystalReport1.SortFields(0) = "+{Publishers.State}"
CrystalReport1.SortFields(1) = "+{Publishers.Zip}"
CrystalReport1.SortFields(0) = "+{Publishers.State}"
CrystalReport1.SortFields(1) = "+{Publishers.City}"
CrystalReport1.SortFields(0) = "+{Publishers.State}"
CrystalReport1.SortFields(1) = "+{Publishers.Name}"The plus sign at the beginning of each field name means to sort the records in ascending order. Use a minus sign to sort in descending order. The use of ascending and descending sort orders for different fields can be mixed and matched in a single report as much as you like.
Crystal Reports also has the capability to sort the group sections in any order you like. This can be set either in the report itself or again through the Crystal Reports custom control using the GroupSortFields property in the same way as the SortFields property is used. So, for example, in this report you could have specified to sort the state groups in descending order, starting with Washington and progressing to Alaska at the end of the report.
Comments
This How-To has illustrated one of the most frequent changes required for a report--changing the sort order. Consider using this feature carefully on very large, frequently used reports if the underlying database does not provide a convenient index. The Crystal Reports engine is pretty good at using database indices to retrieve data in the order it is needed, but a poorly sorted report can take forever to run.
9.8 How do I...
Print labels using Crystal Reports?
Problem
I need to produce mailing labels for our marketing program. How can I use Visual Basic to automatically print the labels we need so that they are ready for use on our mailings?
Technique
This How-To uses Crystal Reports' Mailing Labels design window. Crystal Reports ships with various standard Avery label formats, so there is a pretty good chance that the exact label you need is one of the Avery formats. Even if it isn't, it is quite easy and straightforward to modify one of the formats or create your own label.
This How-To can be combined with How-To 9.6, which creates a bulk mailing report, to print labels already sorted for bulk mailing, with the postage already calculated.
Steps
The steps in this How-To show in detail how to create a shipping label. To open and run a report in Crystal Reports, select File|Open from the Crystal main menu, and select the SHIPLBL.RPT report file, as shown in Figure 9.27. To print the report, click the Print button on the toolbar, or select File|Print from the main menu. To preview the report on-screen, click the Print Preview button on the toolbar, or select File|Print Preview from the Crystal Reports main menu.
- 1. This How-To uses the MAIL.MDB database described in the introduction to this chapter. Start Crystal Reports, and start a new report by clicking the New Report toolbar button or selecting File|New from the main menu. Click on Mail Label when the Create New Report Gallery appears.
Figure 9.27. Crystal Reports design view for SHIPLBL.RPT.
- 2. Click on Data File when the Create Report Expert appears. Use the dialog box to find the MAIL.MDB Access database file. Click Done to close the dialog box.
- 3. From the Fields tab, add the Contact and ADDR1 fields to the report.
- 4. Now, instead of placing the City, State, and Zip fields separately, you'll use a Crystal Reports formula so that all three fields appear on the same line without extra spaces. Start by clicking the Formula button on the Fields tab. Name the formula CityStateZip, click OK, and enter this formula:
TrimRight({Mailing LIst.CITY}) + ", " + TrimRight({Mailing LIst.STATE}) _
+ " " + {Mailing LIst.ZIP}- The Crystal Reports TrimRight function removes extra spaces from text fields. Click Check to make sure that the formula is correct, and then click Accept to define the formula. Click the Add button to include @CityStateZip with the printed fields in the right-hand window.
- 5. On the Label tab, Select an Avery Shipping/Address label (Avery 5164). This label is 4 inches wide by 3.33 inches high, so there are two columns of three labels. That leaves room for both a snazzy return address and the addressee information.
- Select the Avery 5164 label by scrolling down through the Choose Mailing Label Type list box. Click on that entry, and you are finished designing the label layout. The Label tab of the Create Report Expert dialog box should then look like the one shown in Figure 9.28. You can also select the print sequence, either across or down first, by making a selection in the Printing Direction box. Leave the default set at Across then Down. Click OK to insert this format into the report.
Figure 9.28. The Create Report Expert dialog box.
- 6. Click Preview Report to see the basic report. After reviewing the labels, switch to design view by clicking the Design tab.
- 7. Select the three fields on the report by Ctrl-clicking on each field in turn. Right-click and use the Change Font dialog box to increase the font size to 12 points. You can also change font attributes by using the font toolbar at the bottom of the Crystal Reports designer window. Drag the three fields down the label to just below the middle on the left side.
- 8. Add agraphic element in the upper-left corner. Select Insert|Picture from the main menu, or click the Insert Picture button on the toolbar. When the Choose Graphic File box appears, select a graphics file from any of the supported formats: Windows bitmap (BMP), CompuServe (GIF), PC Paintbrush (PCX), TIFF (TIF), or TARGA (TGA). EARTH.GIF, courtesy of NASA and the Galileo spacecraft program, is included on the CD-ROM that accompanies this book.
- After you select the file, click OK and the image appears on the Crystal Reports design screen. Position it so that the upper-left corner of the image is at or near the upper-left corner of the label, inside the left vertical and top horizontal gray lines. Choose Format|Picture from the main menu, and format the picture 2 inches wide and 1.5 inches tall, as shown in Figure 9.29. You'll need to play with the aspect ratio (the ratio of height to width) to get it to look right. Because of different screen and printer aspect ratios, what looks right on the screen might not look right on your printer.
Figure 9.29. The Graphic format dialog box.
- 9. Enter three text fields, and enter the text Global Research Network, One Uranus Place, and Houston, Texas 04107. Place these text fields in the upper-right quarter of the label next to the picture. Format the fields for a 12-point bold italic font.
- 10. Insert a horizontal dividing line to split the two address areas of the label, by either selecting Insert Line from the main menu or clicking on the Insert Line button on the toolbar. Place the point of the line drawing tool directly on the label's left border, with the gray vertical line near the left edge of the label. Click and hold down the left mouse button as you drag the tool to the right edge of the label. Release the mouse button. If you need to, adjust the position or length of the line just as you would with any other field. Format the line to your preferred thickness.
- 11. Some versions of Crystal Reports enlarge the label size on the design window when you increase the font size on a line of the report. This causes you to end up with only one or two rows of labels on the page, rather than three rows. To restore the proper label size, scroll down to the bottom of the label. Notice that the bottom section border (the line that extends into the gray area to the left of the design area) is one-half to one-fourth of an inch below the rectangle of the label. Drag the bottom section edge as far up as it will go, adjacent to the dashed gray line. In other words, the bottom edge of the label, the bottom edge of the section, and the dashed line should all be very close to one another.
- 12. Remember to save the file, calling it SHIPLBL.RPT.
How It Works
When you run this report, Crystal uses the label format specifications to repeat different records across and down the page. The selected graphic is automatically included on each label.
Comments
Designing labels with Crystal Reports is essentially the same as designing any other report. Crystal Reports has support for most of the labels you'll need. If none of the formats is exactly right, pick something close and change the sizes and format to fit your needs.
9.9 How do I...
Create and print form letters using Crystal Reports?
Problem
Now that I can print my mailing labels, how can I print the form letters that will go into the mailing envelopes? How can I use my database with text to prepare form letter reports?
Technique
By using a couple of formatting tricks with Crystal Reports, you can use the report writer to generate almost any type of database report you need. This How-To shows how you can use Crystal Reports to replace your word processor's mail merge, and how to use formulas and field formatting to present your data in the most attractive format.
Steps
The steps in this How-To show in detail how to create a multi-page form letter. To open and run a report in Crystal Reports, select File|Open from the Crystal main menu, and select the FORMLTR.RPT report file, as shown in Figure 9.30. To print the report, click the Print button on the toolbar, or select File|Print from the main menu. To preview the report on-screen, click the Print Preview button on the toolbar, or select File|Print Preview from the Crystal Reports main menu.
- 1. This How-To uses the MAIL.MDB database described in the introduction to this chapter. Start Crystal Reports, and begin a new report by clicking the New Report toolbar button or selecting File|New from the main menu. Click the Custom button when the Create New Report Gallery appears.
- 2. Click on Data File in the lower-right corner after the Create Report Expert expands. Use the dialog box to find the MAIL.MDB Access database file. Click Done to close the dialog box. A screen similar to that shown in Figure 9.31 appears.
Figure 9.30. Crystal Reports design view for FORMLTR.RPT.
Figure 9.31. Crystal Reports blank report design view.
- 3. As you design the report, be sure to save your work periodically by selecting File|Save (or File|Save As, the first time) from the Crystal Reports main menu.
- 4. Because you don't want extra column headings for a form letter, Select File|Options from the main menu. On the Layout tab, uncheck the Insert Detail Field Titles option.
- 5. Start by inserting the date for the letter. Select Insert|Special Field|Print Date Field. Locate the field in the detail section, to the far left.
- 6. Expand the size of the detail section by dragging the bottom gray line of the section down as far as it will go. Alternatively, place the text cursor on the last line of the detail section and press Enter as many times as needed to expand the section.
- 7. In the Insert Database Field window, double-click on the Contact field; then enter it in the detail line, or drag it from the Insert Database Field window. Place the field on the second line after the Date field.
- 8. Repeat the last step for the Addr1 field, placing it on the line after the Contact field.
- 9. Now, instead of placing the City, State, and Zip fields separately, you'll use a Crystal Reports formula so that all three fields appear on the same line without extraneous spaces. Start by clicking the Done button on the Insert Database Field window to get it out of the way. Select Insert|Formula Field from the Crystal Reports main menu, and enter the name CityStateZip in the Formula name text box. Click OK, and enter this formula:
TrimRight({Mailing LIst.CITY}) + ", " + TrimRight({Mailing _
LIst.STATE})
+ " " + {Mailing LIst.ZIP}- The Crystal Reports TrimRight function removes extra spaces from text fields. Click Check to make sure that the formula is correct; then click Accept to place the formula field on the report, on the line under the Addr1 field.
- 10. You'll use another formula field for the salutation. Select Insert|Formula Field from the Crystal Reports main menu, and enter the name Salutation in the Formula name text box. Click OK, and enter this formula:
"Dear " + TrimRight({Mailing LIst.CONTACT}) + ","- The Contact file in the Mailing List table has generic names, such as Medical Practitioner and Occupant. You can easily modify this formula to accommodate a Salutation field, an actual name, or the ever-so-personal "Dear Sir or Madam."
- 11. Set the margins for the letter. Select File|Page Margins from the main menu, and enter 1.00 inch for the top and bottom margins, and 1.25 inches for the side margins. Click OK to return to the report and set the margins.
- 12. Now you'll enter the body of the letter. In this How-To, you'll enter the text in separate text fields for each paragraph so that you can enter fields in certain paragraphs to customize each letter. You can also enter all the text in a single text field if you don't need to customize the text, or even in a single formula field. The latter method tends to get a bit unwieldy and reduces your formatting options.
- Crystal Reports has a rather serious flaw that turns what should be a single step into two steps. If you simply type each paragraph's text into the Edit Text Field window and enter the field into the report, Crystal Reports makes the field as wide as needed to fit the text. When you're using entire paragraphs, the field becomes several times the width of the report. When a Crystal Reports field extends beyond the right margin, there is no way to make the field narrower because Crystal Reports prevents you from grabbing the right edge of the field. On top of that, there is no field formatting option for field width. So you'll need to enter a text field with a single space in it and place that field on the report. You should then edit the text of the field, entering the text you actually want. Then you can size the field to the full width of the report.
- Start by expanding the detail section, if necessary. Select Insert|Text Field from the main menu, or click the Insert Text button on the toolbar. Enter one or two spaces, and click OK to place the field on the report. Place the field on the second line after the salutation, at the left margin. Format the field's font for 12-point type.
- 13. Go back into the field to edit the text by right-clicking on the field and then selecting Edit Text Field from the popup menu, or by selecting Edit|Text Field from the main menu. Enter the following text in the Edit Text Field window. When you're finished typing, click the Accept button to insert the field on the report, and stretch the field to the full width of the report.
Welcome to the Slick Willy Sales Course! I congratulate you on your decision to take the course, because with hard work and study the experience should greatly increase your sales skills. I can speak from experience--after I took the course five years ago, my sales success went up dramatically.
- The field remains one line long in the report design window. When you format the following fields, you'll set an option that tells Crystal Reports to expand the field vertically to show all the text.
- 14. Repeating the procedure in the preceding two steps, enter each of the following paragraphs in a separate text field. You'll need to enter a lot of text so that the letter is two pages long to illustrate how to format different page headers.
I am the Group Leader assigned to keep in contact with you throughout the course. I'll call you regularly to find out how you are doing, whether you need any help, answer any questions you might have, and help you get the most out of the course. I'd be happy to meet with you to discuss the course, too--sometimes face to face is the only way to work out issues. This applies not only to the class material but also to particular sales calls or prospects you might like to discuss.
Feel free to call on any of the other Group Leaders. Call on whatever resources you feel will best help you become a better salesperson.
Please come to class with the homework prepared, for two reasons
First, doing the work is the only way to learn the material. The class will be a waste of your time if you don't learn anything! Second, we will use the assignments in class the following week.
The reading assignments are important too, not just for the lessons they contain, but because you'll occasionally be called upon to give summaries of the readings.
And finally, read through the next week's lesson in the workbook, so you'll have an idea of what to expect in class and can be prepared to discuss the lessons.
The single most important way to learn the material and use it successfully is to use it during the following week. Plan ahead and incorporate it into your sales calls. Think about how to make it work for you. In fact, not everything will work for you directly, but you can almost always adapt a concept to your advantage.
Charley and all of the Group Leaders arrive at the classroom by 5:30 P.M. the night of each class, so come early if you'd like to discuss any of the material in person. Also, as I previously mentioned, any of us can meet with you during the week.
- 15. Now, enter a customized paragraph. Select Insert|Formula Field from the main menu, or click the Insert Formula Field button on the toolbar. Name the field CustomParagraph, and enter this formula:
WhilePrintingRecords;
StringVar para;
if {Mailing LIst.CONTACT} = "Medical Practioner" then
para := "Since you live in the city of " +
TrimRight({Mailing LIst.CITY}) +
", you can take advantage of our convenient MedShuttle. "
else
if {Mailing LIst.CONTACT} = "Occupant" then
para := "In the city of " + TrimRight({Mailing LIst.CITY}) _
+ ", there is an excellent rail system, " +
"with a stop within walking distance of the meeting room."
else
para := " Please arrange your own transportation from "
+ TrimRight({Mailing List.CITY}) + ". ";
para := para +
"Please call us at 800-555-1212 if you need more information about getting here."
- 16. Enter the final two text fields. The Sincerely yours...Group Leader lines can be in a single text field, using carriage returns at the end of each line.
We want you to be successful in the class and in your selling future. Let us know if there is any way we can help you achieve that success.
Sincerely yours,
Bill Morehours
Group Leader
- 17. Format these detail section fields as shown in Table 9.17. The most important setting is Print on Multiple Lines for all the paragraph fields so that the text fields will expand vertically to accommodate all the text in the paragraph.
Table 9.17. The detail section fields and formatting.
REPORT ELEMENT Values Detail Section New Page After PrintDate Date, Default Alignment, 1 March, 1999 Mailing List.CONTACT String, Default Alignment Mailing List.ADDR1 String, Default Alignment @CityStateZip String, Default Alignment @Salutation String, Default Alignment Paragraph text fields String, Default Alignment, MultipleLines, 12 pt Font @CustomParagraph String, Default Alignment, MultipleLines, 12 pt Font
- 18. Now, enter the fields for the page header. You have two sets of fields: one for the first page of the letter with your company name and address, and another for the second page header showing the addressee, date, and page. Use a Boolean variable FirstPage to keep track of whether this is the first page of the letter.
- Select Insert|Formula Field from the main menu, or click the Insert Formula Field button on the toolbar. Name the formula Masthead1, and enter the following formula. Place the field on the top line of the page header, at the left margin.
WhilePrintingRecords;
BooleanVar FirstPage;
if FirstPage then
FirstPage := False
else
FirstPage := True;
if FirstPage then
"Slick Willy Sales and Aerobics Training";
- 19. Select Insert|Formula Field from the main menu, or click the Insert Formula Field button on the toolbar. Name the formula Masthead2, and enter the following formula. Place the field against the right margin on the second line of the page header.
WhilePrintingRecords;
BooleanVar FirstPage;
if FirstPage = True then
"One Pennsylvania Avenue, Nashville, Tennessee 80104";
- 20. Select Insert|Formula Field from the main menu, or click the Insert Formula Field button on the toolbar. Name the formula Masthead3, and enter the following formula. Place the field against the right margin on the third line of the page header.
WhilePrintingRecords;
BooleanVar FirstPage;
if FirstPage = True then
"(800) 555-9875";
- 21. Now, enter the second set of page header fields, for the second page of the letter. Select Insert|Formula Field from the main menu, or click the Insert Formula Field button on the toolbar. Name the formula PageHead, and enter the following formula. Place the field against the left margin on the second line of the page header, under @Masthead1.
WhilePrintingRecords;
BooleanVar FirstPage;
if FirstPage = False then
{Mailing LIst.CONTACT};
- 22. Select Insert|Formula Field from the main menu, or click the Insert Formula Field button on the toolbar. Name the formula PageHeadDate, and enter the following formula. Place the field against the left margin on the third line of the page header.
WhilePrintingRecords;
BooleanVar FirstPage;
if FirstPage = False then
Today;
- 23. Select Insert|Formula Field from the main menu, or click the Insert Formula Field button on the toolbar. Name the formula PageHeadPage, and enter the following formula. Place the field against the left margin on the third line of the page header.
WhilePrintingRecords;
BooleanVar FirstPage;
if FirstPage = False then
"Page 2";
- 24. Expand the size of the page header section to be one line longer than the bottom-most field so that both pages will have a blank line between the page header and the start of the text (detail section).
- 25. Format the page header section fields as shown in Table 9.18.
Table 9.18. The page header section fields and formatting.
REPORT ELEMENT Values Header Section Visible, New Page Before, Keep Together @Masthead1 String, Default Alignment, 16pt Font, Bold Italic @PageHead String, Default Alignment @Masthead2 String, Right Alignment, 10pt Font, Italic @PageHeadDate Date, Default Alignment, 1 March, 1999 @Masthead3 String, Right Alignment, 10pt Font, Italic @PageHeadPage String, Default Alignment
- 26. Run the report, making sure that the different page headers print on the correct page.
How It Works
This How-To used a Crystal Reports Boolean variable to keep track of which page was printing. Although Crystal Reports can print different headers and footers on the first page of the report, it treats all subsequent pages as "nonfirst page." In situations like this form letter, in which each record prints one or more full pages, keep track of where you are by using formulas.
As mentioned in the preceding set of steps, the text for the letter can be put into fields, it can come from the database in memo fields, or it can all be put into one large text field. It really depends on a few factors:
- If every letter will be the same, with only one or two small customizations, use the technique in this How-To, in which a text field is used for each paragraph. This technique gives you the best combination of formatting flexibility and also allows the customization.
- If all the text of the letters is the same, you could use one large text field to hold it all. This means all the text must be formatted the same, and you must use trial and error to get the page headers and footers to work right. Also, Crystal Reports has a problem with General Protection Faults and some video drivers, particularly when editing text fields that have some trailing space configurations.
- If the text in each letter changes substantially, and if much of the text is in the table you use for the report, you can use database fields in combination with text fields and formulas to control (to a low level of detail) how each letter is formatted and what information it contains.
Other than keeping track of which header to print, this form letter report is created in the same way as the other reports in this chapter. Crystal Reports formulas provide you with a great deal of flexibility in presenting database records in the most useful format.
Comments
Using Crystal Reports to print form letters probably isn't the best way to perform the task. Today's word processors make the job easy. Generally, they can use data in a wide variety of formats and provide far more formatting flexibility. But as this How-To shows, Crystal Reports has its own wide variety of flexible tools to perform many printing jobs on its own. Who was it who said, "If the only tool you have is a screwdriver, the whole world looks like a screw"?
9.10 How do I...
Print field data without extra spaces between the fields using Crystal Reports?
Problem
When I create a report, I always need to put individual fields on the report for each database field. My database splits a client's name into "Mr.," "John," and "Jones," and I'd like that name to appear as "Mr. John Jones." How can I do this in Crystal Reports?
Technique
This How-To creates a customer directory list using the string functions of Crystal Reports and operators to make the report fields appear as one field, even though the information is in several different fields. After the report is created, the functions and operators that Crystal Reports provides to manipulate strings in your database will be discussed.
Steps
The steps in this How-To show in detail how to create a customer directory in Crystal Reports. Select File|Open from the Crystal main menu, and select the CUSTDIR.RPT report file, as shown in Figure 9.32. To print the report, click the Print button on the toolbar, or select File|Print from the main menu. To preview the report on-screen, click the Print Preview button on the toolbar, or select File|Print Preview from the Crystal Reports main menu.
- 1. This How-To uses the CRYSTAL.MDB database described in the introduction to this chapter. Start Crystal Reports, and begin a new report by clicking the New Report toolbar button or selecting File|New from the main menu. Click the Custom button when the Create New Report Gallery appears.
- 2. Make sure that Detail field names are automatically inserted into the page header. Select File|Options from the main menu. On the Layout tab, check the Insert Detail Field Titles option.
- 3. Click on Data File in the lower-right corner after the Create Report Expert expands. Use the dialog box to find the CRYSTAL.MDB Access database file. Click Done to close the dialog box.
Figure 9.32. Crystal Reports design view for CUSTDIR.RPT.
- 4. Start by adjusting the margins for the report. Select File|Page Margins from the Crystal Reports main menu. Set the top margin to 0.5 inch and the other three margins to 1.0 inch.
- 5. Double-click on the CoName field and enter it in the detail line, or drag it from the Insert Database Field window. Place the field about one-fourth of an inch from the left margin in the detail section.
- 6. Now, create an @Contact field, using the FormAddress, ContactFN, and ContactLN fields. Select Insert|Formula Field from the Crystal Reports main menu, or click the Insert Formula Field button on the toolbar. Name the field Contact, and enter this formula. Place the field to the right of the CoName field.
WhilePrintingRecords;
StringVar BuiltStr;
if Length({COMPANY.FORMADDRES}) > 0 then
BuiltStr := TrimRight({COMPANY.FORMADDRES}) + " ";
BuiltStr := BuiltStr + TrimRight({COMPANY.CONTACTFN}) + " "
+ TrimRight({COMPANY.CONTACTLN});
- 7. Select Insert|Formula Field from the Crystal Reports main menu, or click the Insert Formula Field button on the toolbar. Name the field CustomerID, and enter this formula. Place the field to the right of the @Contact formula field.
TrimRight({COMPANY.CUSTNUM})- 8. Enlarge the height of the detail section by one line by dragging the lower-section line down with the mouse. To expand the section using the keyboard, place the text cursor at the beginning of the last line of the section and press Enter once.
- 9. Select Insert|Formula Field from the Crystal Reports main menu, or click the Insert Formula Field button on the toolbar. Name the field CompanyAddress, and enter this formula. Place the field on the second line of the detail section, about one-half inch to the right of the left margin. Delete the field title Crystal Reports automatically generated from the Page Header section.
WhilePrintingRecords;
TrimRight({COMPANY.ADDRESS}) + ", " +
TrimRight({COMPANY.CITY}) + ", " +
TrimRight({COMPANY.STATE}) + " " +
{COMPANY.ZIP_POSTAL}
- 10. Format these detail section fields as shown in Table 9.19.
Table 9.19. The detail section fields and formatting.
REPORT ELEMENT Values Detail Section Visible @CustomerID String, Centered Alignment COMPANY.CONAME String, Default Alignment @Contact String, Default Alignment @CompanyAddress String, Default Alignment, Multiple Lines
- 11. Insert a report heading in the page header. Select Insert|Text Field from the Crystal Reports main menu, and enter Customer Directory. Place the field at the upper-left corner of the report area. Wait to adjust the width of the field until you change the font size.
- 12. As you entered fields in the detail section, Crystal Reports should have inserted corresponding column titles in the page header section. Adjust those text fields so that they are more or less above the appropriate field. You'll need to adjust them later when everything else is finished so that they are aesthetically pleasing.
- 13. Expand the size of the page header by dragging the lower edge down one line of text. Select Insert|Line from the Crystal Reports main menu, or click the Insert Line button on the toolbar. Place the tip of the drawing tool at the left margin, just a bit lower than the bottom edge of the line of column headings. Drag the tool across the width of the report, and release the mouse button at the right margin. Click the line with the right mouse button, and select Change Format from the popup menu, or select Format|Line from the main menu. On the Width line, select the third box from the right, for a line width of 2.50 points. Click OK to close the Line Format window.
- 14. Add a page number in the page footer section. Select Insert|Special Field|Page Number.
- 15. Format the page header and footer section fields, as shown in Table 9.20.
Table 9.20. The page header and footer section fields and formatting.
REPORT ELEMENT Values Header Section Visible, New Page Before, Keep Together Text Field Customer Directory String, Centered Alignment, 14pt Font, Bold Italic Text Field Contact String, Left Alignment Text Field Customer String, Left Alignment Text Field Customer ID String, Centered Alignment Line 2.50pt Width Footer Section Visible, New Page After, Keep Together, Print at Page, Bottom @PageNo String, Right Alignment
- 16. This finishes the Customer Directory report. Remember to save the file, calling it CUSTDIR.RPT.
How It Works
Crystal Reports has several functions and operators for converting other data types to strings and manipulating strings to appear the way you want. Table 9.21 lists the primary functions that Crystal Reports provides for this purpose, and Table 9.22 lists the operators that are most useful.
Table 9.21. Useful Crystal Reports functions for manipulating strings.
| FUNCTION | Description |
| Length(x) | Indicates the number of characters in the string, including leading and trailing spaces |
| LowerCase (x) | Converts all alphabetical characters in the string to lowercase |
| NumericText(fieldname) | Indicates whether all characters are numeric |
| ReplicateString(x, n) | Prints string x, n times |
| ToNumber (x) | Converts the string to a number |
| ToText (x) | Converts a number to a text string, with two decimal places |
| ToText (x, # places) | Converts a number to a text string, with # decimal places |
| TrimLeft (x) | Removes leading spaces from the string |
| TrimRight (x) | Removes trailing spaces from the string |
| UpperCase (x) | Converts all alphabetical characters in the string to uppercase |
Table 9.22. Useful Crystal Reports operators for manipulating strings.
| FUNCTION | DESCRIPTION |
| + | Concatenation |
| [] | Subscript |
| In | In string |
Crystal Reports likes an array of characters, so you can use array notation to extract characters from a string, as shown in the following examples. Using a field called Address:
{Company.Address} = "1245 East Elm Lane"Use the substring operator in the following lines to show different extractions:
{Company.Address}[1 to 5] equals "1245 "
{Company.Address}[6 to 200] equals "East Elm Lane"
{Company.Address}[3 to Len(TrimRight({Company.Address}))
equals "45 East Elm Lane"Here are a couple of final notes about strings in Crystal Reports:
- When you trim and concatenate strings, remember to add spaces where you need them. For example, you needed to include a space within the quotation marks when you concatenated the FormAddress, ContactFN, and ContactLN fields. Otherwise, there would have been no space between the names.
- In some databases, such as Paradox, it isn't necessary to use the TrimRight function to eliminate trailing spaces. Paradox includes a null character at the end of the string, effectively making the length of the string the number of characters without trailing spaces. It doesn't hurt to include the TrimRight function, because the format could change in the future or you might need to adapt the report to be used with another database.
COMMENTS
Although Crystal provides many options for manipulating strings, consider carefully the best way to accomplish your goal, especially if the database server is able to perform your task. The use of SQL enables you to perform many string operations in the database and simplifies Crystal operations. In some cases, printing directly from Visual Basic might be the best solution.
9.11 How do I...
Prevent blank lines from being printed when a field contains no data using Crystal Reports?
Problem
We use some tables that have many fields that only occasionally have data in them. How can I set up a report so that a line prints only when it has data, and still allow memo fields with lots of data to print in their entirety?
Technique
This How-To uses two of the space saving features of Crystal Reports--the Suppress Blank Lines option and Print on Multiple Lines option for text boxes. The Suppress Blank Lines option applies to an entire section, setting the report to print only lines that contain data. That means you can conserve paper and save trees that would otherwise be needed to print much longer reports containing many blank fields.
The Print on Multiple Lines option lets you place a text box on the report as a single line so that the box doesn't need to be made large enough to show the longest possible string, wasting space on the report. This option tells Crystal Reports to go ahead and expand the field vertically to fit the text if the text requires two or more lines. Otherwise, only the portion of the string that fits in the first line of the field will print on the report.
Even when using these two options, you still need to design reports intelligently. It would be easy to have a line with several fields, any of which could be blank or contain data. The line would print if any of the fields has data, so you need to try to put only a single field on each line or to group fields together that are likely to be blank at the same time.
This How-To uses the Publishers, Publishers Comments, and Titles tables in the BIBLIO.MDB file. Each table has several fields that are often blank mixed in with long memo fields.
Steps
The steps in this How-To show in detail how to create a fairly complex report from multiple tables and allow for missing information. To open and run a report in Crystal Reports, select File|Open from the Crystal main menu, and select the TITLES.RPT report file, as shown in Figure 9.33. To print the report, click the Print button on the toolbar, or select File|Print from the main menu. To preview the report onscreen, click the Print Preview button on the toolbar, or select File|Print Preview from the Crystal Reports main menu.
Figure 9.33. Crystal Reports design view for the publisher titles report.
- 1. This How-To uses the BIBLIO.MDB database described in the introduction to this chapter. Start Crystal Reports, and begin a new report by clicking the New Report toolbar button or selecting File|New from the main menu. Click the Custom button when the Create New Report Gallery appears.
- 2. Make sure that Detail field names are not automatically inserted into the page header section. Select File|Options from the main menu. On the Layout tab, uncheck the Insert Detail Field Titles option.
- 3. Click on Data File in the lower-right corner after the Create Report Expert expands. Use the dialog box to locate the BIBLIO.MDB Access database file installed with Visual Basic.
- 4. Start by adjusting the margins for the report. Select File|Page Margins from the Crystal Reports main menu. Set the top margin to 0.5 inch and the other three margins to 1.0 inch.
- 5. Add an asterisk at the beginning of the Details line to make the individual titles (Detail section) stand out more clearly to the reader of the report. Enter this as a formula field, because it is conceivable that a record in the table would not have a title but would still have other relevant information. Select Insert|Formula Field from the main menu, or click the Insert Formula Field button on the toolbar. Enter the formula name Bullet, and insert the following formula. Place the field about one-fourth of an inch from the left margin, and make its width just wide enough for the asterisk.
if Not IsNull({Titles.Title}) then
"*"- 6. Double-click on the Titles.Title field and enter it in the detail line, or drag it from the Insert Database Field window. Place the field just to the right of the Bullet field. Don't adjust the width until after the font size is set.
- 7. Expand the size of the detail section by dragging the lower boundary down as far as it will go. Because Crystal Reports limits the amount by which you can expand a section's size, you might need to expand the section again as you add fields.
- 8. Double-click on the Description field and enter it in the detail line, or drag it from the Insert Database Field window. Place the field on the line below the Title field, about one-fourth of an inch to the right of the Title. Drag the right border to the right margin so that the field takes up the remaining width of the report.
- 9. In the same way, add the Notes, Subject, and Comments fields to the report, adding each below the preceding field. Align the Notes and Subject fields with the Description field, and place the Comments field about one-fourth of an inch to the right. Expand all the fields so that the right edge of each field is at the right margin of the report.
- 10. Format these detail section fields as shown in Table 9.23. Remember to expand the Title field to the right margin after changing the font to bold. Also, if there are any blank lines at the bottom of the detail section, drag the lower detail section border up to the bottom of the Comments field. A very quick way to format for multiple lines is to double-click the field for which you want to format a string.
Table 9.23. The detail section fields and formatting.
REPORT ELEMENT Values Detail Section Visible, Suppress Blank Lines @Bullet String, Default Alignment Titles.Title String, Default Alignment, Font 10pt Bold, Multiple Lines Titles.Description String, Default Alignment, Multiple Lines Titles.Notes String, Default Alignment, Multiple Lines Titles.Subject String, Default Alignment, Multiple Lines Titles.Comments Memo, Default Alignment, Multiple Lines
- 11. Now add the Company Name group section. Select Insert|Group Section from the Crystal Reports main menu. From the first list box, select Publishers.Company Name. Leave the sorting option set to ascending order, which is the default, and click OK to proceed.
- 12. The first field you'll enter in this group section is the publisher's company name. There is a problem, however, with simply adding the field to the report. Some of the publishers have a long list of books that span more than one page, whereas others have none. It would be nice to be able to reprint the company name at the beginning of the next page if that company's list of books continues from one page to the next. So you'll enter the company name as a formula, testing to see whether this is a continuation. Select Insert|Formula Field from the Crystal Reports main menu, or click the Insert Formula Field button on the toolbar. Name the formula GroupHeaderLater, and enter the following formula. The formula first checks to see whether the preceding company name was null, in which case the name is printed. Otherwise, the new company name is checked to see whether it is the same as the preceding name. If it isn't, you'll start a list for a new company, and print the name. If this is a continuation list, you'll print the name in a formula field that can be put in the page header.
If PreviousIsNull({Publishers.Company Name}) then
{Publishers.Company Name}
else
if Previous ({Publishers.Company Name}) {Publishers.Company Name} then
{Publishers.Company Name}
else
""- 13. Next, enter the publisher's phone number. There might not be a phone number in the file, and you want to label the number as the phone (and later the fax number as the fax); therefore, you should enter a formula field. That way, you won't have the word "Phone:" sitting in the report with nothing else there. Select Insert|Formula Field from the Crystal Reports main menu, or click the Insert Formula Field button on the toolbar. Name the formula PubPhone, and enter the following formula. Place the field to the right of the @GroupHeadLater field.
WhilePrintingRecords;
if Length({Publishers.Telephone}) > 0 then
"Phone: " + {Publishers.Telephone}
- 14. Do the same thing for the fax number. Select Insert|Formula Field from the Crystal Reports main menu, or click the Insert Formula Field button on the toolbar. Name the formula PubFax, and enter the following formula. Place the field to the right of the @PubPhone field.
WhilePrintingRecords;
if Length({Publishers.Fax}) > 0 then
"Fax: " + {Publishers.Fax}
- 15. Double-click on the Address field in the Insert Database Field window and enter it in the group section, or drag it from the Insert Database Field window. Place the field on the line below the @GroupHeaderLater field, about one-fourth of an inch to the right of that field. Drag the right border to make the width of the field the same as the @GroupHeaderLater field.
- 16. The city, state, and zip code should appear on one line, so enter them as a formula field. Select Insert|Formula Field from the Crystal Reports main menu, or click the Insert Formula Field button on the toolbar. Name the formula PubCityStateZip, and enter the following formula. Place the field on the line after the Address field in the group section header.
WhilePrintingRecords;
TrimRight({Publishers.City}) + ", " + TrimRight({Publishers.State}) +
" " + {Publishers.Zip}
- 17. Double-click on the Publisher.Comments field in the Insert Database Field window and enter it in the group section, or drag it from the Insert Database Field window. Place the field on the line below the @PubAddress field, and align the left edge with that field. Drag the right edge of the field to the report's right margin.
- 18. Now, enter the fields in the group footer that show the number of titles the publisher has on the list. Select Insert|Text Field from the main menu, or click the Insert Text Field button on the toolbar. Enter Title(s) in the field, and place the field in the first group section footer line, at the far right margin. Adjust the size of the field so that it is only wide enough to show the text.
- 19. To insert a summary field, you must first highlight the field you want to count. Click on the Title field in the detail section, and then select Insert|Summary from the main menu so that the Insert Summary window appears. Select Count from the first list box, and leave the second list box to the default group section #1 sorting and grouping. The window should look like the one shown in Figure 9.34 just before you click OK to enter the field. Place this field so that it reaches from the left margin to the left edge of the Title(s) text box.
- 20. Format these group section fields as shown in Table 9.24.
Figure 9.34. The Insert Summary window.
Table 9.24. The detail section fields and formatting.
REPORT ELEMENT Values Group header #1: Suppress Blank Lines, Keep Section Together @PubFax String, Default Alignment @PubPhone String, Default Alignment @GroupHeaderLater String, Default Alignment, 12pt Font, Bold Italic, Multiple Lines Publishers.Address String, Default Alignment @PubCityStateZip String, Default Alignment Comments Default Alignment, Multiple Lines Title(s) String, Default Alignment Group footer #1: Suppress Blank Lines Count of Titles.Title Numeric, Default Alignment, Leading Minus, 0 Decimal Places, Rounding: None, Thousands Symbol: `,', Decimal Symbol: `.'
- 21. Now move to the page header and footer. Select Insert|Text Field from the main menu, or click the Insert Text Field button on the toolbar. Enter the text Publisher Titles Detail, and place the field at the upper-left corner of the page header section. Wait to widen the field until you change the font so that Crystal Reports doesn't make the field so wide that you can't adjust the right edge.
- 22. Now, enter a group header to print the publisher's company name if the list of titles continues from the preceding page. Select Insert|Formula Field from the main menu, or click the Insert Formula Field button on the toolbar. Name the formula @GroupHeaderFirst, and enter the following formula. Place the field on the third line of the page header, but don't adjust its width yet.
if Previous({Publishers.Company Name}) = {Publishers.Company Name} _
then
{Publishers.Company Name} + " continued"
else
""- 23. Now, enter a page number field in the page footer section. Select Insert|Formula Field from the main menu, or click the Insert Formula Field button on the toolbar. Name the formula @PageNo, and enter the following formula. Place the field on the third line of the page footer at the far-right edge of the line.
"Page " + ToText(PageNumber, 0)
- 24. Format these page header and footer fields as shown in Table 9.25.
Table 9.25. The page header and footer fields and formatting.
| REPORT ELEMENT | Values |
| Text Field | Publisher Titles Detail, 14 pt Font, Bold |
| @GroupHeaderFirst | String, Default Alignment, 12pt Font, Bold Italic |
| @PageNo | String, Right Alignment |
How It Works
When you run this report, you should see blank lines only where they were intentionally left in the report. Leave margins at the top and bottom of the page because some white space makes a report more easily read and understood.
Two interesting features were used to create this report. The first is the Suppress Blank Lines section formatting option. Using this option means that all you have to do is make sure that no extraneous text appears on a line that otherwise would be blank so that the line is not printed at all. The phone and fax fields were put into formulas rather than separate text fields to make some of the text fields the full (or nearly full) width of the report. This way, if any field is blank, the entire line won't print. If two or more fields could be blank on the same line, all the fields would have to be blank for the line not to print, resulting in a checkerboard effect if some fields contain data and others do not.
The other interesting feature used in this report was a summary field. Crystal Reports automatically calculates the maximum, minimum, count, and distinct count on any fields indicated. This How-To used a summary field to keep count of how many titles each publisher had on the list.
Comments
Most of this How-To has focused on making a report "pretty." Although programming style is tremendously important to how well a system works, good appearances will be remembered by anyone who sees your creations.
9.12 How do I...
Create cross-tab reports with Crystal Reports?
Problem
I use a table that needs to be summarized weekly in a cross-tab style report. It is almost impossible to produce the report in Visual Basic before printing it. Can I use Crystal Reports to produce a cross-tab report from my tables?
Technique
Crystal Reports can analyze data as well as print it. One method is a cross-tab report, which summarizes two or more dimensions of data in tables. In this How-To you will create a marketing analysis report that produces a breakdown of customers by city and by the day of the week they were serviced, giving totals by weekday and by city.
Steps
The steps in this How-To show in detail how to create a cross-tab report. To open and run a report in Crystal Reports, select File|Open from the Crystal main menu, and select the MAILANAL.RPT report file, shown in Figure 9.35. To print the report, click the Print button on the toolbar, or select File|Print from the main menu. To preview the report onscreen, click the Print Preview button on the toolbar, or select File|Print Preview from the Crystal Reports main menu.
- 1. This How-To uses the MAIL.MDB database described in the introduction to this chapter. Start Crystal Reports, and start a new report by clicking the New Report toolbar button or selecting File|New from the main menu. Click on Cross-Tab when the Create New Report Gallery appears.
- 2. Click on Data File when the Create Report Expert appears. Use the dialog box to find the MAIL.MDB Access database file. Click Done to close the dialog box.
Figure 9.35. The Crystal Reports design window for marketing analysis report.
- 3. From the Fields tab, add the Contact, City, State, and Zip fields from the MailingList table.
- 4. On the Style tab, enter Bulk Mail Calculation Report as the title.
- 5. After you select the database file to use for the report, the CrossTab window appears, as shown in Figure 9.36. The layout of this window makes it easy to visualize the final report. You will need to enter the field or formula used for the rows and columns, and then enter a field or formula for the data that is contained in the body of the report. Crystal Reports will then handle all the calculations to produce the report.
Figure 9.36. The Cross Tab tab of the Crystal Reports Create Report Expert.
- 6. Start by double-clicking on the Mailing List table name in the Fields list box so that the fields in the table appear. Drag the Mailing List.CITY field to the Rows cross-tab list. This tells Crystal Reports that a list of cities contained in the file will compose the rows of the report.
- 7. You will want to use the days of the week for the report columns. But first consider the space you have. There will be a column for each day of the week, plus one for the list of city names and another for the total for each city, so there will be nine columns altogether. If you don't abbreviate the days of the week, the data won't fit on a report, or part of the names will get cut off. Unfortunately, Crystal Reports can't print text vertically, so you should use a formula field to print only the first three characters of the day's name.
- Click the New Formula button, and enter DayOfWeek as the formula name. Enter the following formula, which tells Crystal Reports to use a substring consisting of the first three characters of the name. Drag the new formula to the Columns cross-tab list.
{Mailing List.Week Day}[1 to 3]- 8. The last step is to tell Crystal Reports what information will make up the body of the table. You just want to count contacts, so drag the Contact field to the Summarized field. (The default function for the summarized field is Count, so you won't need to make a change. You'll see in a moment how to change that.) That's all there is to designing the cross-tab layout, so click Preview Report to see the results so far.
- 9. All that is left to do is to reposition and resize the cross-tab fields so that the font can be read, and to enter a report title and page number. Start by setting the four margins of the report to 0.5 inch by selecting File|Page Margins from the main menu.
- 10. Next, move the cross-tab grid to the left so that there will be enough room on the page for all nine columns in the report. Use the mouse to drag the vertical line on the left side of the DayOfWeek column to the left until it is about 1 to 1.5 inches from the left margin, leaving enough room for the city names to print. The space to the right of the Total column will contain the day-of-the-week columns. Narrow the Week Day column to about three-fourths of an inch and the Total column to about one-half of an inch. You'll need to set the spacings by trial and error when all the report elements have been entered into the report.
- 11. To enter a report heading, select Insert|Text Field from the main menu, or click the Insert Text Field button on the toolbar. Enter Marketing Analysis by City and Day of Week, and place the field in the upper-left position in the page header. Change the font size to 14 point by using the list of font sizes in the font toolbar, and click the B in the toolbar to make the font bold.
- 12. Enter a page number field on the page footer. First, show the Page Footer section by right-clicking in the gray left margin of the design window and selecting Show/Hide Sections from the menu. Show the Page Footer section. Then, select Insert|Formula Field from the main menu, or click the Insert Formula Field button on the toolbar. Name the formula PageNo, and enter the following formula. Place the field in the lower-left portion of the page footer, against the left margin.
"Page " + ToText(PageNumber, 0)
- 13. If you want to make changes to the cross-tab design of the report, use the right mouse button to click in the gray area to the left of the page edge in the Cross-Tab detail section, and select Cross-Tab Layout from the popup menu. Then make any changes you want in the same Cross-Tab layout window you used to create the report. To use a summarization function other than Count, highlight the grid cell at the intersection of the City row and Week Day column, and select Edit|Summary Field from the main menu. Figure 9.37 shows the options available. The options for any given report depend on the type of field data selected for the Summarization field in the Cross-Tab layout window.
Figure 9.37. Cross-tab summarization function selections.
How It Works
Creating a cross-tab report in Crystal Reports is an almost trivial task after you understand how to present data using this type of report. The table used in this How-To consists of names, addresses, dates, and week days. By using a cross-tab report, you transform this data into a breakdown of clients by week, day, and geographic location. In fact, the hardest part of producing the report is to size and position the report elements so that all the fields show their data and everything fits on the page.
Creating the report is easy, partially because the same record selection, sorting, and formatting options are available with cross-tab reports as with other report formats used in this chapter.
Comments
One rather serious flaw of Crystal Reports is that any fields or columns that are off the right side of the report page do not print and cannot be reached in the report design window. You can change the page orientation to landscape by using File|Printer Setup, but even that orientation is not wide enough for some reports.
9.13 How do I...
Generate reports using user-entered variables?
Problem
Each time I print a report, I need to change elements of the report, such as the record sort order, the heading, and the name of the person running the report. How can I run a report through a Visual Basic application and change selected elements of the report?
Technique
This How-To takes advantage of three of the properties that the Crystal Reports custom control provides to modify the conditions under which a report is printed at runtime in a Visual Basic application. It is simply a matter of setting the properties of the control.
Steps
Load and run the Visual Basic application MAILLIST.VBP. From the Print Sorted Mailing List window (see Figure 9.38), select a report type to print (City, State, or Zip) and either enter a particular value to use in selecting records or leave the field blank to include all records. Select a page number format to be used, and then click the Print Report button to preview the report.
- 1. Start by creating a simple report that you can modify when printing through Visual Basic (see Figure 9.39). Start Crystal Reports, and start a new report by clicking the New Report toolbar button or selecting File | New from the main menu. Click on Standard when the Create New Report Gallery appears.
Figure 9.38. The Print Sorted Mailing List window (frmMailList).
Figure 9.39. The Crystal Reports design window for a mailing list report.
- 2. Click on Data File when the Create Report Expert appears. Use the dialog box to find the MAIL.MDB Access database file. Click Done to close the dialog box.
- 3. From the Fields tab, add the Contact, City, State, Zip, and Week Day fields from the Mailing List table.
- 4. Preview the report and save the report file, calling it MAILLIST.RPT.
- 5. Because you want to be able to change the report heading from the Visual Basic application, enter the heading as a formula with a default value. That way, if the application doesn't change the title, something relatively meaningful will print. Select Insert | Text Field from the Crystal Reports main menu, or click the Insert Text Field button on the toolbar. Name the formula field ReportTitle, and enter this formula:
`Mailing List'
- Format the title to be 14-point font, bold by using the font tool at the bottom of the Crystal Reports design window.
- 6. Insert the page number formula field @PageFooter by selecting Insert | Formula Filed from the main menu or clicking the Insert Formula Field button on the toolbar. Enter this formula:
"Page " + ToText(PageNumber,0)
- 7. That's all there is to the report. Note that you didn't set up any sorting at all, nor any group sections. Next, you'll see how to make changes through a Visual Basic application. Remember to save the report file, calling it MAILLIST.RPT.
- 8. Start Visual Basic and create a new project in your work area. Save the default form as MAILLIST.FRM, and save the project as MAILLIST.VBP. Select Project | Components from the Visual Basic main menu, and make sure that the Crystal Reports control is selected.
- 9. Place the controls on the form as shown in Figure 9.38, and set the properties as shown in Table 9.26. Note that the Crystal Reports control is invisible at runtime, so place it anywhere that is convenient. Note also that the three page-number-format option buttons make up a control array. The label control lblInstruction and text box txtValue have their Visible property set to False; place them in the open area below the Report Type list box. Note that you are "hard coding" the name of the report to use in the properties of the Crystal Reports control; change its location to wherever the report is located on your drive.
Table 9.26. Objects and properties for MAILLIST.FRM.
OBJECT PROPERTY Setting Form Name frmMailList Caption "Print Sorted Mailing List" CommandButton Name cmdQuit Caption "&Quit" CommandButton Name cmdReport Caption "&Print Report" Default -1 `True TextBox Name txtValue Visible 0 `False Frame Name Frame1 Caption "Select Page Number Format" OptionButton Name optPageNoType Caption "page &one" OptionButton Name optPageNoType Caption "&1" OptionButton Name optPageNoType Caption "&Page 1" Value -1 `True PictureBox Name Picture1 Align 2 `Align Bottom Label Name lblStatus ComboBox Name lstReportType Style 2 `Dropdown List Label Name Label1 Caption "&Select Report Type:" Label Name lblInstruction AutoSize -1 `True Visible 0 `False CrystalReport Name CrystalReport1 ReportFileName "C:\VB\REPORT\MAILLIST.RPT" (Your local path) Destination 0 `To Print Preview Window SelectionFormula "" GroupSelectionFormula "" Connect "" UserName ""
- 10. Enter the following code in the Load event procedure of the form. This procedure initializes the lstReportType list box to the types of reports available and sets the default page format as "Page 1."
Private Sub Form_Load()
`Load the lstReportType list box
lstReportType.Clear
lstReportType.AddItem "City"
lstReportType.AddItem "State"
lstReportType.AddItem "Zip"
lblStatus.Caption = "Select a report type."
`Set the report file name
CrystalReport1.ReportFileName = App.Path & "\MAILLIST.RPT"
`Set the initial value of the page format
optPageNoType_Click (0)
End Sub
- 11. Add the following code to the lstReportType Click event procedure. When a particular report type is selected, the text of the lblInstruction label is set to prompt for the appropriate value, and it and the txtValue text box are made visible. If the text of the lstReportType field is empty, the Visible property of the two controls is set to False.
Private Sub lstReportType_Click()
If Len(lstReportType.Text) Then
Select Case lstReportType.Text
Case "City"
lblInstruction.Caption = "&Enter the City name:"
lblStatus.Caption = _
"Enter a city name or blank for all."
Case "State"
lblInstruction.Caption = "&Enter the State name:"
lblStatus.Caption = _
"Enter a state name or blank for all."
Case "Zip"
lblInstruction.Caption = "&Enter the Zip Code:"
lblStatus.Caption = _
"Enter a zip code or blank for all."
End Select
txtValue.Text = ""
lblInstruction.Visible = True
txtValue.Visible = True
txtValue.SetFocus
Else
lblInstruction.Visible = False
txtValue.Text = ""
txtValue.Visible = False
End If
End Sub
- 12. Add the following code to the Click event procedure of the optPageNoType option button control array. Recall that you entered the page number in the report footer as a formula field @PageFooter. You also set the default formula to print a page number in the form "page one." Any time the report is printed through the Crystal Reports program, the page number appears in the same form. This Visual Basic program simply sets the @PageFooter formula to whatever formula you want. In this case, one of the three formats shown in the option button group, "Page 1," "1," or "page one."
Private Sub optPageNoType_Click(Index As Integer)
`Set the page number format
Select Case Index
Case 0
CrystalReport1.Formulas(1) = "PageFooter= `Page ` + _
ToText(PageNumber, 0)"
Case 1
CrystalReport1.Formulas(1) = "PageFooter= _
ToText(PageNumber, 0)"
End Select
End Sub
- 13. Add the following code to the Click event procedure of the cmdReport command button. Now that the relevant properties of the report have been set, it is time to actually print it. In contrast to previous How-To's, a different technique for setting the properties is demonstrated here. They are all set at once at print time, instead of as they are changed on the form (with the exception of the page number format). Use whichever technique works best in the context of your application.
- Three different types of reports can be printed using this program:
- For one city or all cities sorted by city and zip.
- For one state or all states sorted by state, city, and zip.
- For one zip code or all zip codes sorted by zip.
- Any of the initial digits of a zip code can be entered to obtain a zip code list; the program selects all the zip codes that begin with those starting characters.
- For each selected report type, this procedure first sets the SelectionFormula, which filters the records for the particular city, state, or zip code. If no value was entered, the records for all of that particular group are filtered. Then the formulas you built into the report are reset as appropriate. In the case of a city report, for example, the report title formula @ReportTitle is set to "Mailing list for City of <city name>" or "Full City Mailing" if no city name was entered. Finally, the SortFields array of fields for sorting are set. In each case, three elements of the array are set, clearing any elements that are not used for the particular report. If you didn't clear unused array elements, they would remain set for the next report unless explicitly overwritten.
Private Sub cmdReport_Click()
Dim ZipDigits As String
lblStatus.Caption = "Setting report options. Please wait..."
DoEvents
Select Case lstReportType.Text
Case "City"
`Set the filter and title for the report
If Len(txtValue) Then
CrystalReport1.SelectionFormula = _
"{Mailing LIst.CITY}= `" _
& txtValue & "`"
CrystalReport1.Formulas(0) = _
"ReportTitle= `Mailing List for City of " & _
txtValue & "`"
Else
CrystalReport1.SelectionFormula = ""
CrystalReport1.Formulas(0) = _
"ReportTitle= `Full City Mailing List'"
End If
`Set the sort order and clear second element
CrystalReport1.SortFields(0) = "+{Mailing LIst.CITY}"
CrystalReport1.SortFields(1) = "+{Mailing LIst.ZIP}"
CrystalReport1.SortFields(2) = ""
Case "State"
`Set the filter and title for the report
If Len(txtValue) Then
CrystalReport1.SelectionFormula = _
"{Mailing LIst.STATE}= `" & txtValue & "`"
CrystalReport1.Formulas(0) = _
"ReportTitle= `Mailing List for State of " & _
txtValue & "`"
Else
CrystalReport1.SelectionFormula = _
"{Mailing LIst.STATE}= {Mailing LIst.STATE}"
CrystalReport1.Formulas(0) = _
"ReportTitle= `Full State Mailing List'"
End If
`Set the sort order
CrystalReport1.SortFields(0) = "+{Mailing LIst.STATE}"
CrystalReport1.SortFields(1) = "+{Mailing LIst.CITY}"
CrystalReport1.SortFields(2) = "+{Mailing LIst.ZIP}"
Case "Zip"
`Set the filter and title for the report
If Len(txtValue) Then
ZipDigits = Trim(Str(Len(txtValue)))
CrystalReport1.SelectionFormula = _
"{Mailing LIst.ZIP}[1 to " _
& ZipDigits & "]= `" & txtValue & "`"
CrystalReport1.Formulas(0) = _
"ReportTitle= `Mailing List for Zip Code " & _
txtValue & "`"
Else
CrystalReport1.SelectionFormula = _
"{Mailing LIst.ZIP}= {Mailing LIst.ZIP}"
CrystalReport1.Formulas(0) = _
"ReportTitle= `Full Zip Code Mailing List'"
End If
`Set the sort order
CrystalReport1.SortFields(0) = "+{Mailing LIst.ZIP}"
CrystalReport1.SortFields(1) = ""
CrystalReport1.SortFields(2) = ""
End Select
`Print the report
lblStatus.Caption = "Printing the report. Please wait..."
DoEvents
CrystalReport1.Action = 1
lblStatus.Caption = "Enter new selections and print or quit."
DoEvents
End Sub
- 14. Add the following code to the Click event of the cmdQuit command button to provide an exit point from the program:
Private Sub cmdQuit_Click()
End
End Sub
How It Works
This How-To takes advantage of properties of the Crystal Reports custom control to modify the design of the report at runtime. These changes stay in effect only for the lifetime of the Crystal Reports custom control in the form, and they are not saved in the report file itself. This means that if the form with the control is unloaded, the options revert to the settings in the report until you explicitly set them again. This "stickiness" trait of the control makes it critical to set any unused formulas or fields to a null string if they aren't required for the current operation.
If you specified any particular sorting order in the report design, setting the particular formula or field through your application would replace that formula for this report. So if a report has five formula fields and you change only two, the other three fields remain as they were set in the report.
Three formula and field properties of the custom control were used in this How-To:
- SelectionFormula: There is one selection formula in each report. But because it is a Crystal Reports formula, it can be as complex as you care to make it. In this How-To, the City, State, and Zip fields of the report are set to the particular values entered; if no values are entered, they are set to empty strings so that all records print.
- Formulas: Any formula contained in the report file can be changed at runtime through the Crystal Reports control. In this How-To, the @ReportTitle and @PageFooter formulas were changed.
- SortFields: This property resets the record sort order of the report. A similar GroupSortFields property wasn't used in this program, but it can be used to reset the sorting order of any groups in the report. Both of these properties are implemented as arrays so that fields entered into the array are sorted in the order of the 0th element, 1st element, and so on.
It is important to remember that the format of any formulas set in Visual Basic must be in the Crystal Reports format of formulas, not Visual Basic's statement format. This requires placing the entire formula in "double" quotation marks and placing any literals used in the formula in "single" quotation marks. This is the reason for the convoluted form of this setting for the Formulas(0) property:
"ReportTitle= `Mailing List for City of " & txtValue & "`"
Comments
This How-To has shown the power of Crystal Reports to improve your development by setting different OCX control properties. The creative combination of multiple control properties can help you deliver high-quality results to your customers.
Update Contact :
No Wa/Telepon (puat) : 085267792168
No Wa/Telepon (fajar) : 085369237896
Email : Fajarudinsidik@gmail.com
No Wa/Telepon (puat) : 085267792168
No Wa/Telepon (fajar) : 085369237896
Email: Fajarudinsidik@gmail.com
atau Kirimkan Private messanger melalui email dengan klik tombol order dibawah ini :
