Developers

Sort Fields in Reports

By David Swain
Polymath Business Systems

In the last article we examined how to accumulate and display aggregate values for selected variables for the Totals section of a report. I also suggested that we can derive intermediate aggregate values as "subtotals", but did not go into any detail about this process. This was because we had not yet discussed how Omnis Studio creates subtotal groupings of records during the reporting process. The same Omnis Studio feature that allows us to trigger the release of subtotal information also allows us to determine the order in which records are presented in the printing of a report and to otherwise organize the report contents. This is one of the jobs of the Sort Fields for the report.

Order in Reports

When we build reports that involve multiple records from our database, we most often want to control the order in which those records are placed in the report. We may wish to print lists of people alphabetically by name, sales transactions by date or sports scores with the best at the top of the page. We may further wish to enhance the organization of a report by beginning a new page every time the first letter of the last name, the month part of the transaction date or the league to which the following teams belong changes. We may even want to trigger the printing of intermediate aggregate values on this or some other basis. All of these can make the report more meaningful and informative to the reader. These are among the uses of the Sort Fields assigned to a report.

The Sort Fields of a report is a special set of variables chosen from the variables involved in the report. We can specify up to nine such variables, although in practice most reports will have far fewer than nine sort fields. We can set up the sort fields for a report at design time, but we can also modify them using Notation (with some restrictions) at the beginning of the reporting process. We will examine only the "design setup" of sort fields in this article.

Accessing the Sort Fields Window

There is only one way to access the Sort Fields for a report at design time: We must open the Sort Fields Window. And there is only one means of doing this offered to us by the IDE: clicking on the "Sort Fields" tool of the Report Editor window's toolbar. There is no menu item or keyboard shortcut available as an alternative to using the mouse for this purpose.

If we click on this tool, the following window will appear:

On this window we specify up to nine sort variables and the characteristics we wish them to have for this report. The first column (labeled "Field Name") is used to specify which variables from the report will be used as "sort fields". The other columns are used to switch on or off various sorting options for each of these variables.

Variables Specified as Sort Fields

A "sort field" can be any variable that is in scope during the printing of the report. This includes class and instance variables of the report class, task variables, file class variables and hash variables. It even includes columns of a list associated with the report (even columns defined from a SQL class in Studio 4!) as long as that list variable is in scope. (Local variables are excluded as they are only in scope within the lines of the method to which they belong.)

Generally, a variable used as a sort field would be one that has the potential to change from one record to another. So #D would not make a good candidate, but invoice.date would (if the invoice File is involved in the report, that is!). The choices of sort variables determines the "informational shape" of the report.

Normal Sorting

The records in the report are sorted hierarchically in ascending order (as "value" is implied by the data type of that variable) by default. If multiple sort fields are specified, the second level of sorting only comes into play if two records have the same value (by default) for the first sort variable (or for the part of the first sort variable specified by the sort options detailed below). If two records have different values for the first sort field, they can be put in order without any further assistance.

In the following example, we have five sort levels: Country, City, Last name, First name and Invoice amount. Duplicate values that rely on the next sort level have been greyed out. Notice that this does not mean that each sort level is absolutely sorted. The values in the rightmost column, if taken out of context, would appear to not be sorted at all. But their sort order is hierarchically dependent on the sorting of the four columns to their left.

If two records have the matching values for all sort fields, they are put in the order in which they were introduced to the report. If they were directly taken from a File in a native Omnis datafile, they are put in Record Sequencing Number order. If they were brought into the Main List for the report, the line numbers into which the record images were originally read determine their position in the report. We can avoid having Omnis Studio make this decision for us, however, by using a primary key variable as the "lowest" level (highest numbered) sort field. This ensures uniqueness among the values (and, therefore, gives us proper control) at that sort level.

Here are the Sort Fields setting for the report above:

Sort Options

There are four operational properties or options available for a sort field. Each of these can take on a value of either kTrue or kFalse (the default value). The first two options determine how records are sorted, the other two are used to optionally trigger other actions during the printing of the report. Here are brief explanations of each of them:

Descending

Records are sorted in ascending order for a sort field by default, but there will be times when we need to reverse that order for certain sort fields. Switching the value of this option to kTrue causes records to be sorted in descending order of value for that variable. So, for example, we could display the invoices for each customer in descending order (with the largest one first) while still sorting the customers themselves in ascending alphabetical order.

Upper case

This option only applies to string variables and only affects the sort order of records in the report. It does not force the values to be displayed as upper case values nor does it modify any values stored in the database. It merely causes Omnis Studio to ignore the case of the characters in the string variable to which it is applied when it is switched to kTrue. Otherwise Character and National values would be sorted in ASCII or localized international sort order respectively, where lower case characters have higher values than their upper case equivalents.

Subtotals

If this option is switched on for a given sort field, a change in the value of the sort variable triggers the printing of the corresponding subtotal section (the subtotal level with the same number as the sort level of the sort field). Intermediate aggregate values for all variables that have been flagged for aggregate value tracking are made available within that subtotal section. These values were gathered only from the records processed by the report since the last subtotal section at this level was printed. The aggregation buffer for this level (and all "lower" levels) is cleared after the subtotal section is printed.

New page

If this option is switched on for a given sort field, a change in the value of the sort variable signals the end of the current page and triggers the initialization of a new page if there are additional records to include in the report. If all records have been included, a Totals section will only trigger a new page if the Totals section itself calls for one (as one of its properties) or if the page with the last record image (Record section) does not have sufficient room to include the Totals section.

Additional Subtotal and New Page Triggering Options

By default, any change in the value of the sort variable will trigger the printing of its corresponding subtotal section of the launching of a new page if such options are switched on. But we can have finer control over this process in Omnis Studio with additional options detailed at the bottom of the Sort Fields window. The additional Subtotal and New Page Options are independent of each other and are only available if the basic "Subtotals" or "New page" option (respectively) is switched on for that sort field.

The specific options offered for Subtotals and New Page depend on the data type of the variable used as the sort field. While we can technically use variables of any data type as sort fields, there are only three data types for which Omnis Studio can recognize ranges of values or sub-values: String (Character and National), Number and Date-Time. (Boolean variables are treated like Character variables in this case.) Variables of List, Picture and other data types do not make good sort field candidates.

Here are the options for Subtotal and New Page triggering by sort field data type. These can be set independently for Subtotals as for New page, but the options themselves are the same.

Character

We can specify the number of characters that must change (counting from the left-most character) to trigger a subtotal section or a new page. if we leave the value of this option at 0, any change in variable value will trigger the operation. If we set the value to 1, a change in the first character will trip the switch. (We might use this for an address directory on the Last Name field to trigger a new page when the initial letter changes.) If we set the value to 3, any change in the first three characters will do the job. (We could use this to get a count of address labels by Zip Code Zone - the first three digits of the Zip Code - for US postal codes.) Remember that the records are being sorted in this order anyway, so Omnis Studio is not going randomly from one value to the next for this sort variable.

Number

We can specify both an interval and the beginning value for Subtotal and New Page breaks on a numeric variable. So, for example, we can specify that we want a subtotal break for evey fifth value beginning with the value 3. This will give us subtotal breaks at 3, 8, 13, 18, etc.

Date-Time

Date and Time values (and the composite Date-Time type) have many options. Just think of the ways we subdivide time! By default, any of these will react to any change in their value (Date will break on a change in day, Time on a change in second and Date-Time on a change in centisecond), but we can choose from a plethora of useful intervals. Here are the options:

In the Next Article

Next time we will look at some more practical examples of sorting reports as well as how we can control (or in some cases create) the various sort options using Notation.