HansaManuals Home   >> Standard ERP   >> Report Generator   >> Creating a New Report  

Previous   Next               Entire Chapter in Printable Form               Search  

This text refers to program version 8.5  

Creating a Report Generator Report - Break Points, Subtotals and Totals

This page describes adding break points, subtotals and totals to a Report Generator report.

---

If you need to calculate and print subtotals in a Report Generator report, you need to sort the report so that the records that will contribute to a particular subtotal will be printed together as a group. The subtotals will be printed after each group.

We have already described one example of subtotalling, in step 4 on the Printing Information from the Secondary Register page. In this example, the report was a list of Customers, with each Customer's Invoices listed underneath the relevant Customer details, as follows:

Customer 1

Customer 1's first Invoice

Customer 1's second Invoice

Customer 1's third Invoice

Customer 2

Customer 2's first Invoice

Customer 2's second Invoice

Customer 2's third Invoice

Customer 3

Customer 3's first Invoice

Customer 3's second Invoice

Customer 3's third Invoice

This structure is effectively a list of Invoices sorted into Customer Number order, with Customer information inserted at the relevant points. The sort order of the Invoices (the secondary register) has been imposed by the primary register. This allows us to calculate various subtotals for each Customer (e.g. subtotals for number of Invoices, value of Invoices, quantity of Items sold, and so on), subtotals that are calculated from the secondary register. If you want a report to contain subtotals, you need a trigger (sometimes known as a "break point") to print them. In this case, the last Invoice for a particular Customer (i.e. the end of the secondary register loop) is the break point that causes the subtotals to be printed.

It may be that you do not want to print any Customer details in the report. For example, the report may be entitled "Invoice List" not "Customer List", and you need it to be a simple list of Invoices, sorted into Customer Number order and with subtotals for each Customer. One way to do this is to use exactly the same structure and simply not print any information from the primary register. To the person reading the report, it will appear as a simple list of Invoices. If you make the Invoice register the primary register, you can sort the Invoices into Customer Number order, but there will be no break point between the various Customers to cause the subtotals to be printed (the loop will continue from the last Invoice of one Customer to the first Invoice of the next Customer with no break).

This method (using a primary register to impose a sort order on a secondary register but only printing information from the secondary register) is a useful way to structure a report with subtotals. For example, you may want to print a list of Customers with various subtotals for each Customer Category. The Customer Category register would be the primary register, from which nothing would be printed, and the Contact register would be the secondary register. The report will loop through the Customer Category register. For each record in that register, it will search in the Contact register to find Contacts belonging to that Category, print them in a group, and then print the subtotals.

The drawback with this method is that you cannot use it if there is no register that you can use as a primary register to impose a sort order on the secondary register. For example, you cannot use this method to sort Invoices into date order and print subtotals for each date, because there is no register that stores dates. And, as already mentioned, you cannot make the Invoice register the primary register and sort the Invoices into date order, because then there would be no break point between the various dates to cause the subtotals to be printed. The solution is to make the Invoice register both the primary register and the secondary register. As the primary register sorted into date order, it will find the break points between the dates, and as the secondary register it will print the relevant Invoices and calculate the subtotals.

Follow these steps:

1. Create a new report in the Report Generator. On the 'Data' card, specify that the Invoice register is to be the primary register, and that the Invoices will be sorted by Invoice Date. You may want to use the [Selection] button to add a search for Invoices that fall within a specified period, as described here, and a second search to remove Invoices that have not been marked as OK from the report. Do not design any output on the 'Layout' card.
   

   

2. The intention is that the report will loop through the Invoices in the specified period. When the loop reaches an Invoice whose date is different to that of the previous one, there should then be a search in the secondary register (also the Invoice register) for Invoices issued on the new date. To test that the date of an Invoice is different to that of the previous one, each Invoice Date should be loaded into a variable. When the loop reaches the next Invoice, it will compare its date with the date in the variable (i.e. with the date of the previous Invoice). So, the next step is to declare a date variable at the beginning of the report.
   

   

3. In the previous Customer List example, the report looped through Customers in the Contact register, and for each Customer it searched in the Invoice register for Invoices issued to that Customer. The Invoice search took place once for every Customer. In this Invoice List, the report will loop through the Invoice register and when it reaches an Invoice whose date is different to that of the previous one, there should then be a search in the secondary register (also the Invoice register) for Invoices issued on the new date. In other words, unlike the Customer List, we do not want the search to take place for every record in the primary register, we only need it to take place when the date changes. We need a variable to record the fact that the date has changed and to trigger the search. If the variable is true, the search will take place. If the variable is false, the search will not take place. The next step is to declare this variable at the beginning of the report. This variable can be a boolean variable (can contain true and false) or an integer variable (can contain 1 and 0).
   

   

4. The next step is to add two lines of code to the primary register section of the report. The first will set the vbChanged variable to false when the loop reaches the next Invoice. The second will compare the Invoice Date of that Invoice with the date of the previous Invoice (in the vdDate variable). If the two dates are different, the Invoice Date of the current Invoice will be copied into the vdDate variable ready for the next comparison, and vbChanged will be set to true to trigger the search in the secondary register.
   

   

5. Specify that the Invoice register is to be the secondary register. Add a Condition that the secondary register will only be processed if vbChanged is true.
   

   

6. Use the [Selection] button to add a search in the secondary register section for Invoices whose Invoice Date is the same as vdDate.
   

   

   You may also want to add a search to remove Invoices that have not been marked as OK from the report, especially if you added a similar search to the primary register in step 1.
   

7. Since the report is a list of Invoices, you will need to use a variable to calculate the subtotals, not the [Total] button. If you use the [Total] button, Credit Notes will be added to a day's Invoice value when they should be subtracted. Declare the variable at the beginning of the report, and add the code to the primary and secondary register sections to calculate the subtotals. You will also need to use a variable to display each individual Invoice value if you want Credit Notes to be shown as negative figures:
   

   

8. Design the output of the secondary register as necessary on the 'Layout' card.
   

   

9. This is the result:
   

   

---

Creating a Report Generator Report:

• Introduction
  

• Specifying the Primary Register
  

• The Appearance of the Report
  

• Choosing a Print Destination
  

• The Width of the Report Window
  

• Printing the Report
  

• Adding White Space to Improve Readability
  

• Adding Searches
  

• Adding a Secondary Register
  

• Using Variables and Formulae
  

• Printing Information from Matrices
  

• Bringing Information in from Other Registers
  

• Page Breaks
  

• Filtering Records - Print If
  

• Adding Check Boxes to the Specification Window
  

• Adding Radio Buttons to the Specification Window
  

• Searching for Records within a Period
  

• Break Points, Subtotals and Totals
  

• Blocks
  

• Syntax for Print If conditions, Formulae and Code
  

• Useful Functions
  

• Example - Listing Sales Figures
  

• Common Error Messages

---

Go back to:

• Home page for Report Generator manual
  

• Home page for Standard ERP manual

Previous   Next   Top            Entire Chapter in Printable Form