Doc# 747
=====================================================================
Once-Per-Break Operations in Crystal Reports
=====================================================================
PRODUCT: Crystal Reports VERSION: 1.0
=====================================================================
AREA: Reports CATALOG: Forms, Reports and Labels
=====================================================================
A common task in R:BASE reports is how to calculate or print a piece
of data only once for a particular break or group. The R:BASE report
writer is a single pass or row-by-row report writer -- it processes a
row and then prints it. It does not have any built in operations or
functions to do a once-per-break operation; to accomplish this, you
need to define variables. Previous R:BASE Exchange articles describe
the variables to define for the most commonly requested once-per-break
operations. The most recent article was published in the May/June 1993
Exchange.
This article shows how to accomplish these same operations in a
Crystal Reports report. Some of the operations require defining
formulas in Crystal, while others take advantage of Crystal's built in
formatting features. Because Crystal is a two-pass report writer -- it
reads and sorts all the records, then prints -- the logic involving
some of the operations is different from that used in R:BASE. The once-
per-break operations described here are:
Print a descriptive heading on the first detail row without
repeating the heading on every row of detail
Count the number of breaks (different groups of like data values)
Add a blank line after every five lines of detail
Add a lookup value once per break
Do calculations, such as a sum, of break footer totals.
The techniques cover basic reporting techniques that you will use
throughout your Crystal Reports.
Heading on the first detail line
Often you need to print heading information on the first detail line
of a break group without repeating the information on every line of
detail. For example, a report based on a transaction table prints the
company name on the first line of the detail section for each company.
A separate group header section is not used. The report might look
like this:
Company A Transaction Detail Information
Transaction Detail Information
Transaction Detail Information
Company B Transaction Detail Information
Company C Transaction Detail Information
Transaction Detail Information
In R:BASE you define variables. In Crystal you format the fields. All
you need do is check the "Suppress if duplicated" box for the field
location.
Count the Number of Breaks
When grouping data, you sometimes need to know how many different
groups are in the report. This number is printed in a footer at the
end of the report. You count the groups using the built-in Group
Number Field or by defining formulas.
The Group Number Field only numbers and counts one group, the
innermost. Use this option when you only have one break group in your
report or if you have multiple breaks but want to count the innermost
one. Define formulas to count for other situations.
The Group Number Field is numbering the breaks, it isn't really
counting them per se. It numbers a group and then increments itself.
So after the last group, the value in the GroupNumber function is
actually one more than the number of groups on the report. To print
the correct number of groups, define and locate a formula with the
expression, GroupNumber - 1. The GroupNumber function can be accessed
at the bottom of the functions list in the Formula editor under Other.
Locate the formula in the Page Footer section, or create a Grand Total
section. Format the field to remove the ".00" so it prints just the
integer value.
When the GroupNumber doesn't fit your situation, define formulas using
Crystal's two-pass capability to count the groups. The formulas are
defined using the "WhilePrinting Records" function -- wait to
calculate until all records have been read.
The first formula counts the groups. It is located in either the Group
Header or Group Footer section for the group to count. Hide this
field, you don't want it to print for each group. Define the formula
as follows:
WhilePrintingRecords;
NumberVar VCount;
VCount := VCount + 1
The Crystal formula sets the evaluation time, declares the variable
and increments it. By locating the field in the header or footer
section for the group, it only evaluates once per group and thus
increments by one for each different value in this group.
This formula is placed in a header or footer section to evaluate
correctly, but the value is printed at the end of the report. A second
formula is defined to print the result.
WhilePrintingRecords;
NumberVar VCount;
VCount
Again we identify the evaluation time and declare the variable. This
time we aren't defining a calculation, but simply the name Vcount.
This prints the result of our group calculations. Locate this formula
in a Page Footer or Grand Total section. Format the field to display
integer values only.
You'll notice some differences between defining formulas in Crystal
and variables in R:BASE. Crystal formulas are like programs with the
different commands separated by ";". You don't need parentheses around
the expression and Crystal variables are not "dotted". Also, you don't
equate items in an expression with an "=" sign but you use ":=". Don't
forget to use ":=", if you just use an "=", your formula results
become Boolean -- true/false.
For more information about the GroupNumber function and defining and
using formulas, refer to your Crystal Reports for R:BASE User's
Manual.
Add a Blank Line Every Five Lines:
To improve report readability by adding a blank line after every five
lines you use Crystal's conditional printing and formatting
capabilities. To find out when five lines have printed, use Crystal's
built in RecordNumber formula with the Remainder function. The
RecordNumber formula, like the GroupNumber formula, assigns a number
to each record when the records are printed.
Create a formula as follows:
if Remainder(RecordNumber,5) = 0 then " _" else " "
If five lines have printed, then the result of the formula is an
underscore, if not, the result is a blank. Locate the formula field on
a detail line by itself and shorten it so the underscore character
doesn't fit in the field. Then format the Detail section to suppress
blank lines. The line is suppressed except for every fifth one.
Because you shortened the field, the character doesn't print, it looks
just like a blank line; however, Crystal knows that the field does
contain a character and the line is not completely blank.
Subtotals
In R:BASE, multiple levels of totals are usually done by repeating the
same totaling expression for a different variable name and not
resetting the variable. For example, three variables are defined to
print totals sales by region, salesperson, and then for the whole
company:
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.
Crystal operates similarly when you insert subtotals for the various
groups and for grand totals. Crystal automatically resets the totals
for each group.
Sometimes, however, a sum function returns the wrong total. This can
be because multiple tables are used in the report and a value gets
added more than once, or because the value being totaled is the result
of other calculations. In R:BASE these are called "Add a lookup value
once-per-break" and "Summing break footer totals". In Crystal, the
same basic procedure provides the solution to both problems. The
technique can be used anywhere a sum function does not return a
correct result.
Add a Lookup Once Per Break
Whether you add tables to a report in Crystal or print from an R:BASE
view, you may need to total values from the "one" table. The values in
the "one" table are duplicated for each matching row in the "many"
table. Just doing a sum to get the total leads to the wrong amount,
the values are added in more than once.
The procedure to sum a value once for each particular group is similar
to creating a running total. Running totals are described on page 8-28
of the Crystal Reports for R:BASE User's Manual.
Formulas are used to do the totaling. You need two or three formulas
depending on whether or not you need to reset the total at a higher
level break group. Like the formula for counting the groups, one
formula at the group header accumulates the total, a second one in the
grand total section prints the result. To reset, you need a second
formula in a higher level group header section.
For a specific example using the Concomp sample database, include the
transmaster and transdetail tables together on a report. The tables
are linked by the transid column. The desired result is a grand total
of all the amounts in the invoicetotal column. The invoicetotal column
is in the transmaster table, the one side of a one-to-many
relationship. If we just sum the invoicetotal column, our grand total
is too big. The sum includes a value for invoicetotal for each
matching row in the transdetail table. The column needs to be summed
only once for each transid value.
First, insert a group section for transid. Then locate a formula field
in the transid group header section. The formula declares a variable
and accumulates the amounts from the invoicetotal column. Define the
formula, Report total, as follows:
WhilePrintingRecords;
CurrencyVar GrandInvTotal;
GrandInvTotal := GrandInvTotal + {transmaster.invoicetotal}
Because the formula is located in the group header, it is only
evaluated when that section executes, i.e. once for each transid
value. The WhilePrintingRecords ensures that the formula is not
evaluated and data accumulated until all the records have been read
and sorted.
The formula field is formatted to hide when printing. You don't want
the running total to print in the header. It is printed in a Grand
Total section using a second formula, Print report total.
WhilePrintingRecords;
CurrencyVar GrandInvTotal;
GrandInvTotal
This formula again specifies WhilePrintingRecords for correct results,
then declares the variable and simply reads the value accumulated
through the header formula. Even though the variable is the same, it
needs to be declared in each formula that uses it.
These formulas work when you have a single group, or are totaling the
value for the outermost group. If you wanted to have a total for each
customer, for example, and a report total you would need additional
formulas and group sections.
First change the groups. Group 1 needs to be custid, transid moves to
group 2. Add custid as a group, then select Edit|Group section to
change the group columns -- change Group section #1 to custid, Group
section #2 to transid. Move the formula @Report total to group header
2, now the group section for transid. Leave the formula @Print report
total in the Grand total section.
Now define formulas to accumulate the total for each customer. Two
additional formulas, Cust total and Print cust, are defined.
WhilePrintingRecords;
CurrencyVar InvTotal;
InvTotal := InvTotal + {transmaster.invoicetotal}
WhilePrintingRecords;
CurrencyVar InvTotal;
InvTotal
Notice that the formulas are the same as the others, just a different
variable name is used to accumulate the total. The first formula,
@Cust total, is placed in the Group Header section for transid and is
hidden. This variable will be reset for each customer. The second
formula, @Print cust, is placed in the the Group Footer 1 section, the
footer for custid.
Define a third formula, Reset cust, to reset the customer invoice
total variable, InvTotal. It needs to start at zero for each customer.
Place the formula field, Reset cust, in the group header for custid.
It is defined as follows:
WhilePrintingRecords;
CurrencyVar InvTotal;
InvTotal := 0
This resets InvTotal to zero for each new customer. The formula,
@Reset cust, is also formatted to hide when printing.
Sum Break Footer Totals:
When a subtotal or break total is not a simple sum of a column, but is
derived from other calculations, you get the correct total by summing
the group footer calculation amount, not the column. In Crystal you do
this with a series of expressions similar to the above formulas for
summing a value once per group. The only difference is that you place
the accumulator formula in the group footer section.
For example, place the formula, Cust total, defined above:
WhilePrintingRecords;
CurrencyVar InvTotal;
InvTotal := InvTotal + {transmaster.invoicetotal}
in the Group Footer section for transid. It evaluates only when the
Group Footer section is processed. It does not evaluate for every row,
so you can have other calculations that are evaluated in the Detail
section (every row) or at lower level groups where the final result of
the calculation, the value at the Group Footer, is accumulated.