Understanding Report Writer Calculations

The Report Writer is a powerful part of SEQUEL. It lets you create virtually any type of report using either the easy-to-use Report Design Wizard or the graphical design screen. It provides multiple subtotal levels, multi-line formatting at any level, and complete skipping and spacing control. In addition, you can create powerful calculations to manipulate data or create new elements.

This article reviews several different report writer calculations—how they work and when to use them—and provides examples you can use in your own reports.

Calculation Sequence

By using the expression editor of SEQUEL’s report designer, you can easily create Report Writer calculations. Use numeric or alphabetic expressions to create new result fields, or to assign new values to View fields. You can make your calculations conditional by basing them on values in other fields, results of other expressions, or on a level break.

Make sure you enter your calculations in the expression editor in the order you want SEQUEL to execute them. Calculations at the top of the list are performed before those at the bottom. SEQUEL also distinguishes between calculations that it executes before or after it reads a record. In the example below, the first two lines (labeled “Before Read”) are executed before a record is read, so they are often called “before record read” calculations.

The Report Writer repeats the following steps until all records from the view have been processed:

1. Perform “before record read” calculations.
2. Read a record from the view and determine its break level.
3. Perform “after record read” calculations.
4. Print appropriate information, including detail information, heading, and break level lines.

Using aggregate expressions to summarize

The MIN, MAX, and SUM functions are aggregate expressions. They have a syntax similar to their SQL counterparts (used when you create the View), but they create results for each level of the report, rather than a single result for a group. These functions create a series of values (sometimes called a “vector” or an “array”), instead of a single value, for a chosen column or field for each level of the report.

You can easily calculate subtotals and grand totals using the SUM function. Only one calculation is necessary because SEQUEL automatically creates a separate result for each level of the report.

For example, when you place the aggregate expression @@EXTEND (see the image of the expression editor on page 1) on the print line for the order break level (ORDNO), SEQUEL creates a subtotal for each order number it prints. If you use the same expression on the customer name level (CNAME), SEQUEL prints the subtotal for each customer. If you use the same expression on the grand total format, SEQUEL produces a total field for the whole report.

Using aggregate expressions to count

Counters come in handy in Report Writer. You can use them to create numbered lists or make them part of your IF-THEN-ELSE logic for conditional calculations (“Do X if the counter equals Y” situations). Let’s look at a simple, yet powerful, calculation that you can use many different ways:

COUNTER=SUM(1)

This calculation is an excellent way to count the number of detail lines in the report. Because it uses SUM, SEQUEL considers it an aggregate expression and summarizes and prints the number of records at each subtotal level of the report. For example, it can show customers in each region, orders for each customer, line items in each order, or a total record count on the grand total.

Aggregate expression considerations

When you use an aggregate expression in a conditional calculation (IF-THEN-ELSE), you must think about the question of “when.” An aggregate expression creates an array based on the level breaks in the report, in other words, “when” the data changes. Therefore, one calculation can create many summary values depending on the break levels on which they are printed.

In a conditional calculation, the answer to “when” is sometimes ambiguous. Is the condition true at this level or that level? The calculation doesn’t know—it reads the condition at the detail level and adds up the values where the condition is true.

Here’s an example of an expression that suffers from ambiguity. Without further qualification, this expression generates the error, Vector assignment not allowed for conditional expression.

To fix this problem, you need to create two calculations. On the SEQUEL support team, we call this “create a bucket” and “sum the bucket.” First, we create a bucket:

When the condition is true, SEQUEL places the amtdu value in the bucket. Next, we sum the bucket:

It’s that easy. Now, you can use this calculation on any break level of your report!

When you use aggregate expressions, you may be tempted to use fields that are the result of an aggregate expression in another calculation. In a word, don’t—SEQUEL won’t let you.

The example below uses two aggregate expressions (TOTAMT and COUNTER) in the AVERAGE calculation. This produces the error message, TOTAMT cannot be used since it is the result of an aggregate expression.

You can easily solve this problem by basing the calculations on the expressions rather than on the field names as shown below:

Using level breaks in calculations

Earlier, we used a level break value to avoid a vector error. These values have other uses—with break values, you can create a type of conditional calculation.

In the next example, SEQUEL performs calculations at the ORDNO level break only when the order number changes. Similarly, SEQUEL performs calculations at the CUSNO level break only when the customer changes. This allows SEQUEL to count information beyond the detail level of the data.

Notice the “before record read” calculation at the top of the list. SEQUEL increments ORDS only once for each order and resets the value to zero before each customer. It then increments CUSTS once for each customer. You don’t need to reset the CUSTS counter since it is designed to reflect a count for the entire report. (These two counters are shown in the final example at the end of this article.)

Conditional calculations

You can use conditional calculations to do many things. They are one of the most important features of the Report Writer.

This example creates descriptions for the order type (OTYPE) field, a single-digit code in the database. You can convert it to more meaningful descriptions by using several conditional calculations to populate the single calculated field (DESC). In this process, only the first calculation includes an ELSE clause. Succeeding calculations basically “undo” the effect of the previous ones:

Here is the final output with all the calculations.

For More Information

Report Writer calculations are very powerful tools. Refer to the ViewPoint User Guide or the online help for more information.

Contributed by Steven Smith, Technical Consultant