Delphi
Quick Report Techniques
- Scenario:
- Frequently one needs to generate a multitude of
ad hoc style reports, all based on data from a
table which needs to be filtered according to a
date range
- Problem:
- The usual way is to create one report for each
required report, but this uses up an excessive
amount of memory & resources
- A solution:
- Create one report then before previewing report
alter its SQL statements, SQL parameters,
qrDataLabel assignments etc
- This creates a problem in that cannot access a
quick report's added components from its generic
quickreport object by using the following code:
- with quickReport1 as TQuickReport do
Query1.SQL.ParamByName('StartDate').asDateTime
= Startdate
- as Query1 is not part of a TQuickReport
base object, it cannot be referenced this
way
- Inherited solution:
- Create a "base" report with various
components such as TQuery, Title labels, date
range labels, title, column header, group,
detail, page footer bands
- When creating a new report, just inherit from
this base report then can create report at
runtime & access its components generically
as above.
- In particular, one can create a generic procedure
that takes various parameters such as report
name, date range mode (today, date, date range)
which then prompts user to select date range from
a dialog box then runs report by sending the
report its date parameters
- there are several ways of creating calculated fields:
- within SQL statement:
- calculation performed on server if is an
SQL database
- problems with limitations &
differences between databases
- may be possible to sort/group on
calculated field BUT NOT if it is a
summary field (eg. count, sum, average)
- calculated field in a Tdataset component
which is updated via its OnCalcField event:
- very flexible & easy
- can use global variables to track
progress while report is iterating
through records within table
- can't order/sort/group by this field
unless using a TClientDataset component
which supports in memory tables and
indexing
- manual update of a label component
via the band's BeforePrint event:
- flexible and easy
- can use global variables to track
progress while report is iterating
through records within table
- but can't order/sort/group by this field
- Quick Report expression object:
- easy but not as flexible & some bugs
exist:
- TDataset must exist on report and
not in another form or datamodule
- can't order/sort/group by this field
- can copy component onto summary band and
it will provide summary calculation for
each group band &/or whole report as
well
- set its ResetOnPrint to false to give
cumulative value
- Scenario:
- you have a database table which contains all
attendance bookings for various clinics and
doctors
- each clinic occurs same day each week
- you wish to generate a report which shows number
of bookings per clinic per doctor for the next
12weeks including all clinic dates whether
bookings made or not
- Problem:
- if just use your bookings registry table to
generate report it will only show data for
clinics where bookings have already been made and
not show the empty clinics.
- Possible solution:
- ClinDates is a table used to store the possible
combinations of doctor codes, clinic dates for
next 12 weeks based on a user-selected clinic
code:
- The program empties data from this table
then regenerates new data by:
- determining the 1st clinic date
from user-selected start date and
looking up next available clinic
date for selected clinic code
- looks up all doctor codes used in
selected clinic code
- iterating through all 12 wks for
each doctor
- The report is grouped on Clinic_code, then Doctor
code.
- Details:
- ClinDates table structure: ClinicCode,
DoctorCode, ClinicDate with primary index
being compound on 1st 2 fields
- User selects a clinic and starting date
(eg. today's date)
- Determine next clinic date for that
clinic from bookings table
- Empty ClinDates table
- Add new record to ClinDates table for
each possible clinic/doctor combination
for next 12weeks assuming clinics are
1week apart
- Create a quickreport:
- ClinDates table as its master
table and a secondary lookup
table which is the main bookings
register table
- create a calculated field in the
ClinDates table and on the tables
OnCalcFields event, determine
number of bookings for that
clinic/doctor/date by looking up
the bookings register table
- add 2 group bands - one for
clinic code & second for
doctor code
- complete remainder of quick
report as per usual
- Selecting paper trays:
- this can be problematic in that the Quick Report PrinterSettings.Outbin
does not always equate with a printer paper tray you would expect and
sometimes does not equate with anything (as is the case on the new
Lexmark printers such as the T652 even though they worked fine on the
older T644) and thus the default printer tray is used.
- in fact the QuickReport OutputBin is just an array that may not make
sense unless you understand how QuickReport maps these values to a
tray - see here,
and you will note a rather non-intuitive mapping is "Lower"
gets mapped to tray bin code 2, while Middle gets mapped to tray bin
code 3 - perhaps early versions printers only had 2 trays, and they
did not want to break backward compatibility?
- solution 1:
- you could edit the QRPrntr.pas file to add your own tray
mappings as a workaround.
- this zip
file contains code to determine bin codes for your printers
- solution 2:
- you can avoid using the OutBin, and instead use the PrinterSettings.UserBinCode value, but to
determine this value for your printer and its tray, you need to
download the code listed in Jan 2003 here
- for instance, trays 1,2, and 3 on a Lexmark T644 gives bin values of
1,2,3 respectively (hence you could use the OutBin successfully
without editing it), but a Lexmark T652 gives bin values of 279,280 and
281!
- remember to set PrinterSettings.UserbinCodes to true.