Developers

Data Gathering in the $construct Method of a Report

By David Swain
Polymath Business Systems

Well, that's certainly a long title! As we get deeper into report territory, it takes more words to describe what we're about. The good news is that, since we've covered so much in previous articles, it shouldn't take quite so many words to convey the essence of this process.

In this article, we examine the process of generating more complex reports than in previous articles and how we can handle them in an "encapsulated" fashion. That is, all the code for handling the data manipulation, as well as any modifications of the layout, resides within the Report Class itself.

As we take over more of the functionality of the report with our own code, we also have to be more careful. We are removing parts of the safety net Omnis Studio provides and taking responsibility for more aspects of the reporting process upon ourselves. More power comes with an increased obligation to wield that power wisely.

But that's just part of being an adult...

Marching Orders

Parameters of the $construct method become even more important as we put more functionality into our reports. Not only might we need to tell the report how to configure itself for special situations, but we may now need to give it descriptive information about the data to be included as well if our code takes over this function. We are, in essence, telling the report instance how to proceed on a number of levels.

It is a rare report that always includes all records from the table it addresses, so we must pass into our process information about what the range of records is for this specific run. This information is generally obtained from user input, but may be time- or date-based or derived from some other environmental value. But even when based on environmental values, a report will always be more flexible if this information is passed into the report instance using constructor parameters. The user may not always want to report on last week's information (fixed by a built-in calculation based on #D), but on data from some arbitrary range of dates (passed in as parameters). We might set default values for the variables we use on the data entry window that launches the report, and we might even set default values for the parameter variables in the report's $construct method (useful for testing if nothing else). But we will generally need to give the user control over what values to pass to the report and then program that report to respond appropriately to those values.

As in all programming, it is important to perform operations in the $construct method of a report instance in the proper order. The report setup operations we explored last time must be completed before any record images are processed for printing. The order in which the parameters are passed is irrelevant (as long as the values and references passed to the report instance match their intended parameter variables), but the order in which operations take place is critical.

As soon as the first record image is processed, it is too late to modify overall report properties (like destination, sort fields, repeat factor, etc.). Page properties (like orientation, number of copies, etc.) that are modified after the first record image is processed will only apply to the next page, not the current one. Object properties that need record-by-record revision must be modified before that object is placed on the Virtual Section, otherwise the updated property value will not take effect until the next record is processed.

These are the rules by which the report instance lives. We cannot change them, but must work within their purview.

Look But Don't Touch

This may be obvious to most Omnis Studio programmers, but it must be included here. We generally want to avoid changing the Report Class with our setup code. It is better to make all such changes in the current instance. That way, we always know from what state we are starting the process for a given Report Class. This means that commands used to modify the report and its components in deployed applications should point to things notationally that begin with $cinst rather than with $cclass or $clib.$reports. There may be legitimate reasons for not following this suggestion, but do so at your own risk.

We also want to avoid changing data with our data gathering and processing code. Again, this should go without saying. The reporting process is an observational one. We only want to get a view of the data, not interact with it. There are rare exceptions where it is required that records be marked with the number of times they have been printed on reports, but I would suggest using a separate data structure for such monitoring and not putting that information into the record itself.

Instance Variables for Parameters

Some parameters we send to the $construct method just need to do their job and have no use throughout the rest of the report instance. But even parameters that define the range of records for the report may need to be given instance variable counterparts if our finished report is to be truthful and meaningful. That is, somewhere in the title, subtitle or header or footer information on the printed page we should tell the reader of the report what part of the data that report represents. Even a mailing label report would do with a cover page giving the current date and record selection criteria for that set of labels. This requires that we put in some extra effort.

Parameter variables are local in scope, which means they cannot be directly represented or used by objects on the face of the report. They also cannot be seen from any subroutine invoked from the $construct method (although their values could be passed), or from any field or section $print (or other) method during the report process. If any such method or object might require knowledge of a value passed to the report's $construct method, an instance variable should be established to accept that value. Then code must be added to the $construct method to transfer the value of each such parameter to its matching instance variable. That value will then be available throughout the report instance. The sooner we perform this housekeeping task in our $construct method, the better.

Of course, this can also impact the setup process we discussed in the last article. It is possible that a report may have been created for different sets of selection criteria based upon the parameter values received. For example, a range of dates may be used, or a specific customer number, or both. This should be reflected in the descriptive information printed on the report, which means our $construct method may have to change the content of a text object (in the Page header section or elsewhere) to contain the proper string (which may include square bracket expressions or directly calculated values) for displaying the content of the parameters.

This may seem like a lot of work, but the results are well worth the effort!

Gather Ye Records...

So much for setup processes, now let's get down to work!

Omnis Studio itself gives us clues and suggestions as to how to structure our data gathering process. When left on its own, the Report Generator gathers in just enough data values to perform the sorting operation for the report. It then brings in the details as it processes each record in sorted order. I suggest that this is a good model for us to follow much of the time. If our data set is small enough, we could read all the information the report needs into a list variable and let automatic processes sort and print it from there (as discussed in the article "List-Based Reports" published November 4, 2004). But we are considering reports with more complexity than that - and we should also consider reports that could become quite large - even larger than our computer's memory capacity. This strategy allows us to handle very large reports.

The trick for dealing with large reports is to gather only as much data as we need for sorting purposes. (That is, of course, unless we believe the very records we need to report on could be changed by others while our report process is running. It is rare that reports would be generated from such volatile information, but not impossible.) Even if we are using an SQL database where we can specify an order by clause for our result set, we still might want to limit the size of that result set on the first pass.

So I suggest we set up a list variable to use as a sort buffer, just like Omnis Studio does. This list should include columns that match all the sort fields defined for the report. Even if we use our own code to sort this list, Omnis Studio still uses the set of sort fields to monitor changes in value that trigger page breaks and various levels of subtotaling.

We must also be sure to include the primary key of the central table for our report. Every report will have one table at its core, even if data from many other tables must ultimately be included. The primary key (or unique index) is the value we will need to retrieve the complete record (or at least the parts we need for the report if we're using an SQL database where we can limit the columns returned in the result set) as we work through the list to process each line. Our purpose in building and sorting this list is to get those key values into the proper order for the report. The proper order makes it easier to read the report and find specific details. It also makes the gathering of intermediate aggregate values (subtotals, etc.) work as expected.

Once this list has been defined (either from an SQL class or using File Class variables), we then use the data-limiting parameters passed to the report's $construct method to retrieve record images into the list (limited by the where clause of a select statement or by search criteria on the native Omnis database). At this point, all setup processes must have been completed. We have now begun the process of actually generating the report. The wheel is spinning; the dice are rolling; no more bets may be placed and all bets are final.

Processing

Once our sort buffer has been populated and sorted, we can proceed with building the report image. We loop through the sort buffer list and retrieve each record in its turn. We do this using separate, but more inclusive, select statements or native Omnis record location commands. We want to do this in such a way that we fill the variables that are used by fields and calculations on the face of the report. If we are using the native Omnis database, we most likely are using the Current Record Buffer and variables from a File Class (except for calculated values). If we are tapping into an SQL data source, we should use a row variable of instance scope defined from the appropriate SQL class for this purpose. That row variable must have a column for each column in the result set, which may very well be more than just the columns for a specific Schema Class.

To loop through the sort buffer, we could use a process like this:

Calculate maxline as listVar.$linecount
For linecounter from 1 to maxline step 1
  ; retrieve, process and print current record
End for

The variables maxline and linecounter are local variables. The variable listVar is our sort buffer.

If we are using an SQL data source and network traffic is a concern, we have another option. We can send a select statement to the server that encompasses the complete set of core records for the report and then fetch those records one at a time into a row variable for processing and printing rather than fetching the entire set into a list. We must make sure that our original select contains an appropriate order by clause so that subtotaling and page breaks are properly triggered. Here is an example of what that code might look like:

Do recordRow.$definefromsqlclass('invoice')
Calculate sqlText as con('select * from invoice where datecreated >= ',kSq,startDate,kSq,'
       and datecreated <= ',kSq,endDate,kSq,' order by customerid,datecreated;')
Do statementObjRef.$execdirect(sqlText)
Repeat
  Do statementObjRef.$fetch(recordRow) Returns result
  ; additional processing and printing
Until result=kFetchFinished

For reasons of space conservation, a join on customer was not included in the select given above, nor was the usual error checking code. Also, the variable result is a local variable of Boolean type. (The process indicated by the commented line should be enclosed by an appropriate If... statement, since the fetch that signals the end of the loop will return empty.)

A similar option exists for native Omnis if we know that our target records lie in a closed range of a given index (like a range of dates) for our Main File. If we need no other sorting (that is, if we only require the records to be in the order implied by that index), we can simply retrieve those records one at a time using that index. For example, perhaps we need to report on invoices for a range of dates and the only order we need is the date (we might want to get a subtotal of sales for each day). We could then use code like this:

Find on invoiceFile.date {startDate}
While invoiceFile.date<=endDate
  ; process and print current record
  Next on invoiceFile.date
End While

The variables startDate and endDate are instance variables who received values from parameters earlier in the process. Of course here we could not include an additional sort on customerid, so this is not the same as the SQL example just above. This can only follow the date index from invoiceFile. (And again, a test of the current record using an If... statement may be required before processing and printing.)

Additional Information

But we may need to do more than simply retrieve a basic record image and print it. The basic record we want to represent in the Record Section of the report may require one or more auxiliary records as well. Sometimes it is easier to retrieve that information separately when using an SQL data source. Using native Omnis, it is always necessary to retrieve related records with separate command lines (except for Connected records, which are automatically retrieved with the Main File record).

It is also possible that one or more lists of sub-records may be needed in our Record Section. For example, our focus in a report might be a collection of invoice records, but we may want to include the line items for this invoice. Yes, it is possible to make the line items the central table of the select and then use additional subtotal levels to format the resulting report, but think of how much redundant data must be retrieved in so doing. And what if we require more than one list per record? I once had to create a series of reports where the Record Section might contain up to four lists of auxiliary data, depending on what type of item appeared in that Record Section instance. (And this was in Omnis 5 where we also had to parse such lists into return-delimited strings!) In Omnis Studio we now have two kinds of list display field for reports (Report Data Grid and Wrapping List fields have been detailed in earlier Tech News articles), so building auxiliary lists for records fetched from a basic select offers some benefits over trying to squeeze every last character for the report into the result set of a gigantic select statement.

Whatever additional command lines or subroutines are required for this processing must all be completed before the next step is taken...

Printing

The command we must use to launch each Record Section when gathering and printing records from within the $construct method of a report is:

Do $cinst.$printrecord()

The Print record command does not work for us here. Apparently, since we are overriding the "normal" reporting process, we need to do this using Notation. The $printrecord() method takes no parameters, we simply supply the parentheses to ensure that the method executes.

While executing this command line causes the Record Section to be processed and added to the Virtual Page, it is still possible to perform additional auxiliary processes (including the retrieval of data from the database) using the $print methods of the objects in the Record Section. While I would recommend that this be done either directly within the $construct method or in some method called from there, this $print technique can also work. We just have to make sure the process is performed in the proper order - and that's where things can get tricky...

Each field on the report is processed in its turn. Its value is set and/or its calculation is evaluated. Then its (built-in) $print method is executed. This happens one line at a time (top to bottom in the section). Objects on a line are processed in left-to-right order. Those that have the same left coordinate are processed from back to front (so that the top one overprints those below). If we want to override the $print method of a field in the Record Section (or any other section for that matter), to affect the value of another item, then we need to do so in a field that is processed before the target item. Again, this is all very orderly - we just have to understand the order and work with it.

As Record Sections are sent to the Virtual Page, the Sort Fields still monitor the need for page and subtotal breaks and the Aggregation Buffer still accumulates and appropriately releases aggregate values when a Subtotal Section is required at some level. While there are even ways to override and control this behavior, that is beyond the scope of this article.

So Omnis Studio will continue printing our report as we cycle through retrieved records in our $construct method. But what then? What happens when this method is finished?

Finishing

Depending on how the report is launched, it may want to create its own version of the report once the $construct method has completed execution. After all, if we tell Omnis Studio to Print report, it will dutifully attempt to do so - and $construct is just the setup process as far as the Report Generator is concerned. But in this case, we want to prevent that from happening.

We also want to be certain that the report instance cleans up after itself. Even if we simply $open() a report instance, we still need to put it away when the reporting process is over.

We can keep the Report Generator from appending its own version of our report, or we can simply put a report instance away, by issuing the command:

Do $cinst.$endprint()

This is (somewhat) the equivalent of the $close method for a window instance. It tells the report instance that its job is done. But it does more than that. The $endprint() method also triggers the last subtotal section for all levels of subtotaling in effect. It then triggers the Totals Section and the End of Report Section. The End of Report Section performs various cleanup operations, like de-allocating the memory required for the Aggregation Buffer and the various virtual workspaces Omnis Studio uses to lay out our report pages. This is all necessary for our report instance to properly close up shop after doing its job.

If we have printed our report to the screen, however, this does not close the screen report. A screen report is not a report instance, but it is the output of a report instance. It can stay around long after the report instance has gone away.

Summary

So here are the generic steps that we follow to control the report process from inside the report instance:

Manage parameters
Setup operations
Gather sort information
Sort
For each line
  Retrieve complete record
  Perform additional processing
  Print record section
End for
Close the instance

Pretty simple when put that way!

In Closing

For the next few months, we will take a break from reports and look at other aspects of Omnis Studio. We have spent many months on this general topic and it's time to move on.