Creating Reports using Crystal Reports

*  How to create a simple report using Crystal Reports?

*  What are the available sections in a report?

*  How to create formula fields and special fields?

*  How to format data and numbers?

*  How to create cross tabulation report?

*  How to invoke a Crystal Reports from a Visual Basic form?

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

¨         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 option.

2.      Crystal Reports displays Create New Report window containing various report styles.

3.      Select Listing type.

4.      Crystal Reports displays another window  where you can select the data file or ODBC data source to be used to retrieve data.

5.      Click on Data File button to get Choose Database File dialog.

6.      Select BIBLIO.MDB and either double click on the file or click on Add button.

7.      Once tables are listed in Crystal Reports Expert, then click on Done to close Choose Database File dialog.

8.      Crystal Reports Expert displays list of tables and the links between the tables in Links tab.

9.      Select Fields tab and select fields  that you want to include in the report from PUBLISHERS table.

10.  Click on Preview Sample button. When Crystal Reports prompts you to enter number of records to be displayed Select First 100 Records radio button and click on Ok.

11.  Crystal Reports displays the report, but it will be too small to see.

12.  Select Report menu and Zoom option to increase the size of the characters.

At this stage, your report should look like

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

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.

Section	Description
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 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.

7.      Move date towareds right to create space for text ‘Date:’.

8.      Select Insert -> Text Field

9.      Enter Date in text box.

10.  Click on Accept button.

11.  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 section.

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
	Name
Titles	Title
	Year Published
	ISBN

5.      Select Sort tab in Create Report Expert

6.      Select Publishers.PubID as the field to be used for grouping and sorting.

7.      Select Total tab

8.      From Total field list box remove all by selecting each and then clicking on  <-Remove button. By default CRW adds a total field for each numeric field in the report.

9.      Add Titles.ISBN to Total Fields and change summary function to Count. This will contain the count of books for each publisher.

10.  Click on Select tab and select Titles.Year published and add it to Select Fields.

11.  Change condition to greater than and enter value 1980.

12.  Click on Style tab and enter ‘Publisher-wise Titles Report’ as the title.

13.  Leave Standard  as the style of the report.

14.  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 window.

3.      In Edit Formula  window enter the following expression in Formula text (see figure 22.6).

Year(today) – {Titles.Year Published}

4.      Click on Accept to accept the formula and place the rectangle on the right of ISBN in details section.

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

6.      Change Font Size of all the fields to 11.

At the end of all these steps report designer

7.      Click on Preview tab to display report.

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

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 Cross-tab as the type of the report.

3.      Select BIBLIO.MDB as the datafile.

4.      Click on Cross Tab tab and drag fields

Rows                                  titles.year published

Columns                            titles.pubid

Summarized Field              titles.title

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

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

6.      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	Value
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

 Write the following code for command buttons.

 

Private Sub cmdpubdet_Click()

 With CrystalReport1

   .ReportFileName = "C:\books\vb60\programs\pubdet.rpt"

   .PrintReport

 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

   .PrintReport

 End With

End Sub

Private Sub cmdpubyearcount_Click()

 With CrystalReport1

   .ReportFileName = "C:\books\vb60\programs\pubyearcount.rpt"

   .PrintReport

 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.

Property	Meaning
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.