DOCUMENT #719
=======================================================================
ONCE-PER-BREAK OPERATIONS
=======================================================================
PRODUCT: R:BASE VERSION : 3.1 or Higher
=======================================================================
CATALOG: Reports AREA : Breakpoints
=======================================================================
When you create a breakpoint in a report, R:BASE sorts the column in
ascending order to create groups of like values. Break header and
footer sections allow you to print information only once for a group
of like data values (a break). Many times there is a need to process
an expression only once for a break. But report expressions are
designed to evaluate for each row from the driving table or view of
the report. In other words, expressions will evaluate for each row of
data in a break group, not just at the header or the footer.
R:BASE logical functions are used in expressions to "trick" reports
into processing a calculation only once-per-break. The logical
function expression is evaluated every row, but it does a comparison
which is true only once per break -- at the header or at the footer.
Only when the logical function comparison is true is the calculation
executed.
Using logical function expressions, you can do these once-per-break
operations:
- Count the number of breaks (different groups of like data values)
- Add a lookup value once per break.
- Print a descriptive heading on the first detail row without repeating
the heading on every row of detail.
- Add a blank line after every five lines of detail.
- Do calculations, such as a sum, of break footer totals
Most of these operations can be done either at the beginning of the
break (header), or at the end of the break (footer). It's faster to
do the once-per-break operation at the beginning of the break since
there are less expressions to evaluate and the expressions themselves
evaluate faster, but some calculations need to be done at the end
after all the data for the break has been processed.
If you choose to evaluate the once-per-break expression at the header
(the beginning of the break), you can locate the result in either the
header or the footer section. If you choose to evaluate the once-per-
break expression at the footer (the end of the break) you must locate
the result in the break footer. In all cases, SET ZERO ON before
printing the report.
Count the Number of Breaks
=============================
To count the number of breaks (the number of different values in the
break column) define these expressions to evaluate at the header:
vcount INTEGER = (.vcount + 1)
vbnum INTEGER = +
(IFEQ(.vcount,1,(.vbnum + 1),.vbnum))
Or these expressions to evaluate at the footer:
vcount INTEGER = (.vcount + 1)
vrowcount = (COUNT(*)) FROM rep_table WHERE break_column=break_column
vbnum INTEGER = (IFEQ(.vcount,.vrowcount,(.vbnum + 1),.vbnum))
Add VCOUNT to the break column's reset list. Then locate VBNUM in the
report footer (RF) section.
Add Lookup Once Per Break
============================
To total a lookup value, you need to use a once-per-break operation.
For example, if the lookup column (COLNAME) has a CURRENCY data type,
define these expressions to calculate at the header:
vcolname = colname IN look_table WHERE look_column=break_column
vcount INTEGER = (.vcount + 1)
vsum CURRENCY = (IFEQ(.vcount,1,(.vsum + .vcolname),.vsum))
Or these expressions to evaluate at the footer:
vcolname = colname IN look_table WHERE look_column=break_column
vcount INTEGER = (.vcount + 1)
vrowcount = (COUNT(*)) FROM rep_table WHERE break_column=break_column
vsum CURRENCY = (IFEQ(.vcount,.vrowcount,(.vsum + .vcolname),.vsum))
Add VCOUNT to the break column's reset list. Then locate VSUM in a break
footer or report footer section.
Heading on First Detail Line
================================
You can print heading information on the first detail line of a break
group without repeating the information on every line of detail. For
example, you might base the report on a transaction table, and look
up the company name in another table. The report might look like this:
Company A Transaction Information
Transaction Information
Transaction Information
Company B Transaction Information
Company C Transaction Information
Transaction Information
Define these expressions:
vcolname = colname IN look_table WHERE look_column=break_column
vcount INTEGER = (.vcount + 1)
vprint TEXT = (IFEQ(.vcount,1,.vcolname,' '))
Add VCOUNT to the break column's reset list. Locate VPRINT on the
left side of the detail (D) line. You don't need a break header or
break footer section but can include them. VPRINT contains the
company name on the first row of the break group but is blank on
all the subsequent rows.
To calculate the expression at the footer use these expressions:
vcolname = colname IN look_table WHERE look_column=break_column
vcount INTEGER = (.vcount + 1)
vrowcount = (COUNT(*)) FROM rep_table WHERE break_column=break_column
vprint TEXT = (IFEQ(.vcount,.vrowcount,.vcolname,' '))
Add a Blank Line Every Five Lines
=====================================
You can improve readability by adding a blank line after every five
detail lines. Define these expressions:
vcount INTEGER = (.vcount + 1)
vbreak INTEGER = (IFEQ(.vcount,6,1,0))
VCOUNT counts the number of rows, and the IFEQ function in the VBREAK
expression determines when five lines have printed. The IFEQ function
says, "If VCOUNT is six, set VBREAK to 1; otherwise, set VBREAK to 0."
Make VBREAK a breakpoint and add VCOUNT to its reset list. When you lay
out the report make a one-line, blank break footer section. R:BASE
prints a blank line when VBREAK changes value.
Sum break footer totals
==========================
Usually multiple levels of totals are done by repeating the same
totaling expression for a different variable name and not resetting
the variable. For example, to print total sales by region, salesperson
and then for the whole company, three variables are defined:
vregion_tot = SUM OF sales_amount
vsalesprsn_tot = SUM OF sales_amount
vcompany_tot = SUM OF sales_amount
Each variable has the same definition but accumulates the totals for
different groups of data based on when the variable is reset. The
variable vregion_tot is reset at the breakpoint for region, the
variable vsalesprsn_tot is reset at the breakpoint for salesperson,
and the variable vcompany_tot is not reset at all.
Sometimes, a sub-total or break total is not a simple SUM OF, but is
the result of other calculations. It may not be possible to duplicate
the expressions for a different level of total. By using the once-per-
break operation and setting it up to calculate the expression at the
footer you can simply total the break footer amount without duplicating
all the other expressions. Use the following expressions:
vcount INTEGER = (.vcount + 1)
vrowcount = (COUNT(*)) FROM rep_table WHERE break_column=break_column
vgrand_total CURRENCY = (IFEQ(.vcount,.vrowcount, +
(.vgrand_total + .vsub_total),.vgrand_total))