Grouping and group breaks

You can add clarity to a report's organization by grouping the information. You can define separate headings for each new group and can also display group-specific summary information, such as subtotals at the end of each group's area of the report. For example, you might create a report that displays departments, employees, and their salaries. Grouping the data by department lets users quickly understand department salary characteristics. When the department ID changes, the ColdFusion Report Builder triggers a group break, which completes the old group by displaying the group footer (which can contain calculated fields, such as total and average department salary) and starts the new group by displaying the group header.

The ColdFusion Report Builder does not group data itself. You must ensure that the SQL used for retrieve the result set is already grouped in the appropriate order; typically you implement grouping by specifying an ORDER BY clause in the SQL SELECT statement used for the report. For example, you might use the following SQL SELECT statement:

SELECT EmployeeID, LastName, FirstName, Title, City, Region, Country
FROM Employees
ORDER BY Country, City

For this example, you can define two groups: one that corresponds to Country, and a second group that corresponds to City. When you define more than one group, the Group Management dialog box appears Up Arrow and Down Arrow keys, which you can use to control group hierarchy. For example, country should be above city, because countries contain cities.

To define a group:

1. Select Report > Group Management from the menu bar.
2. Click Add.
3. Specify a group name.
4. Specify the value that controls grouping (also called a group expression). At runtime, ColdFusion triggers a group break when the result of this value changes. These values are often query field names. However, this value can also be a calculated field or other type of expression. Sample group expressions include the following:

   Query field Creates a group break when the associated column in the result set contains a different value. The field that you specify must be one of the sort criteria for the result set; for example, query.country.

   Calculated field Creates a group break when a calculated field returns a different value. For example, if the expression calc.FirstLetter returns the first letter of a query column, you can group a report in alphabetical order.

   Boolean expression Creates a group break when a Boolean expression returns a different value. For example, if your result set is sorted by the passpercentage column, you might use the Boolean expression query.passpercentage LT 50.

5. Specify group break options:

   Start New Column Forces a new column on a group break.

   Start New Page Forces a new page on a group break.

   Reset Page Number Resets the page number to 1 on a group break.

6. Specify band size and printing information:

   Min. height for group The minimum height that must remain on a page for ColdFusion to print the group band on that page.

   Reprint Header on Each Page Displays the group header on each page.

7. Click OK.

   The Report Builder adds the group to the report and creates header and footer bands for the group.

8. Click OK.

   The Group Management dialog box closes.

9. Add headings, text, query fields, calculated fields, and other information to the group's header and footer.

To create group subtotals:

1. Create a calculated field to contain the group subtotal. Create the calculated field using the following criteria:
   • Specify a numeric data type.
   • Select Sum in the Calculation field.
   • Specify the field to sum on in the Perform Calculation On field. For example, on an employees by department report, you might sum on query.emp_salary.
   • Specify that the field should be reset when the group changes.
2. Place the calculated field on the report.

For more information on calculated fields, see Defining, modifying, and using fields and input parameters or the Report Builder online Help.

View comments in LiveDocs