CODE-VB6.BLOGSPOT.COM

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

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

---

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.

---

Table 9.1. Tables from BIBLIO.MDB.

Table 9.2. Tables from CRYSTAL.MDB.

Table 9.3. Mailing List table in MAIL.MDB.

Table 9.4. Sample data in Mailing List table in MAIL.MDB.

9.1 Create a report using Microsoft Data Report Designer

9.2 Add Calculated Fields to a Microsoft Data Report

9.3 Select if the Microsoft Data Report will be displayed, printed, or export using Visual Basic code

9.Have a Report Displayed Based on Criteria I Choose Using a Microsoft Data Report

9.5 Determine Which Records Will Be Printed Using Crystal Reports

9.6 Create Subtotals and Other Calculated Fields Using Crystal Reports

9.7 Control the Order in Which Records Will Be Printed Using Crystal Reports

9.8 Print Labels Using Crystal Reports

9.9 Create and Print Form Letters with Crystal Reports

9.10 Print Field Data Without Extra Spaces Between the Fields with Crystal Reports

9.11 Prevent Blank Lines from Being Printed when a Field Contains No Data Using Crystal Reports

9.12 Create Cross-Tab Reports with Crystal Reports

9.13 Generate Reports Using User-Entered Variables with Crystal Reports

9.1 How Do I...

Create a report using Microsoft Data Report Designer?

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.

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.

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

9.2 How Do I...

Add Calculated Fields to my Microsoft Data 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.

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.

Table 9.8. rptFunction control functions description.

9.3 How Do I...

Select whether the Microsoft Data Report will be displayed, printed, or exported using Visual Basic code?

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.

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.

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.

ExportReport(ExportFormat, filename, Overwrite, ShowDialog, Range, PageFrom, PageTo)

9.4 How Do I...

Have Visual Basic generate a Microsoft Data Report based on criteria I choose?

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´ = ?)

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.

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.

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?

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.

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

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.

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.

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.

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.

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

"{Authors.Year Born} in " & txtStart.Text & " to " & txtEnd.Text

"{Authors.Year Born} in 1940 to 1950"

"{Market.State} = `" & stateName & "`"

"{Market.State} = `California'"

"{Market.State} = ` " & stateName & " `"

"{Market.State} = ` California `"

9.6 How do I...

Create subtotals and other calculated fields using Crystal Reports?

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.

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.

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.

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.

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.

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.

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

{database.field}[1 to 5]

9.7 How do I...

Control the order in which records will be printed using Crystal Reports?

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

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}"

9.8 How do I...

Print labels using Crystal Reports?

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.

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.

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.

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.

9.9 How do I...

Create and print form letters using Crystal Reports?

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.

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.

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

9.10 How do I...

Print field data without extra spaces between the fields using Crystal Reports?

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.

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.

Table 9.21. Useful Crystal Reports functions for manipulating strings.

Table 9.22. Useful Crystal Reports operators for manipulating strings.

{Company.Address} = "1245 East Elm Lane"

{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"

• 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

9.11 How do I...

Prevent blank lines from being printed when a field contains no data using Crystal Reports?

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.

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.

9.12 How do I...

Create cross-tab reports with Crystal Reports?

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.

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.

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.

9.13 How do I...

Generate reports using user-entered variables?

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.

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

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

"ReportTitle= `Mailing List for City of " & txtValue & "`"