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
     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
     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:
     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.
     NumberVar 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
     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.
     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
              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:
     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.
     CurrencyVar 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.
     CurrencyVar InvTotal;
     InvTotal := InvTotal + {transmaster.invoicetotal}
     CurrencyVar 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:
     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:
     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.