Developers

Aggregate Values in Omnis Studio Reports

By David Swain
Polymath Business Systems

Every month or two a question is posted on the Omnis Underground list server regarding "totals" in Omnis Studio reports. The concepts behind and practice of dealing with aggregate values are actually quite simple, but veteran Omnis programmers who have gotten used to the way Omnis 7 handled them sometimes have a difficult time switching to the "new and improved" techniques for Omnis Studio. Let's try to clear this up!

Aggregate Values Defined

For any variable in an Omnis Studio report, we can track five aggregate values. An aggregate value for a variable is one derived as a summary of the individual values that variable contains during the process of building the report. As values pass into and out of that variable for various records, aggregate values can be accumulated in a special buffer. These cumulative values can then be tapped from within a Subtotal or Totals section when the printing of such a section is triggered.

The choices of aggregate value types are: total, count, average, minimum and maximum. Here is a brief description of each:

Basic Technique

To cause Omnis Studio to track aggregate values for a variable used in a report, we must place a field that represents that variable (one that has the name of that variable as its dataname property value) in a Subtotal or Totals section within the report and set that fields totalmode property to some value other than kTmNone. It is that simple. Setting the totalmode property for a field in the Record section (or in a Page Header or Footer section) has no effect on generating aggregate values for the associated variable in Omnis Studio.

There are five choices for the totalmode property value besides kTmNone (which has a numeric equivalent of 0). Each value is given as a property constant, but each such constant also represents a numeric value (shown here in parentheses) that can be used in its stead. The property constants are: kTmTotal (1), kTmCount (3), kTmAverage (2), kTmMinimum (4) and kTmMaximum (5). When dynamically setting the value of this property, either a property constant or its numeric equivalent can be used...but we won't be doing anything that advanced in this article. Perhaps a simple example is in order.

Simple Count Example

Let's create a report to demonstrate the basic technique for deriving an aggregate value. Suppose we assign a file named "customerFile" as the Main File for this report class. In the Record section we place a field representing the variable customerFile.lastName. In the Totals section we place a field representing the variable customerFile.address2. Both variables are of Character data type.

For the field in the Totals section we will also set the totalmode property value to kTmCount using the dropdown list of possible values provided. The Totals section will then show us how many records used for this report have a non-NULL value for customerFile.address2. This simple layout will look like:

On printing the report to the screen for my test database, the Totals section displays the number 161. This is, in fact, the number of Customer records in my database. If I change the value of customerFile.address2 in one record to be NULL (by calculating it as the value of #NULL), the Totals section displays 160 the next time it is printed (thus demonstrating that NULL values are not included in the count).

This is just one example. The other aggregation types work in a similar way. There are just a few additional things we must know to take advantage of this facility.

Basic Rules

There are a few basic rules as to how this technology works. Understand these and you should have no problems with "totals" in your reports.

First, only a field with a non-zero totalmode property value can turn on aggregate value tracking for its associated variable. If no such field exists for a variable, no aggregate values are accumulated for it.

Second, if tracking of one aggregate value type is switched on for a variable, all five aggregate values are actually tracked. So if we switch on tracking of a count for a variable, the total, average and the others are also accumulated - even if we never do anything with them.

Finally, if aggregate values are tracked at one subtotal level, they are tracked at all levels. We don't need to place a field with a non-zero totalmode value in each subtotal level section for the same variable in order to use that variable's aggregate values in each of those places. Only one is absolutely required. We will see how this can come into play in the next section.

Using Aggregate Values In Expressions

There will be times when we need to access an aggregate value in a way other than displaying it in an Entry field with a specific totalmode setting. For example, we may need to derive other values from one using an expression. Or we may want to display one in a Text object using square bracket notation. In either of these cases, we need to use Omnis Notation to access a specific aggregate value for a given variable.

Since each such variable automatically has all five aggregate values tracked for it, Omnis Studio can't make any assumptions about which of those values to use in a specific place. So we need some means of stipulating this ourselves. Omnis Studio has just the answer...

The creators of Omnis Studio simply extended Omnis Studio Notation with properties for variables that allow us to access their aggregate values in a report. We can use these property names just as we would for working with the properties of a component object on a window. The five aggregate value properties for a variable are: $total, $count, $average, $minimum and $maximum. So to access the count value for our customerFile.address2 variable somewhere in a subtotal or totals section, we would could use the notation customerFile.address2.$count.

The scope of these properties is extremely limited. They are only in scope while the subtotal or totals section prints. Even just before or just after the Do default command line in the $print method for such a section, these properties are out of scope. If we need to bring an aggregate value back into the methods of our report, we must transfer it to another variable out on the report "surface" that can retain the value beyond the "life" of the section. Interestingly, hash variables seem to be the most stable for this purpose. We will examine this in a later article in this series.

Let's look at another, more complex example that illustrates a few more of the principles mentioned above.

Subtotal Example

Let's modify the report we built earlier in this article. First, we will add a Subtotal level 1 section to the report by setting the report's subtotal1 property value to kTrue. Notice that three lines are added to the report class in addition to the section banner. Next we will move the field that was in the Totals section up into the Subtotal level 1 section. Now we have a few changes to make to this field.

We are going to change the dataname property value for the field to customerFile.dateEntered. This is a field of Short date data type that is populated with the current date when each new record is created. For the moment, we will set the totalmode property value of this field back to kTmNone to prove a point...then we will do something more interesting.

In the Totals section we will place five Text objects. (If you use Label objects, this will not work as they do not support square bracket notation.) Into these five objects, we will place the following five text strings:

Count: [customerFile.dateEntered.$count]

Total: [customerFile.dateEntered.$total]

Average: [customerFile.dateEntered.$average]

Minimum: [customerFile.dateEntered.$minimum]

Maximum: [customerFile.dateEntered.$maximum]

The layout for this report class should now look like:

Before we print this, we must also set the sort order for the report. I am setting the sort variables as follows:

So a change in the stateCode value will trigger the printing of the Subtotal level 1 section and the lastName values will be sorted alphabetically within each State. We are now ready to print this report to the screen.

When we print the report, we see a date value at each subtotal break and a lot of empty values following the label headers in the Totals section at the end of the report (except for Count:, which is followed by 0). This is because no aggregate values are being tracked for the customerFile.dateEntered variable. If we set the totalmode property value of the field in the Subtotal level 1 section to kTmCount, we get a much different result.

We now get a count value at each subtotal break. This shows us how many people are listed for each State. Remember that this is the only field that sets any kind of aggregation for customerFile.dateEntered - and it only set up the count. We demonstrated above that the notational aggregation properties do not cause aggregation to be applied to the associated variable.

But now, look what is happening in the Totals section! Here is what it looks like in my report:

The $count property returns the number of records involved in the report (none of the dateEntered values were NULL). The $total property shows the sum of the numeric equivalents of each date value involved in the report. This is a very large number - and not terribly meaningful for a date variable. But the $average, $minimum and $maximum values show more promise. Even though they are also large, they can be converted back into date values using the dat() function. If we change the text value of these Text objects as follows, we get the result below:

Average: [dat(customerFile.dateEntered.$average)]

Minimum: [dat(customerFile.dateEntered.$minimum)]

Maximum: [dat(customerFile.dateEntered.$maximum)]

We can apparently derive information about the range of dates encountered during the creation of this report. Perhaps you can conceive of a few possible uses for this...

So What Did We Learn From All This?

First, we learned how Omnis Studio expects us to set up report fields for generating aggregate values. We learned the five types of aggregation Omnis Studio offers to us, that all five are accumulated even if we only want one of them and how we can access these values if we need to do further processing with them. And we learned that there is a "scope" for these aggregate values that is quite narrow (although no examples have been given yet where we attempt to carry those values outside that scope).

Next Time

So far we have mainly talked about aggregate values in the Totals section that prints at the end of a report. We have not discussed in any detail how subtotal sections are set up and triggered. That will be the subject for the next article.