Working with quickreport1

Working with quickreport1
	Chapter 25. Using Reports in SIMPOL

Working with quickreport1

Working with the quickreport1 type is similar to working with the report1 discussed in the previous section. One of the main differences is that this report handles output to various targets, and therefore needs to know more about the content. It also has the concept of a title, page numbering, and showing the current date at the top of each page (all optional), plus displaying column headings and coping with columns where the data is too long. It is limited to one font that it uses for the entire report and has the advantage that it is quite simple to define. The Quick Report also supports grouping, sorting, and output of group and report aggregate values, such as the count of rows plus the sum, mean, medium, or mode for a column in the report and groups. To begin, let's have a look at the definition of the quickreport1 type:

type quickreport1 export
  embed
  boolean dirty

  integer outputtarget
  boolean valid
  string filename
  event onpagechange
  event onoutputheader
  event onoutputfooter
  event onbeforerow
  event onafterrow
  event onbeforegroup
  event onaftergroup
  event onoutputreportheader
  event onoutputreportfooter

  // flag indicating if the report header has been output yet,
  // so people can suppress the page header output
  boolean reportheaderoutput
  // allows people to suppress row output and just output
  // totals of groups or the whole report
  boolean suppressrowoutput

  // Properties for output to window or printer
  integer paperwidth
  integer paperheight
  integer marginleft
  integer margintop
  integer marginright
  integer marginbottom
  integer dpix
  integer dpiy
  boolean showpagenumber
  boolean showdate
  boolean showtitle
  string title
  string dialogdata
  number wrapcharcountkludgevalue
  integer currpagenumber
  integer currrownumber
  integer currtopofpage
  integer rowheight
  number rowheightadjustment    readonly
  integer lastreportedpagenumber
  // used to reserve an area for a footer,
  // to throw an early end of page
  integer footerlinecount

  string defnumberformat
  string defdateformat
  string deftimeformat
  string defdatetimeformat
  string defintegerformat
  string defbooleanformat

  dring columninfo
  dring datasources
  dring tables

  boolean usegauge
  gaugedialog gauge reference

  reference
  wxfont italicfont
  wxfont underlinefont
  wxfont bolditalicfont
  wxfont boldunderlinefont
  wxfont italicunderlinefont
  wxfont bolditalicunderlinefont
  wxfont headerfont
  wxfont pagefont
  wxfont origfont
  report1 report resolve

  // The following are for output types 1 and 2 (window and printer)
  wxprintout printout
  wxprintpagetemplate currtemplate
  wxprintpage currpage
  GDI gdi
  WINSPOOL winspool

  embed
  number fontwidthratio
  number fontheightratio
  boolean usewrapheight2

  // This is only for output type 1
  boolean centeroverdisplay
  boolean startat100percent

  // For clipboard output
  boolean suppressoutputmessages

  reference
  // This is for the clipboard target (tab separated and crlf separated)
  array clipoutput

  // This one is for the HTML output
  fsfileoutputstream fpo
  string outputfilename embed
  string stylefilename embed
  boolean tbodyoutput embed
  boolean outputrowodd embed

  // This is for the CSV output
  dbQRImport qrimportconverter
  dbCSVExport csvexportconverter
  boolean headeroutput embed

  // And this is for the SBME output
  dbSBMEExport sbmeexportconverter
  string targettablename embed

  array columns; // This is assigned after a callback has happened from
                 // the report engine, it contains the column information

  function addaggregate           readonly
  function addcolumninfo          readonly
  function adddatasource          readonly
  function addtable               readonly
  function finddatasource         readonly
  function findtable              readonly
  function getwrapheight          readonly
  function getwrapheight2         readonly
  function getprinttextextent     readonly
  function run                    readonly
  function outputextraline        readonly
  function setrowheightadjustment readonly

  function addgroup               readonly

  function getcolumninfobycolno   readonly
end type

As we can see, this type definition is considerably more complex than the one for the report1 type. In fact, if you look closely you will find that it actually contains the report1 type in addition to all of its extensions. The good news is, you don't need to worry about most of it, since it just works. The important bits to be aware of are the paperwidth, paperheight, and margin properties. Virtually everything is handled in the call to the quickreport1.new() method. Below is some sample code that demonstrates how to create a Quick Report:

include "quickreporthdr.sma"

function main()
  integer e, erridx
  string s, errmsg
  sbme1 sbmfile
  sbme1table address
  quickreport1 qr
  wxfont font
  report1group group
  quickreport1datasource ds1

  e = 0
  sbmfile =@ sbme1.new("address.sbm", error=e)
  if sbmfile =@= .nul
    s = "Error number " + .tostr(e, 10) + " opening \
         ""address.sbm""{d}{a}"
  else
    address =@ sbmfile.opentable("Address", \
       recordidfieldname="recid_ro_internal", error=e)
            
    if address =@= .nul
      s = "Error number " + .tostr(e, 10) + " opening the \
           ""Address"" table{d}{a}"
    else
      errmsg = ""
      erridx = 0
      font =@ wxfont.new("Arial Narrow", 10, "n", "n", "", \
                         error=e)
      qr =@ quickreport1.new(outputtarget=QR_OUTPUTWINDOW, \
               title="Address List", pagefont=font, error=e)
      qr.setselectclause("AddressID, FirstNames, Surname, \
                         City, CountryCode", errmsg, erridx)
      qr.setwhereclause("", errmsg, erridx)
      ds1 =@ qr.adddatasource(sbmfile.type, "address.sbm", \
                              sbmfile, error=e)
      qr.addtable(address, ds1, error=e)
      qr.setorderclause("City, Surname")
      group =@ qr.addgroup("City", 4, string, error=e)
      if group !@= .nul
        qr.addaggregate(group, QR_AGG_COUNT, .nul, integer, \
                        error=e)
      end if

      qr.addcolumninfo( 20000,  12000, "right,top", error=e)
      qr.addcolumninfo( 34000,  30000, error=e)
      qr.addcolumninfo( 66000,  40000, error=e)
      qr.addcolumninfo(108000,  65000, error=e)
      qr.addcolumninfo(175000,  6000,  error=e)
      qr.showdate = .true
      qr.showpagenumber = .true
      qr.showtitle = .true
      qr.usewrapheight2 = .true
      qr.addaggregate(.nul, QR_AGG_COUNT, .nul, integer, error=e)

      // The following commented out lines show how to save
      // and load a Quick Report using the XML format
      //savequickreport(qr, "addresslist.sxq", error=e)
      //qr =@ loadquickreport("addresslist.sxq", error=e, \
      //      errortext=errmsg)

      qr.startat100percent = .true
      qr.centeroverdisplay = .true

      qr.run(errmsg, erridx, error=e)

      if not (errmsg > "" or e != 0)
        wxprocess(20000000)
        s = "Success!{d}{a}
      else
        if errmsg > ""
          s = errmsg + "{d}{a}"
        else
          s = "Error number " + .tostr(e, 10) + \
              " running report{d}{a}"
        end if
      end if
    end if
  end if
end function s

The preceding program code should be fairly self-explanatory, but we will go through it briefly touching on the interesting points. In this program we decided to use Arial Narrow 10 points for our report. The font is created first and passed to the new() method. This is important! For various reasons, the Quick Report code makes variants of the font, so it is necessary to pass in the font when the object is created. There is no provision for changing it later. The outputtarget should be one of the valid output targets. There are six different valid target types, including: window, printer, CSV file, HTML file, clipboard, and database (SBME). The HTML target is similar to the window and printer targets, in that it produces a formatted report, albeit in one long page. The other three targets are well-suited to exporting data in their respective formats (clipboard produces a tab separated, newline separated output that can be directly pasted into programs like MS Excel). In the case of the data export targets, the aggregates, grouping, and other formatting information is ignored. The various constants for output and aggregate types can be found in the quickreporthdr.sma.

Once the quickreport1 object has been created, the select clause, where clause, and order clause are defined, and the data source and tables are added (just as in report1). Then we add a group, and an aggregate for the group. Finally we define the column information. This is required to be defined in the same order as the list of columns in the select clause. The addcolumninfo() method takes the following parameters:

Parameters to the quickreport1.addcolumninfo() Method

integer columnstart
integer columnwidth
string alignment
boolean wrap
integer error

The first two parameters are the horizontal starting position (from the left edge of the paper – the left and right margin values are ignored currently) and the width of the column. These are measured in micrometers (millionths of a meter). The next is the alignment. This can be one of:

Alignment Values for quickreport1.addcolumninfo() Method

"left,top" (the default)
"top" (centered horizontally)
"right,top"
"left" (centered vertically)
"" (centered vertically and horizontally)
"right" (centered vertically)
"left,bottom"
"bottom" (centered horizontally)
"right,bottom"

The fourth parameter is wrap. This will attempt to ensure that the content is wrapped around within the confines of the column and extends the line down the page until the content has been output. If the content is too large to fit on the page, it will be moved to the next page. If it is too large to fit on a page, it will be truncated. Using this feature will slow down the report, since for every row the content of each column with this feature will need to be tested to see if it fits and if not, how much space it requires. Also, there are two algorithms, one is much faster than the other, but is less precise. To use the faster algorithm, as shown in the preceding sample, the usewrapheight2 property must be set to .true.

Once the column information has been added, the program sets the switches to .true to enable the page title to be output, the date, and the page count. It then adds an aggregate for the overall count of rows in the report. Following that are two commented out program statements, that demonstrate how to save the report to disk, and how to load the report from disk. The default file extension for SIMPOL Quick Reports is .sxq. Finally, there are two options, one called startat100percent and centeroverdisplay, both of which currently only work on Windows (they are implemented by calling functions in the Windows API that do not have equivalents elsewhere). Once everything is prepared, the run() method is called. Since this program is just a sample, it then enters a wxprocess() call for 20 seconds, so that there is time to look at the output in the window. Without this call, the program would simply exit and the output window would immediately close. In a normal application that has a window open and which is sitting in a wxprocess() loop anyway, this would not be an issue.

Enhanced Quick Report Output

With the 1.8 release of SIMPOL Professional, a number of new capabilities were added. There are now the following events for formatting output to the print preview window or the printer:

onoutputheader – Called after the title, date, and page number line is output (if it is output) and before the column titles are output. The prototype for a function that handles this event is: funcname ( quickreport1 quickreport, report1 report, report1inst reportinst, type(*) reference ).
onoutputfooter – Called just before calling the function report1_quickreport_outputpageheader(). There is no standard footer output by the Quick Report. The prototype for a function that handles this event is: funcname ( quickreport1 quickreport, report1 report, report1inst reportinst, type(*) reference ).
onbeforerow – Called just before calling the code that outputs the row data. This call is not suppressed even if the property supressrowoutput is set to .true. The prototype for a function that handles this event is: funcname ( quickreport1 quickreport, report1 report, report1inst reportinst, type(*) reference ).
onafterrow – Called just after calling the code that outputs the row data. This call is not suppressed even if the property supressrowoutput is set to .true. The prototype for a function that handles this event is: funcname ( quickreport1 quickreport, report1 report, report1inst reportinst, array columns, array currcolvals, type(*) reference ).
onbeforegroup – Called as the group changes. No group header is output by the Quick Report, so the user can use this as they wish. The prototype for a function that handles this event is: funcname ( quickreport1 quickreport, report1group group, report1groupinst groupinst, type(*) reference ).
onaftergroup – Called just after calling the code that outputs the the aggregate values and the count (if any are defined). The prototype for a function that handles this event is: funcname ( quickreport1 quickreport, report1group group, report1groupinst groupinst, type(*) reference ).
onoutputreportheader – Called just after calling the code that outputs the page header, including the columns headings. This may be modified in a later version, or as an alternative, a method of suppressing the column headings at the start of the report may be added. The prototype for a function that handles this event is: funcname ( quickreport1 quickreport, report1 report, report1inst reportinst, type(*) reference ).
onoutputreportfooter – Called just after calling the code that outputs the the aggregate values and the count (if any are defined). No page footer is currently output on the final page. This may be corrected in a future release. If it is, it will be made optional. The prototype for a function that handles this event is: funcname ( quickreport1 quickreport, report1 report, report1inst reportinst, type(*) reference ).

In all of these events, to output anything to the window or printer, it is necessary to create an instance of a quickreportextraoutputinfo type. This contains all of the information required to print this content, including the position, alignment, name (this is the name given to the template item and must be different from the printname), printname (this is the name given to the string data and if not supplied it will be derived from the name), and the text (the actual string data that should be output).

This is passed to the outputextraline method of the quickreport1 object. The prototype for this function is: quickreport1.outputextraline ( quickreport1 me, quickreportextraoutputinfo outputinfo, string fontcharacteristics="", boolean incrementtopofpage=.true, integer error ). The fontcharacteristics should contain either nothing, or any of: biu, bi, bu, iu, i, u, or b, where b is bold, i is underline, and i is italic.

By setting the value of the incrementtopofpage parameter to .false it is possible to output multiple values on the same line. It is important that in the final call to this function (if making multiple calls) that this parameter is set to .true.

quickreport1 Summarizing Quick Report Output

Another new feature added in the 1.8 release of SIMPOL Professional was the suppressrowoutput of the quickreport1 type. If this is set to .true, then only the report and group aggregate values will be output. This feature is only available to the window and printer output targets.

quickreport1 Summary

As we have discovered in this section, creating a Quick Report in code is not very difficult. It is similar to the code used for a report1 report, but with the extra requirement of selecting a font and defining the column information. It is quick and easy, but it has the down side that everything on the report is in one font, and there is little flexibility over the layout and none regarding group headers and footers. This latest version does support some additional enhancements, such as summarizing the output by suppressing the row output, and offers the new events and the new method outputextraline() which can be used to output additional content. For more complex reports, where all of the items in headers and footers can be defined, and images can be incorporated, we need to use the Graphic Report, which is the subject of the next section.