
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:
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:
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 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 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.
| 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.
| 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 |
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.
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.
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.
Have a Data Report display data based on a parameter passed to a SQL-based query contained in a Data Environment object.
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.
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!
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.
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.
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.
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.
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.
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.
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.
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.
Figure 9.3. The Data View Window.
Figure 9.4. The Command1 Properties dialog box.
Table 9.5. Properties for drBasic.dsr AuthorsDR.
OBJECT Property Setting DataReport Name drBasic DataMember Command1 DataSource DataEnviroment1
Figure 9.5. Arranged report objects.
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
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.
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.
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"
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.
| 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.
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.
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
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
Private Sub cmdPreview_Click()
DataReport1.StartUpPosition = 0
DataReport1.WindowState = 2
DataReport1.Show
End Sub
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
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
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
Private Sub cmdExit_Click()
End
End Sub
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"
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.
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.
SELECT Author, ´Year Born´ FROM Authors WHERE (´Year Born´ = ?)
Figure 9.10. Data View window displaying SQL Statement.
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
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
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:
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.
Figure 9.14. The New Report Gallery.
Figure 9.15. Automatic table links defined by Crystal Reports.
Table 9.13. Computer author report tables and fields.
TABLE Field Authors Author Authors Year Born Titles Title Titles Year Published
Figure 9.16. Author report total fields.
Figure 9.17. Author report design view.
Figure 9.18. The Crystal Format Number dialog box.
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:"
Figure 9.19. Crystal Report control property pages.
Table 9.15. Custom properties.
PROPERTY Setting ReportFileName "" Destination To Window
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
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
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.Text
If 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.
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:
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.
Figure 9.20. Print preview of bulk mail report.
Figure 9.21. Crystal Reports design window for bulk mail reports.
WhilePrintingRecords; NumberVar Zip5Count; Zip5Count := Zip5Count + 1;
Figure 9.22. The Zip5Increment formula entry.
WhilePrintingRecords; NumberVar Zip5Count; Zip5Count := Zip5Count + 1;
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;
WhilePrintingRecords;
NumberVar StateCount;
if StateCount >= 10 then
"Total Count for " + {Mailing List.STATE} + ": "
else
""
WhilePrintingRecords; NumberVar StateCount; StateCount := 0;
Figure 9.23. Formatting for zero decimal places.
{Mailing List.ZIP}[1 to 3]
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;
WhilePrintingRecords;
NumberVar Zip3Count;
if Zip3Count >= 10 then
"Total Count for " + {Mailing LIst.ZIP}[1 to 3] + ": "
else
""
WhilePrintingRecords; NumberVar Zip3Count; Zip3Count := 0;
{Mailing List.ZIP}[1 to 5]
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;
WhilePrintingRecords;
NumberVar Zip5Count;
if Zip5Count >= 10 then
"Total Zip (5) Count for " + {Mailing LIst.ZIP}[1 to 5] + ": "
else
""
WhilePrintingRecords; NumberVar Zip5Count; Zip5Count := 0;
Figure 9.24. Setting the record sort order.
WhilePrintingRecords; NumberVar Zip5UnitCost; NumberVar Zip3UnitCost; NumberVar StateUnitCost; NumberVar FirstClassUnitCost; Zip5UnitCost := .191; Zip3UnitCost := .191; StateUnitCost := .256; FirstClassUnitCost := .320;
WhilePrintingRecords; NumberVar FirstClassCount; NumberVar FirstClassCost; NumberVar FirstClassUnitCost; FirstClassCost := FirstClassCount * FirstClassUnitCost; FirstClassCount;
WhilePrintingRecords; NumberVar FirstClassCount; "Total First Class Count: ";
WhilePrintingRecords; "Zip (3) Totals:"
WhilePrintingRecords; NumberVar Zip3TotalCount; Zip3TotalCount;
WhilePrintingRecords; NumberVar Zip3Cost; Zip3Cost;
WhilePrintingRecords; NumberVar Zip5Cost; "Zip (5) Totals:";
WhilePrintingRecords; NumberVar Zip5TotalCount; Zip5TotalCount;
WhilePrintingRecords; NumberVar Zip5Cost; Zip5Cost;
WhilePrintingRecords; "State Totals:";
WhilePrintingRecords; NumberVar StateTotalCount; StateTotalCount;
WhilePrintingRecords; NumberVar StateCost; StateCost;
WhilePrintingRecords; "First Class Totals:";
WhilePrintingRecords; NumberVar FirstClassCount; FirstClassCount;
WhilePrintingRecords; NumberVar FirstClassCost; FirstClassCost;
WhilePrintingRecords; "Mailing Totals:";
WhilePrintingRecords; NumberVar TotalCount; TotalCount;
WhilePrintingRecords; NumberVar Zip5Cost; NumberVar Zip3Cost; NumberVar StateCost; NumberVar FirstClassCost; NumberVar TotalCost; TotalCost := Zip5Cost + Zip3Cost + StateCost + FirstClassCost; TotalCost;
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:
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.
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.
Table 9.15. Computer author report tables and fields.
TABLE Field Publishers State Publishers Zip Publishers Telephone Publishers City Publishers Name
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
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
Private Sub cmdQuit_Click()
Unload Me
End Sub
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.
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.
Figure 9.27. Crystal Reports design view for SHIPLBL.RPT.
TrimRight({Mailing LIst.CITY}) + ", " + TrimRight({Mailing LIst.STATE}) _
+ " " + {Mailing LIst.ZIP}
Figure 9.28. The Create Report Expert dialog box.
Figure 9.29. The Graphic format dialog box.
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.
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.
Figure 9.30. Crystal Reports design view for FORMLTR.RPT.
Figure 9.31. Crystal Reports blank report design view.
TrimRight({Mailing LIst.CITY}) + ", " + TrimRight({Mailing _
LIst.STATE})
+ " " + {Mailing LIst.ZIP}
"Dear " + TrimRight({Mailing LIst.CONTACT}) + ","
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.
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.
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."
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
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
WhilePrintingRecords;
BooleanVar FirstPage;
if FirstPage then
FirstPage := False
else
FirstPage := True;
if FirstPage then
"Slick Willy Sales and Aerobics Training";
WhilePrintingRecords;
BooleanVar FirstPage;
if FirstPage = True then
"One Pennsylvania Avenue, Nashville, Tennessee 80104";
WhilePrintingRecords;
BooleanVar FirstPage;
if FirstPage = True then
"(800) 555-9875";
WhilePrintingRecords;
BooleanVar FirstPage;
if FirstPage = False then
{Mailing LIst.CONTACT};
WhilePrintingRecords;
BooleanVar FirstPage;
if FirstPage = False then
Today;
WhilePrintingRecords;
BooleanVar FirstPage;
if FirstPage = False then
"Page 2";
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
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:
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"?
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.
Figure 9.32. Crystal Reports design view for CUSTDIR.RPT.
WhilePrintingRecords;
StringVar BuiltStr;
if Length({COMPANY.FORMADDRES}) > 0 then
BuiltStr := TrimRight({COMPANY.FORMADDRES}) + " ";
BuiltStr := BuiltStr + TrimRight({COMPANY.CONTACTFN}) + " "
+ TrimRight({COMPANY.CONTACTLN});
TrimRight({COMPANY.CUSTNUM})
WhilePrintingRecords;
TrimRight({COMPANY.ADDRESS}) + ", " +
TrimRight({COMPANY.CITY}) + ", " +
TrimRight({COMPANY.STATE}) + " " +
{COMPANY.ZIP_POSTAL}
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
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
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.
| 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 |
| 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:
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.
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.
if Not IsNull({Titles.Title}) then
"*"
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
If PreviousIsNull({Publishers.Company Name}) then
{Publishers.Company Name}
else
if Previous ({Publishers.Company Name}) {Publishers.Company Name} then
{Publishers.Company Name}
else
""
WhilePrintingRecords;
if Length({Publishers.Telephone}) > 0 then
"Phone: " + {Publishers.Telephone}
WhilePrintingRecords;
if Length({Publishers.Fax}) > 0 then
"Fax: " + {Publishers.Fax}
WhilePrintingRecords;
TrimRight({Publishers.City}) + ", " + TrimRight({Publishers.State}) +
" " + {Publishers.Zip}
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: `.'
if Previous({Publishers.Company Name}) = {Publishers.Company Name} _
then
{Publishers.Company Name} + " continued"
else
""
"Page " + ToText(PageNumber, 0)
| 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.
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.
Figure 9.35. The Crystal Reports design window for marketing analysis report.
Figure 9.36. The Cross Tab tab of the Crystal Reports Create Report Expert.
{Mailing List.Week Day}[1 to 3]
"Page " + ToText(PageNumber, 0)
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.
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.
Figure 9.38. The Print Sorted Mailing List window (frmMailList).
Figure 9.39. The Crystal Reports design window for a mailing list report.
`Mailing List'
"Page " + ToText(PageNumber,0)
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 ""
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
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
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
- 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.
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
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:
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.
© Copyright, Macmillan Computer Publishing. All rights reserved.