Creating Reports using Crystal Reports

Creating Reports using Crystal Reports

Crystal Report Writer is a report generation tool. It is used to generate simple to slightly complex reports.  However, with Visual Basic 6.0 onwards you also get Data Reports (covered in chapter 20). So you can generate a report either with CRW (Crystal Report Writer) or with Data Report.  CRW is not a product from Microsoft. It is from Segate Software. A report created using Crystal Reports is accessed from a Visual Basic project using an ActiveX Control  – Crystal Report Control.


The best part of Crystal Reports is its user friendliness. Even an end-user will be able to generate a report in a few minutes.  It is also powerful enough to enable application developer to generate moderate to complex reports.


Let us generate a simple report to understand how to use elementary part of crystal reports.


A simple sample report

We will generate a report to display the details of all the publishers using PUBLISHERS table of BIBLIO.MDB. Here are the major steps in creating the report:


  • Start CRW and select Tabular
  • Select the data to be used in the report
  • Arrange the fields in the way you want to display them
  • See the preview of the report
  • Save report
  • Run report from Visual Basic Project using ActiveX Control.


Starting CRW

First make sure CRW is installed in your machine. If it is not installed, install it from Visual Studio CDs. Note that by default CRW is not installed. So install it from Common\tools\Visual Basic\crysrept directory of the Visual studio CD.


If it is installed, then you start CRW as follows:


  1. Click on Start button
  2. Select Programs-> Microsoft Visual Studio 6.0 -> Crystal Reports. In which group crystal reports is placed depends upon the group selection at the time of installation.
  3. If your are prompted to enter registration details, cancel it or register and then click on Ok.
  4. The initial screen of Crystal Reports will be displayed.


To create a new Report:


Take the following steps to create a new report that displays the details of publishers.


  1. When you are in Crystal Reports, select File menu and New
  2. Crystal Reports displays Create New Report window (figure 22.1) containing various report styles.
  3. Select Listing type


Figure 22.1: Create New Report dialog displaying available report styles.


  1. Crystal Reports displays another window (figure 22.2) where you can select the data file or ODBC data source to be used to retrieve data.

Figure 22.2: Tables tab of Crystal Report Expert.


  1. Click on Data File button to get Choose Database File
  2. Select BIBLIO.MDB and either double click on the file or click on Add
  3. Once tables are listed in Crystal Reports Expert, then click on Done to close Choose Database File
  4. Crystal Reports Expert displays list of tables and the links between the tables in Links
  5. Select Fields tab and select fields (shown in figure 22.3) that you want to include in the report from PUBLISHERS table.
  6. Click on Preview Sample When Crystal Reports prompts you to enter number of records to be displayed Select First 100 Records radio button and click on Ok.
  7. Crystal Reports displays the report, but it will be too small to see.
  8. Select Report menu and Zoom option to increase the size of the characters.

At this stage, your report should look like figure 22.4.

  1. Now you are in Preview mode. Select Design tab at the upper –left corner to get into design mode.


Figure 22.3:Fields tab of Crystal Report Expert.

Figure 22.4: Preview of the report.


Sections in Report

Each report contains a few sections. The number of sections may vary from report to report. But the following are the general and common sections.




Title Displays the title of the report.  Information placed here is displayed only once at the top of the report.
Page Header This is displayed at the top of each printed page.
Details Data placed here is displayed for each row of the data that report has retrieved.
Page Footer Displays the information at the bottom of each printed page.
Summary Displays the information only on the last page of the report.

Table 22.1: Sections of Crystal Report.


Note: When we insert a group into report, the group will contain two sections – one for Header of the group and another for footer of the group. More on this later.


Hiding/showing a section

Each section can be hidden or displayed by selecting context menu (popup menu invoked using right button) and then selecting Hide Section option, when section is displayed or Show Section option, when section is hidden.


Enhancing the sample report

Let us enhance the sample report as follows:


  1. Change format of current date and add text “Date” before the date. Remember date is automatically placed at the upper left corner.
  2. Add a title to report.

Get into design mode by selecting Design table before you start enhancing report.

To change date format:


  1. Select date that is placed in  Page Header section.
  2. Invoke context menu of the field using right button.
  3. Select Change Format… option
  4. When Format Date (figure 22.5) dialog is displayed, change Month to March, Day to 01 and Year to 1999.
  5. Select DMY radio button to display date in Day month and year format.
  6. Click on Ok to close dialog box.

Figure 22.5: Format Date dialog used to format date type data.

  1. Move date towareds right to create space for text ‘Date:’.
  2. Select Insert -> Text Field
  3. Enter Date in text box.
  4. Click on Accept
  5. Then a small rectangle moves along with mouse pointer. Move it on the left of date and click there. Text “Date:” is placed on the left of date field.

To add a title to report:

  1. Select Insert-> Text Field
  2. Enter “Publishers Details” as the text and place the field in Title
  3. Change the following font attributes for the title using Change Font options of context menu.

Font name             Arial

Font Style              Bold

Size                        16

Color                     Maroon

Underline              Check on.

To add lines around headings:

  1. Select Line tool in toolbar.
  2. Drag a line above the headings
  3. Drag a line below the headings.

To save report:

  1. Select File -> Save option
  2. When Crystal Reports displays Save As dialog box, enter the name of the report file – PubDet.

Note: The default extension for report files is .RPT

That’s all that we have for a simple report.

Creating a complex report

In this section, we will see how to generate a complex report that has following characteristics.

  • Takes data from Publishers and Titles tables of BIBLIO.MDB. That means it takes data from two linked tables.
  • Groups data by Pubid
  • Displays count of books for each publisher
  • Displays age of the book since release in years
  • Filters records and takes only those records where Year Published is after 1980.

Here is the step-by-step procedure to create this report:

  1. Create a new report using File-> New
  2. Select Standard as the type of the report
  3. Select BIBLIO.MDB as the data files
  4. Select the following fields from the specified tables.
Table field
Publishers Pubid
Titles Title
  Year Published


  1. Select Sort tab in Create Report Expert
  2. Select PubID as the field to be used for grouping and sorting.
  3. Select Total tab
  4. From Total field list box remove all by selecting each and then clicking on <-Remove By default CRW adds a total field for each numeric field in the report.
  5. Add Titles.ISBN to Total Fields and change summary function to Count. This will contain the count of books for each publisher.
  6. Click on Select tab and select Year published and add it to Select Fields.
  7. Change condition to greater than and enter value 1980.
  8. Click on Style tab and enter ‘Publisher-wise Titles Report’ as the title.
  9. Leave Standard as the style of the report.
  10. Click on Preview Sample button and select First 100 records radio button. This will show the data of the report in preview mode.

Enhancing the report

Crystal Reports has created a report with one group on PubID, which contains two sections – one is group header and another one is group footer. Apart from these groups you anyway have groups such as Details.

But the report is not quite the one that we want to have. So let us modify the report by following the steps given below.

  1. Remove PubID field from details section.
  2. Pull Details section down to create more space for group header.
  3. Move Publishers.Name and Publishers.City into group header and arrange them one below other (see figure 22.7).
  4. Move fields in details section towards left and place heading below details of publisher (see figure 22.7).
  5. Delete Grand Total section using Delete Section option from Context menu.
  6. Move Page number field  on to the right of date field in page header section.
  7. Hide Page Footer section using Hide Section option of context menu.

Adding a formula to the report

A formula is used to display a calculated value. In our report, we need to display the age of each title. The age of the title is; current year – year published.

To create a formula for age:

  1. Select Insert -> Formula Field
  2. Enter Age as the name of the formula and click on Ok in Insert Formula
  3. In Edit Formula window enter the following expression in Formula text (see figure 22.6).

Year(today) – {Titles.Year Published}

  1. Click on Accept to accept the formula and place the rectangle on the right of ISBN in details section.
  2. Select Change Format option from Context menu of Age field and change Decimals to 1 so that only integer portion is displayed and decimal portion is not displayed.
  3. Change Font Size of all the fields to 11.

At the end of all these steps report designer should look like figure 22.7.

  1. Click on Preview tab to display report.

The report should look like the one shown in figure 22.8

Figure 22.6: Formula editor to enter expression for formula.

Save the report using File->Save and enter PUBTITILES as the name of the report.

Figure 22.7: Final report at design mode.

Figure 22.8: Report at runtime.


Creating a cross tabulation report

A cross tabulation report is a report that has three sets of values. One set is displayed on the left, another set is displayed at the top and the third set is displayed at the intersection of first two sets. Let us generate a cross tabulation report using Crystal Reports. You will be surprised to know how simple it is to generate a cross tabulation report in Crystal Reports.

The following are the steps to generate cross tabulation report to display Year, Pubid and number of books published in that year by the publisher.

  1. Create new report using File -> New
  2. Select Crosstab as the type of the report.
  3. Select BIBLIO.MDB as the datafile.
  4. Click on Cross Tab tab and drag fields as follows (figure 22.9).

Rows                                  titles.year published

Columns                            titles.pubid

Summarized Field              titles.title

Figure 22.9: Cross-tab tab of Create Report Expert.


  1. Click on Preview report button to display the report.


Figure 22.10, shows report in design mode. You have to format report a bit to get the display in the way you want.

Figure 22.10: Cross-tab report at design mode.

  1. Save the report using File-> Save and enter PUBYEARCOUNT as the name of the report.


Invoking report from Visual Basic form

In order to invoke a report generated using Crystal Reports from a form in Visual Basic we need to use Crystal Report Control 4.6 ActiveX control. This ActiveX control allows you to invoke the named report from a form. It also allows you to send a condition to report so that the data is filtered before report is generated.

We will create a sample form to invoke all three reports created in this chapter – PUBDET, PUBTITLES and PUBYEARCOUNT.

  1. Start a new project using File -> New Project and select Standard EXE as the type of the project.
  2. Load Crystal Report Control 4.6 ActiveX control using Project -> Components.
  3. Place Crystal Report Control on the form. The default name of the control is CrystalReport1.
  4. Add three command buttons on the form as show in figure 22.11.
  5. Change the following properties of the three command buttons.


Object Property


Command1 Name CmdPubDet
  Caption Publishers Details
Command2 Name CmdPubTitles
  Caption Publisher-wise Titles
Command3 Name CmdPubYearCount
  Caption Publisher x Year Book Count
Form Caption Calling Crystal Report Demo


Figure 22.11: Form to invoke reports at design time.


Write the following code for command buttons.

Private Sub cmdpubdet_Click()

With CrystalReport1

.ReportFileName = “C:\books\vb60\programs\pubdet.rpt”


End With


End Sub


Private Sub cmdpubtitles_Click()


‘take publisher id from user

pubid = InputBox(“Enter id of publisher whose titles are to be printed”, “Pubid”)

With CrystalReport1

.ReportFileName = “C:\books\vb60\programs\pubtitles.rpt”

.SelectionFormula = “{publishers.pubid} = ” & pubid


End With


End Sub


Private Sub cmdpubyearcount_Click()

With CrystalReport1

.ReportFileName = “C:\books\vb60\programs\pubyearcount.rpt”


End With


End Sub

Listing 22.1: Code to invoke reports.


Properties of Crystal Report Control


The following are some of the commonly used properties of Crystal Report control.




ReportFileName Specifies the report to be printed.
Connect Connect string necessary to connect to database.
CopiesToPrinter Specifies the number of copies to be printed while printing to printer.
Destination Specifies the destination to which the report is to be sent. Valid options are:

0-Windows, 1- Printer, 2 – File and 3 – MAPI.

DetailCopies Specifies the number of copies of each record in the Details section to be printed.
GroupSelectionFormula Contains the formula that is to be merged with current group selection formula.
MarginBottom, MarginTop,

MarginLeft, MarginRight

Specify the margins of the report.
PrinterCopies Specifies the number of copies to be printed.
PrintFileType Specifies the type of file used while printing to a file. See on-line documentation of Crystal Report Control for the list of available options.
SelectionFormula Specifies the formula to be merged with current record selection formula.
SQLQuery Contains the query that is to be executed to get the data. Only WHERE and FROM clauses of the report may be changed from the original query.

Table 22.2: Properties of Crystal Control.


The above list is really a small list of properties. For complete details, please see on-line documentation.


PrintReport Method

This method of Crystal Report Control is used to print named report to the specified destination. This method returns a result code, which is 0 for success and in the range 20xxx in case of failure.


Crystal Report Writer is a very simple and interesting report writer. It has always been a part of Visual Basic and most of the Visual Basic programmers are quit used to it. However, it should be noted that it is not bound to only Visual Basic. It could be used from any tool that can host an ActiveX control. Because as you have seen, the interface between the form and the report is through Crystal Report control.