Delphi Quick Report Techniques

• using inherited reports
• calculated fields in reports
• a future bookings summary report
• printing to Lexmark laser printers
• see also:
  • Delphi Programming Main Index

Using inherited base reports:

• 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

Calculated fields in reports:

• 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

Future Bookings Summary Report:

• 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:
      1. determining the 1st clinic date from user-selected start date and looking up next available clinic date for selected clinic code
      2. looks up all doctor codes used in selected clinic code
      3. 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

Printing issues:

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