Printed Reports

Quick Reports

A menu has been added to the form from which Form2 can be accessed. We will probably want a printed report of the data, so here's how to do it.

Add a new form to the project and change its caption to 'Search by Customer'. We will use this to launch a search so create a link to it from the menu on the main form.

Choose File/New and choose 'Report' from the options:

A new QuickReport is added to the application; components for QuickReports can be found on the QReport palette. QuickReport is another sample application, written by an external developer (like Charts), which Borland has incorporated into Delphi.

We have already seen how to create a query based on the name of the customer and we could use the Report tool to create a more professional printed invoice. Other reports on the data might be based on invoices generated in a single day, invoices generated in a particular week or period, or invoices based on a certain total cost, such as greater than £1,000. Here we shall create a report that will retrieve all invoices belonging to a particular customer.

On the new form add a label, an edit box and a button as follows:

The button will activate a preview of the report, which includes a print button:

procedure TForm3.Button1Click(Sender: TObject);
begin
with QuickReport5 do
begin
ADOQuery1.Parameters[0].Value:=Edit1.Text;
ADOQuery1.Open;
ADOQuery1.Requery;
if ADOQuery1.RecordCount=0 then showmessage ('No records found')
else Preview;
end;
Close;
end;

On the report:

Add Unit1 to the Uses part of Unit4 (so the QuickReport module can pick up the ADOConnection object)
Add an ADOQuery component to the report form and set its Connection to Form1.ADOConnection1.
Set the DataSet property of the report form to 'ADOQuery1'
Create a query in Access and copy and paste the SQL into the SQL property of the ADOQuery component, or type it straight in:

SELECT Invoices.[Invoice ID], Invoices.Date, Invoices.Payee, Invoices.[Total Cost]
FROM Invoices
WHERE Payee = :cust;

Make sure the ':cust' parameter is present in the Parameter property of the ADOQuery.
On the report form, add two QRBands. The default type is 'rbTitle', change the BandType property of the second band to 'rbDetail'.
Select the Detail section and add a QRLabel and a QRDBText component. Set the Caption of the label to 'Invoice ID'. Set the DataSet property of the QRDBText component to 'ADOQuery1' and the DataField property to 'Invoice ID'.
Add three further pairs of QRLabel and QRDBText components to cover the other fields in the SQL query.

The output is a little crude at this stage but further refinements could be made:

To create a report for invoices over a certain amount:

Create a new form as before with a label, an edit box and a button. Link this new form to the menu structure of the main form.
Create a new report and add an ADOQuery component as before, adding 'Unit1' to the Uses section and setting its connection string to Form1.ADOConnection1.
Set the DataSet property of the report form to 'ADOQuery1'.
The main difference from the previous report is the SQL and the call to the ADOQuery:

SELECT Invoices.[Invoice ID], Invoices.Date, Invoices.Payee, Invoices.[Total Cost]
FROM Invoices
WHERE [Total Cost] > :amount;

procedure TForm7.Button1Click(Sender: TObject);
begin
with QuickReport6 do
begin
ADOQuery1.Parameters[0].Value:=strtofloat(Edit1.Text);
ADOQuery1.Open;
ADOQuery1.Requery;
if ADOQuery1.RecordCount=0 then showmessage ('No records found')
else Preview;
end;
Close;
end;

The WHERE clause of the SQL features a different parameter. The underlying data item in the Access database is Currency (a real number), so we have to convert the string in the EditBox to match it.
Check that the ADOQuery has picked up the ':amount' parameter from the SQL
Add QRBands to the report form as before
Add QRLabel and QRDBText components to the report form as required, setting the DataSource to ADOQuery1 and the DataField to the appropriate field for each pair
Add a QRSys component to the Title band and change its Data property to qrsDate
Add a new QRBand and set its BandType to PageFooter (the band moves to the correct place)
Add a QRSys component to the footer and set its Data property to qrsPageNumber

Another report might be based on the date or a range of dates. Unfortunately we chose Long Dates for our Access data type and we cannot convert a string input to this data type (the StrToDate function takes outputs a date in short date format) - we will put this right in the next section.

A final report might be based on a query involving both tables in the database, in particular a report to print an invoice for a customer's order with the details of all items on it. To achieve this, create a new report as before with an ADOQuery component, a Title band and a Details band.

Create in Access or write out the following Query:

SELECT [Invoices].[Invoice ID], [Invoices].[Date], [Invoices].[Payee], [Invoices].[Total Cost], [Items].[Item ID], [Items].[Description], [Items].[Unit Cost], [Items].[Quantity], [Items].[Total cost]
FROM Invoices INNER JOIN Items ON [Invoices].[Invoice ID]=[Items].[Invoice]
WHERE Invoices.[Invoice ID] = :number;

This has the necessary JOIN between the tables to find all the Items linked to a particular invoice through the Invoices.[Invoice ID] and Items.[Invoice] fields. Add it to the ADOQuery SQL property and check that the parameter has been picked up by the Parameters property.

Add items to the report form to make something like this:

Make sure that the DataSet property of the report form is set to ADOQuery1.

Create a new form and add the controls required to launch the report - these will be pretty much the same as before; the code for the button click is exactly the same apart from the report form reference.

Once again the various reports can be added as main menu items on the form. In the next section we shall look at ways to refine this database application further.

Database Home