SIMPOL Documentation

The type definition is probably the most compact way to look at these types. Here is the type definition of report1:

type report1(sqlq1, report1aggregatecontainer) export
  reference
  sqlq1 query resolve  readonly

  embed
  string orderclause   readonly
  boolean distinct     readonly
  
  event onreportstart
  event onreportend
  event onoutputrow
  
  dring groups
  dring aggregates
  
  reference
  type(*) _
  type(*) __ resolve
  type(reportoutputtarget) outputtarget
  SBLNumSettings numlocale
  SBLlocaledateinfo datelocale
  
  function addgroup         readonly
  function removegroup      readonly
  function addaggregate     readonly
  function removeaggragate  readonly
  function run              readonly
  function setorderclause   readonly
end type
  

Let's start at the beginning. The first thing we see is two type tags: sqlq1, and report1aggregatecontainer. This allows a report1 object to be assigned to any variable that has been declared as able to contain one of these two types. Following that, we see as the first thing, a sqlq1 type parameter called query, which is marked as resolve. Since it is marked that way, all of the properties and methods of the sqlq1 type will appear as part of the report1 type.

	Note
	Column names are case-sensitive, so when using them in various parts of the report, such as the where or order clause, make sure to use the exact name as specified in the select clause.

The next two items are the orderclause and the distinct properties. The sqlq1 carries out the query, but does not handle the ordering of the output. This is handled by the report1 engine. The name used in the orderclause must match the names used in the columns passed to the select clause. If a field name has its name changed using the AS operator, then the name following the AS operator must be used in the order clause. Sorting is done in ascending order by default. To reverse it, add the DESC key word preceded by a space following the column name. Sorting of text currently does not support any other collation order except native Unicode number, which means that lowercase letters will sort out of sequence with uppercase characters. The second property, distinct, if set ensures that if an entire output line is duplicated, that duplicates do not appear in the output. This can happen under certain circumstances with various filters and joins between tables.

Following on from there, three events are listed: onreportstart, onreportend, and onoutputrow. If defined, these events will be called at the appropriate times, as can be inferred from their names. When implementing some code that makes use of the report engine, at the very least you would want to create a handler for the onoutputrow event. This will get called each time a row is read. The function is passed the following parameters:

The first two are the report itself and the current running instance. The details about the instance will be found below. The third parameter is a 2-dimensional array that starts at 1, and in the n,0 position is the data type, and in the n,1 position is the display format for that column. The columns are in the same order as when they are passed in to the select clause of the query (these are not necessarily 1:1 with fields, since the select clause allows the use of SQL92 functions to create calculated columns). The number of columns can be retrieved using the report1.getcolumncount() method. The fourth parameter is a 1-dimensional array starting at 1 that contains the values for the current row for each of the columns. The values will be of the same data type (or a compatible one) as that of the column.

For completeness, the parameter lists of the other two events are:

The next parameter is a dring called groups. This contains the ring of report1group objects that will be processed by the report. Groups are processed in the order they are added, so the outermost group will be the first one added, and the innermost group will be the last one added. For example, if you are reporting on name and address data, and grouping on city and then by surname, the city group should be added first, and then the surname group. Also, the sort order should be "city,surname" in order to get the results that are expected.

The aggregates property is also a dring that contains the report-level aggregate values to be computed, each of which is of type report1aggregate. All aggregates only work with numeric columns, except for the count aggregate which is not associated with a column at all (the column number should be set to 0). The supported aggregates currently are: sum, mean, median, mode, and count.

The outputtarget property is not used by the report1 type, since it is not actually concerned with the output at all. It is there to be used by types that deal with output.

The numlocale and datelocale properties should be passed in so as to ensure that the output is formatted correctly. If the application is using the appframework.sml library, then the application object will make these available using the exact same types, so that consistency can be assured across the application.

The usage of the methods should be pretty clear from their names. They provide a method for adding and removing groups and aggregates, setting the order clause, and running the report. There will be a large number of additional methods exposed that are part of the sqlq1 type. These include:

The other methods are used while the report is running but are used by the report engine itself, so you shouldn't need to use them unless you are trying to use the sqlq1 type on its own, which is an advanced topic.

Aggregate values can be calculated at the report or group level. In each case they make use of the same types: report1aggregate and report1aggregateinst. The creation of an aggregate for a qualified column is quite simple, and is done the same for both report-level and group-level aggregates. The key is the first parameter to the new() method of the type. Here are the parameters to the method:

The first two parameters must be passed, or the creation of the object will fail. In the case of the count aggregate, the colno parameter is not required (but in the quick report and graphic report versions is set to 0). In all other cases the colno parameter will also be needed, as will the datatype parameter. The typeid parameter is used by both the quick report and graphic report libraries, but is not used by this one. The final parameter is as usual, the error parameter, and should be a pre-initialized integer in order to get the value back should the object fail to be created.

The only other thing that needs to be done to use the aggregate in the report is to assign the onupdate event handler. Each time a row is read, the aggregate values need to be updated. The function assigned to this event for the specific aggregate handles doing the appropriate type of update.

The report library contains ten functions that are used together with the aggregates, five of them for providing the getval functionality and five for providing the update functionality. These are:

The only place that you might encounter this type, is if you decide to implement your own aggregate value type and handler. This type is one of the parameters passed to the getval and update functions of an aggregate implementation. Unless you need to do that, you don't really need to worry about this type. Doing this is an advanced topic.

In order to provide a grouping functionality within the report, we implemented the report1group type. This type contains the static definition of a group that is used in a report. This includes the two events: ongroupstart and ongroupend, the column number (colno), the name of the group (typically the column name), its data type, and if defined, any aggregate values. Aggregates work exactly the same way as with the report, and use the same type. When adding a group to a report, the addgroup() method of the report is called. To add an aggregate to a group, call the addaggregate() method of the group.

This type is only used by event handlers that are dealing with the ongroupstart and ongroupend events. The report1groupinst type contains the current information about this instance of the group, including its value and in the ongroupend event also the various aggregates that may have been defined for the group.

Creating a report is not particularly complicated. Using the address.sbm from the Address Book example (see the SIMPOL Quick Start Guide), a sample report can be seen in the code below. This report outputs a tab-delimited carriage-return and linefeed delimited file of the data from the selected columns.

function main()
  report1 report
  sbme1 sbmfile
  sbme1table address
  integer e, erridx
  string s, errmsg
  fsfileoutputstream fpo

  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
      report =@ report1.new()
      report.setselectclause("AddressID, FirstNames, Surname, \
        City, CountryCode", errmsg, erridx)
      report.setwhereclause("", errmsg, erridx)
      report.adddb1table(address)
      report.setorderclause("Surname")
      fpo =@ fsfileoutputstream.new("addresslist.txt", error=e)
      if fpo =@= .nul
        s = "Error number " + .tostr(e, 10) + " opening output \
             file 'addresslist.txt'{d}{a}"
      else
        report.onreportstart.function =@ \
          report1_tabbed_output_reportheader
        report.onreportstart.reference =@ fpo
        report.onoutputrow.function =@ report1_tabbed_output_row
        report.onoutputrow.reference =@ fpo

        report.run(errmsg, erridx, error=e)
        if not (errmsg > "" or e != 0)
          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 if
end function s


function report1_tabbed_output_reportheader(report1 report, \
  report1inst reportinst, fsfileoutputstream fpo)
              
  integer cnt, i
  string title, outline, emsg

  emsg = ""
  outline = ""
  cnt = report.getcolumncount()
  i = 1
  while i <= cnt
    title = report.getcolumntitle(i, emsg)
    if title > ""
      outline = outline + .if(i > 1, '{9}', '') + title
    else
      outline = outline + .if(i > 1, '{9}', '') + ""
    end if
    i = i + 1
  end while
  
  outline = outline + "{d}{a}"
  fpo.putstring(outline, 1) 
end function


function report1_tabbed_output_row(report1 report, report1inst \
  reportinst, array columns, array currcolvals, \
  fsfileoutputstream fpo)

  integer cnt, i
  anyvalue value
  string svalue
  string outline
  string displayformat
  type datatype

  outline = ""
  value =@ anyvalue.new()
  cnt = report.getcolumncount()
  i = 1
  while i <= cnt
    value = currcolvals[i]
    datatype =@ columns[i,0]
    displayformat = columns[i,1] 
    svalue = val2string(datatype, value, report.datelocale, \
               report.numlocale, displayformat, .false)
    if svalue > ""
      outline = outline + .if(i > 1, '{9}', '') + svalue
    else
      outline = outline + .if(i > 1, '{9}', '') + ""
    end if
    i = i + 1
  end while
  
  outline = outline + "{d}{a}"
  fpo.putstring(outline, 1) 
end function
            

The previous sample program demonstrates the use of two events to handle the initial output of the header, and then to output the data for each row. It also sorts the results according to the Surname column. As can be seen from the source code, there isn't much required to create a report using code, especially once the event handlers have been written. The two event handlers here are not specific to the data, so they can be used to output any result in tab-delimited format.

In this section we have learned about the design of and how to work with the report1 type. We have also discovered that although it doesn't take much code to create a report this way, that it doesn't actual produce output unless we write it ourselves. In the next two sections, we will have no more effort, but we can get output to a window or the printer.