Fortes4Lazarus

Basic Tutorial Part 2

By Stephen Frye

This article is Part 2 of a 2-part tutorial on building reports in Lazarus using FortesReport. This tutorial will show how to build a master-detail style report.

Before Starting

As with the first tutorial, this chapter will use three extra sets of items not supplied with Lazarus:

A SQL database supplied with and running under the Firebird database engine;
The ZeosLib set of database-connection components;
And, of course, the FortesReport report-design components.

If you don’t have these installed on your PC, refer to Tutorial Part 1 for links to these resources and installation instructions.

What’s being built

The end result of this tutorial will be a Master-Detail report. For every item in the Master part of the report, there will be one or more related Detail items. This relationship is very common in database systems: Orders (Master) and Order Items (Detail) is an often-seen scenario. For this tutorial, we will report on the Sales (Detail) generated by the Sales Staff (Master) in the EMPLOYEES.FDB database included with the Firebird database engine.

Let’s Get Started

Start Lazarus and start a New Project. Since FortesReport uses a non-visible form to hold its components, add an extra form to the project for the report itself plus a Data Module to hold the database connections. Save the project, giving meaningful names to the forms and source files. The following names will be used in this tutorial:

frmMain (frm_main.pas): “Main form” that will be the user-visible interface.
rptMasterDetail (rpt_masterdetail.pas): form to hold the FortesReport components.
ModData (mod_data.pas): the DataModule.

Beginning with this tutorial, I’m adopting a naming convention recommended in a QuickReport tutorial: the name of the form holding the report is given the name of the report, and the report itself is named, simply, “Report”. I feel it will make it easier to keep track of reports in the IDE, the Obect Inspector and code – printing a report is then done with a self-explanatory line of code, e.g.:

rptMasterDetail.Report.Print;

Connect to the Database

First, we’ll add the components to connect the Lazarus application to the Firebird database, starting with the basic connection. From the Zeos Access tab in the Lazarus IDE, place a TZConnection component into the DataModule. Set the following properties:

Database: find the “EMPLOYEES.FDB” database installed with the Firebird database engine.
Hostname: if EMPLOYEES.FDB is located on your desktop PC, enter “localhost”; otherwise use the IP address of the computer where the file is stored.
Name: (optional) give a relevant name to the connection. Used for this project: “dbEmployees”.
Password: masterkey.
Protocol: firebird-2.1 or whatever version you’re actually using.
User: sysdba.

Test the connection by setting the Connected property to true. If no errors occur, your connection is properly set.

Next are the components to retrieve data for the Master part of the report. Again from Zeos Access, place a TZReadOnlyQuery component in the DataModule, and set the following properties:

Connection: open the list and select dbEmployees.
Active: set to True.
Name: (optional) give a relevant name to the connection. Used for this project: “qrySalesStaff”.
SQL: SELECT

EMPLOYEE.EMP_NO,

EMPLOYEE.FIRST_NAME,

EMPLOYEE.LAST_NAME,

DEPARTMENT.LOCATION

FROM

EMPLOYEE

INNER JOIN DEPARTMENT ON DEPARTMENT.MNGR_NO = EMPLOYEE.EMP_NO AND EMPLOYEE.DEPT_NO = DEPARTMENT.DEPT_NO

WHERE

(EMPLOYEE.JOB_CODE = 'SRep') OR

(EMPLOYEE.JOB_CODE = 'Sales')

ORDER BY

EMPLOYEE.LAST_NAME, EMPLOYEE.FIRST_NAME;

Test this by right-clicking qrySalesStaff, picking “Edit Fields” from the context menu, and then clicking on the “+” sign in the pop-up dialog. You should see the following fields in the list:

Select all of the fields in the list, and click on Create to add the fields to the Object Inspector. This is necessary to make the fields usable in your report.

Switch to the Data Access tab in Lazarus and place a TDataSource component in the DataModule. You’ll need to set the following properties:

DataSet: open the list and select qrySalesStaff.
Name: (optional) give a relevant name to the connection. Used for this project: “dsSalesStaff”.

Finally, add the components to retrieve data for the Detail part of the report. From Zeos Access, place a second TZReadOnlyQuery component in the DataModule, and set the following properties:

Connection: open the list and select dbEmployees.
Active: set to True.
Name: (optional) give a relevant name to the connection. Used for this project: “qrySalesItems”.
SQL: SELECT

SALES.SALES_REP,

CUSTOMER.CUSTOMER,

SALES.PO_NUMBER,

SALES.SHIP_DATE,

SALES.ITEM_TYPE,

SALES.TOTAL_VALUE

FROM

SALES

INNER JOIN CUSTOMER ON SALES.CUST_NO = CUSTOMER.CUST_NO

WHERE

SALES.ORDER_STATUS = 'shipped'

ORDER BY

SALES.SHIP_DATE;

As with the qrySalesStaff query, add the fields from the query to the Object Inspector. You should see the following fields in the list:

Finally, it’s time to link the detail query to the master side, so that the detail items are automagically filtered and limited to displaying only the rows related to the master item. For this report, the Employee Number is the piece of information that joins the two sets of data. Three additional properties need to be set in qrySalesItems:
- MasterSource: set this to the DataSource linked to the master query: dsSalesStaff.
- MasterFields: this is the column in the Master RowSet containing the value common to both sets of data; in this case EMP_NO.
- LinkedFields: this is the column in the Detail RowSet containing the value common to both sets of data; in this case SALES_REP.

Switch to the Data Access tab in Lazarus and place a TDataSource component in the DataModule. You’ll need to set the following properties:

DataSet: open the list and select qrySalesItems.
Name: (optional) give a relevant name to the connection. Used for this project: “dsSalesItems”.

Build the Report

Now it’s time to actually start construction of the report. The report is a listing of Employees in the Firebird database who are classified as being part of the Sales departments, along with any sales they have generated which have actually been shipped. As with the report in Tutorial Part 1, the report will have a Page Header and Column Header along with the rows of information, and the report will be sorted by the employee’s Last Name (surname) and First Name (given name). Since most of the report-building basics were covered in Part 1, they will not be repeated here. When done your report should look something like this:

Place a TRLReport component onto rptMasterDetail from the FortesReport tab. Set its name to “Report”, and set the DataSource property to the Master DataSource: ModData.dsSalesStaff. This tutorial will be based on a Letter-size sheet of paper in Portrait orientation, but feel free to choose whatever PageSetup properties you wish. Expand the AllowedBands property and set btColumnHeader (column header), btDetail (detail information) and btHeader (page header) to True.

Now we add specific bands and controls to the report page:

Page Header

Add a TRLBand to the report page. Set the BandType to btHeader, and set its name as well: bndHeader. Drag the sizing handles to increase the height of the band.

Page Title: add a TRLLabel within the Page Header band. Set the following properties:
- Align = faCenter to center the label within the band.
- Alignment = taCenter to center-align the text within the label.
- Caption = Shipped Orders.
- Font = your desired font style and size.
System Information: we’ll add some controls in the Page Header to display the date when the report is printed, as well as the page number:
- System Date: add a TRLSystemInfo control to the Page Header; manually place it on the left side of the page. Set Info to itDate, and set your desired font style and size.
- Page Number: add a TRLSystemInfo control to the Page Header; manually place it on the right side of the page and set Alignment to taRightJustify. Set Info to itPageNumber, and set your desired font style and size.

Master-Level Data Fields

Place a new TRLBand underneath the Page Header band. Set its BandType to btDetail. Drag the sizing handles to make it tall enough to accommodate the Master-table data fields and column headers for the Detail-table data fields. A height of 3 cm. should be plenty of room for the design work; once you’re satisfied with your layout you can shrink it back down to what’s really needed.

Master-data Fields: Drop a TRLLabel and TRLDBText component into the master-data band for each master data field you want to display in the report. Set the Caption property of each label to the name you want displayed for the data. Position the Label to the left of the text component, and line them up horizontally across the page. For each TRLDBText component, set the DataSource property to ModData.dsSalesStaff, and choose the appropriate data field.
Column Labels: Place a TRLLabel in the master-data band for each detail data field that will be shown in the report. Set the Caption property of each label to the name you want displayed for each column.

Detail-Level Data Fields

Now it’s time to add the detail items. In addition to the detail data, we’ll also add summary components to show the total number and total value of the shipped orders for each sales person.

Place a TRLSubDetail band on the report underneath the master-data band. Make it tall enough to contain two bands: a Detail band to display the data fields, and a Summary band to show the summary calculations. Set the following properties for the SubDetail Band:
- AllowedBands: Expand and set btDetail and btSummary to true.
- DataSource: Open the list and select ModData.dsSalesItems.
Place a TRLBand in the upper part of the TRLSubDetail band. Set the BandType to btDetail.
- Place five TRLDBText components in this new detail band. Set the font attributes as desired. Set the DataSource for each one to ModData.dsSalesItems. Align these text fields with their headers, and select the matching data fields: Customer, PO Number, Ship Date, Item Type, and Total Value. Since the Total Value control will be displaying numbers, set its Alignment property to taRightJustify and align it with the right-hand end of its caption.
Summary calculations: Place another TRLBand within the TRLSubDetail band. Set the BandType to btSummary.
- Place two TRLDBResult components in the Summary band: one for Items Sold (a count of the items for each salesperson) and one for the Total Value of the items (a sum of Total Value). Set properties for each as follows:
  Items Sold
  - DataSource:ModData.dsSalesItems
  - DataField:Item_Type
  - Info:riCount
  Total Value:
  - DataSource: ModData.dsSalesItems
  - DataField: Total_Value
  - Info: riSum
- Now place two TRLLabels in the Summary band, and set their captions to match the two summary-data fields.

OK, that’s it! There’s nothing else to be added to the design of the report. If you’ve followed the Tutorial precisely, your form in design-mode should be similar to this:

We’re not quite done yet, however. If you right-click the report and select Preview, you might see something like this:

Yuck! What’s with those numbers under the Value column? They don’t look like anything useful! OK, the last step in the design of this report is to format the Value column so that it is easy for the human eye to read:

Switch to the DataModule: ModData
In the Object Inspector, find and select the TOTAL_VALUE data field under the query qrySalesItems.
From here, formatting can be done in one of two ways:
- Set the Currency property to true. Data in the field will be displayed according to the Currency settings of your Operating System.
- Enter a specific formatting string into the DisplayFormat property.

For my report, I entered this string into the DisplayFormat property: #,###,##0.00. This formats the data with commas as the thousands separator, a period for the decimal point, and two places to the right of the decimal. Making either of the above formatting settings will set the format for both the data rows and the summary field.

Finish the Application

Now it’s time to add the user-interface components to the main form which allow the report to be Previewed or Printed. We also want to make sure that the report-design form is not visible when the program is run. There are three basic steps needed to make all this happen:

Set the Visible property of rptMasterDetail to False.
Switch to the code module for the Main Form, and add the name of the code module for the Report form in a Uses statement in the Implementation section of the code:

implementation

uses rpt_masterdetail;

Finally, add a Button to the Main Form. Set its Caption to Preview, and add the following code line to the button’s OnClick event:

rptMasterDetail.Report.Preview;

or, set the Caption to Print, and put this code in the OnClick event to print directly to the default printer: