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.