Developers

Carrying Over Aggregate Values In Omnis Studio Reports

By David Swain
Polymath Business Systems

This article is intended to follow on from an article I wrote on "Aggregate Values in Omnis Studio Reports" back in January of 2004. Some recent questions have arisen on this topic on the Omnis Developer mailing list and I thought it would work best to handle those here (instead of writing lengthy, article-sized emails to the list). The original questions had to do with tracking cumulative aggregate values in an Omnis Studio report, but there are some related techniques that could prove interesting and useful as well.

A Brief Review Before We Begin

To summarize what we covered in the earlier article:

• If we want to gather aggregate information for a row-by-row variable in our report, we must place a field for that variable within a subtotal or totals section of the report and set the totalmode property value of that field to some value other than kTmNone.
• Doing this for a field in the Record section has no effect (indicating that this feature of Omnis Studio works differently than it did in earlier version of Omnis for those Omnis 7 developers just joining us).
• Having done this, Omnis Studio sets up the complete Aggregation Buffer slot for that variable when an instance of this report is launched, which includes all types of aggregation at all subtotal levels.
• If we want to access any of the aggregation types for that variable within a subtotal or totals section, we can use the name of the variable followed by the appropriate notational aggregation property - the variable name alone will only give us the current value of that variable (also a departure from past versions of Omnis).
• These values are only available within a subtotal or totals section - either on the surface of the report or within the sections $print method - and we can only access the aggregation values for the current level of subtotaling.

Here is a list of the notational aggregation properties and their corresponding totalmode constants:

The aggregate values we can accumulate using these tools only apply to the subtotal grouping of rows that immediately precede the current subtotal section. But what if we want or need to see the values grow toward the ultimate total as each subtotal grouping is processed in what is called a running total? (We are most likely to use this with counts and totals, by the way.) This presents us with more of a challenge.

Hash Variables Have Their Place

The most likely way of accumulating aggregate values during the execution of a report would seem to be to use another variable for the accumulation. But those developers who have been inculcated with the notion that we must always use instance variables in Omnis Studio find that those variables are not stable enough (currently) to hold subtotal accumulations. They tend to "zero out" once execution progresses beyond the subtotal section in which they are set. A simple experiment will demonstrate this.

But first, let's demonstrate that running totals (or counts, in this case) can, in fact, be accumulated in some way. For this, we must use the lowly hash variable, which is apparently the only type of variable stable enough for this purpose. Let's make this really simple and obvious!

I have a table of people (members is the name of the table) that includes a memberType column. There are five possible values for this column (0-4 inclusive) and I want to see how many people are in each type. I can accumulate a count on any column that I wish, but it is best to use a column that contains no NULLs since NULLs are usually skipped by aggregation functions in most languages. So I will choose the address column to gather my count.

To focus our attention even more, I will suppress the printing of the Record section so that we only see the subtotals. The Sort Fields for this report are set with only one entry: The first level of sorting will be in members.address and the Subtotals attribute for that level is set to kTrue. I have no fields placed in the Record section, but I have made sure that I have a kSubtotal1 section added to the report. In that section I have placed two fields. The first field is given the dataname property value members.address and its totalmode property value is set to kTmCount. The second field is given a dataname value of #10 with kTmNone as the totalmode value. In addition, the second fields calculated property is set to kTrue and the text property (where we must put the calculation for that field) is set to #10+members.address.$count. In the $construct method of the report class, I put the following code:

The purposes of this code block is twofold: To initialize the #10 variable and to do so without the execution of the report interfering with any ongoing use of that variable. This way, we can run this report over and over again and get a consistent running accumulation in #10. It is also important to make sure that #10 is not NULL, since the result of adding to NULL is still NULL.

Here is what the report output looks like:

So here we see that it is possible to generate a running result in an Omnis Studio report subtotal section. Now let's see how variables of other scopes fare in this same situation.

Hash Variables Rule!

There are four variable scopes that we will explore in this next experiment. We don't bother with local variables because they can't even be used as the dataname value of a field on the report surface. But it is important for us to explore all the possibilities, so we will try the same technique with an instance variable (ivRunning), a class variable (cvRunning), a task variable (tvRunning) and a variable from a memory-only File Class (flags.Running). The original field for #10 is copied four times and all references to #10 in the dataname and text properties are replaced with the appropriate variable. To make the report output easier to read, I included a Page heading section with Label fields to label the columns that are generated. I also changed the $construct method to:

Here is the output of this experiment:

Notice the difference. My best guess as to what happens with scoped and memory variables is that they are either zeroed out after the subtotal section is added to the virtual page or they are simply not present during the presentation of subtotal information. Either way, we can't count on them for carrying forward accumulated aggregate values like this.

So why do the hash variables work then? Well, it's only a guess, but my guess is that these variables are so completely outside of scope of the report instance that they are unaffected by report processes. They just exist universally - and can therefore be queried and updated as we need from anywhere at any time. At least they serve our purposes well in this case!

What About Accessing Aggregate Values From Our Methods?

Are the fields on the report layout the only places where we can access aggregate values? At one time that appeared to be the case, but recent versions of Omnis Studio give us notational access to the current set of aggregate values from within the $print method of the subtotal section with which those values are associated. My guess is that each variable is aggregated as it is processed through the Record section, so the final aggregation set is available as soon as a subtotal section is triggered. This means that we also have access to this information in the custom $print method of each subtotal section - if we choose to include such a method.

We can demonstrate this by creating duplicates of the reports we just built and then modifying them to generate the running subtotals from the $print method instead of using calculations in the report fields:

• Duplicate the first report we made for this article.
• Change the properties of the field we made for the #10 variable so that the calculated property is not kFalse and the text property is empty.
• Double-click on the Subtotal1 section banner. This will open the Method Editor to the $print method for that section.
• Add the following lines of code:

This will make sure that the calculation is performed before the section is printed so that the value we calculate is available for that field. Now print the report (to the screen). Notice that we again get our running subtotals. Perform the same kinds of changes to the second report and notice that we get the same results: Only a hash variable is stable enough to use for running subtotals using this technique as well.

Expanding The Basic Technique

There are a number of variations on and extensions to this technique that we could devise. For example, we could perform a feat that is somewhat the reverse of what we've done so far. Suppose that we are accumulating values, such as sales data, where we want to compare that aggregated value from one period to the next - to see whether sales have increased or decreased quarter-on-quarter, for example. In this case, we only want to carry the aggregate value forward into the next subtotal section that prints, perform the comparison calculation and then pick up the new aggregate value to carry into the next subtotal section and so on. In fact, let's do it with two aggregate values while we're at it!

Suppose we have some sales data with invoice number, invoice date and invoice amount. We want to summarize this data over a number of years with subtotals generated at the end of each quarter. In this summary, we want to get a count of the invoices (using the invoice number column) and a subtotal of their amounts. These are the two aggregate values that we want to carry forward for comparison, so we'll need two hash variables to perform that feat. (We'll dispense with demonstrating that hash variables are the only ones we can use from here on, but you're welcome to experiment on your own.) We'll use #14 for the invoice count and #15 for the total amount. We initialize these in the reports $construct method as before:

We then need to set up the fields on the layout of the report class. We don't need any fields in the Record section since we will set the repeatfactor property value for the report to 0. We will add a Subtotal1 section to the report class, as well as a Page Header section. In the subtotals section we will place a field for the invoice date, one for the invoice number and one for the invoice amount. The number and amount fields will be used to display the appropriate aggregate value ($count for number and $total for amount), but we will leave the date field to simply display the current value. As long as we leave the noreload property value of that field set to its default value of kFalse, the value from the last record processed by the Record section will be reloaded for use in the Subtotal1 section. Since we will be sorting the records for the report by date and then setting the sort fields to trigger a subtotal at every change of quarter, this field will then show the last date value from our data for the quarter represented by that subtotal section. This is not necessarily the last calendar day in the quarter, since we might not have made a sale on that date, but it will be close enough for this experiment.

Just to the right of both the number and the amount fields, we will place two additional fields. The first of these will be used to display the difference between the current aggregate value and the one we will carry forward from the previous subtotal. The other field of the pair will simply indicate whether there was an increase or a decrease in the subtotal from the previous quarter to the current one. Each field has its calculated property value set to kTrue and its text property value set to the appropriate expression. There is no need for a dataname property value, though, because we only want to display these values, not catch them in some variable. The expression for the difference field for the invoice count is:

invoices.number.$count-#14

And the expression for the direction indicator field is:

pick(invoices.number.$count>#14,'Decrease','Increase')

But where does the value for #14 come from? Somewhere after these fields have been processed, either to the right on the same line or on a line below this one, we place an invisible field with the hash variable set as the dataname, the calculated property value set to kTrue and the text property value set to the appropriate expression (here the expression is invoices.number.$count). The value of the hash variable is simply held until it is needed and then we reset it for its next use. So we could use this value in any other section, such as the following Subtotal Heading or even a Record section in the next subtotal grouping, as well as what we have shown here. When the report is completed, the value of each hash variable reverts back to its original setting before the report was begun because of the reversible block in which it was initialized.

Here is how the final report might look once it is run:

One More Possibility - For Now...

Another thing we can do, using a related technique, is to build a summary list of the subtotaled values for use in the totals section. We could then simply print out this summary or use it as the basis for a graph presentation. This technique requires the use of a hash variable list, which might best be defined from hash variables as well (although this probably isn't quite so necessary). We begin by initializing the list variable in the $construct method:

I made a copy of one of the reports we built earlier in this article, so we are again using #10 to accumulate and display a running total. The list has a different purpose. I chose to use the "current list" technique for this because I want to preserve the current definition and contents of #L5 in case it is being used for something else already. Reversible blocks can only reverse the action of a command, not the execution of a method - so I would not get the same effect if I defined the list using Do #L5.$define(). The execution of the $define() method would not revert. I have given this list two columns: a character string column for a label (in this case, the memberType value for the subcount) and a numeric column for the aggregate count. Then in the $print method for the Subtotal1 section, we add the line for that subtotal group:

The full text of the Add line to list command line shown above is:

Add line to list {(pick(
    members.memberType,'Freshmen','Sophomores','Juniors','Seniors','Advisors'),
    members.address.$count)}

In this case, there are only five possible subtotal groupings because there are only five distinct values allowed for the memberType column.

The list lines that are accumulated as the report progresses are then displayed in the Totals section at the end of the report. A report Data Grid field is used here to present the final list variable value. Beyond setting the dataname of this field to #L5 and the two column calculations to #S2 and #20 respectively, there is nothing special or unusual done with its other property values. The result is a report that ends like this:

The Summary List is from the Totals section here while the individual lines are separate Subtotal1 sections.

Now that we have this list working nicely, let's do something more interesting with it - let's use it to display a pie chart using the Graph2 report object! All we need to do is place a Graph2 object into the Totals section of the report, set its dataname value to #L5 and set the majortype property value to kG2pie. An appropriate maintitle value is a good idea too. Otherwise, the rest of the graph takes care of itself:

Nothing like a nice graph to spice up a report!

Closing Thoughts

Once we understand that hash variables are not the pariahs that some would have us believe, we can use them to transport aggregate values from place to place within a report. There are plenty of other variations on these techniques that we could use, but this should be enough to get you going. It wouldn't take too much more effort, though, to implement cumulative aggregation on multiple levels, to perform year-on-year comparisons for a given quarter - or any other useful analytic addition to an aggregate report.