Adding field breaks to an iterator
Field breaks are special header and footer forms that you can add to the body section of a Report Iterator to flag or operate on groups of information within the iterator. The following illustration shows a Report Iterator on a database query that groups the results by job type: one group each for clerks, managers, and sales staff. This Report Iterator includes the following sections:
• A header section for the Report Iterator, which provides column headings for the NAME, SALARY, and COMM columns of the result table.
• A body section for the Report Iterator that contains the result table information. This section contains a field break with the following parts:
o A header with the job field
o A footer with subtotals for the SALARY and COMM columns in each job group.
• A footer section for the Report Iterator that totals all job groups and counts the number of rows in the result table.
The following sections explain how to create this report.
Creating the report
To create this report, follow these steps:
1. Create a new report part.
2. Add a multi-row query part to the free-form surface and define the following query statement:
SELECT STAFF.JOB, STAFF.NAME, STAFF.SALARY, STAFF.COMM
FROM STAFF
GROUP BY STAFF.JOB, STAFF.NAME, STAFF.SALARY, STAFF.COMM
3. On the Update page of the query settings, make sure the read-only option is selected.
4. On the Fetch page of the query settings, make sure the Enable Packeting option is selected.
5. From the pop-up menu of the query part, select Quick Report and then select resultTable from the list that displays.
6. Select NAME, SALARY, and COMM.
7. Drop the quick report onto the body section of the report shell.
You now have a report iterator in the body of your report.
8. Select the report iterator and then press mouse button 2 to display its pop-up menu.
9. Select Add Field Break from the menu and then select JOB from the list that displays.
This step adds a header and footer to the body section of the report iterator. The header contains two report text fields: one is a label and one is the job type information from the database.
10. To add subtotals for each job type, add the following report fields to the footer section of the field break:
a. A Report Text Field with the text Subtotals:
b. Two Report Calculated Field fields.
11. From the Report Calculated Fields settings, select total from the displayAttriubte drop-down list.
12. Connect the formatted event of the [SALARY] field to the countObject action of one of the Report Calculated Fields in the footer section of the field break. Make a similar connection between the [COMM] field and the other Report Calculated Field.
The formatted event is signaled after the information in the field has been formatted. You connect to this event to ensure that the field has information in it before attempting to run a calculation on it.
The report now has all of the functions it needs to print the query results with field breaks on the JOB column of the container.
Formatting the report
You can format your report as shown in the illustration at the beginning of this section as follows:
1. To align all fields in each of the three columns (Name, Salary, and Comm) open the settings for each field in the column and set the same value for x, width, and justification.
For example, open the settings for the following fields:
a. The Salary field in the header section of the iterator
b. The [SALARY] field in the body section of the iterator
c. The Report Calculated Field connected to [SALARY] in the footer section of the field break
d. The Report Calculated Field connected to [SALARY] in the footer section of the iterator
Set x to 1.5, width to 1.25, and justification to Right.
2. To display the information in the numeric fields as monetary amounts, open the settings of each [SALARY], [COMM], and total] fields. For the converter field, select Monetary Amount.
3. To change the font for the Job, Subtotals, and Totals lines of the report, open the settings for each line and select the font you want to use for each.
This method ensures that all fields in the line use the same font. To set fonts for individual fields in a report line, open the settings for each field and change the font separately for each.
Your report should appear similar to the following in the Composition Editor.
When you test your report and select Preview from the Reports menu of the Report Tester, it should look very similar to the example at the beginning of this section.
Note:
Your report will have more rows than the sample shown here. For the sample, the number of rows returned was restricted with the WHERE clause WHERE STAFF.NAME <= 'J%'.
Last modified date: 05/18/2020