User Guide

780 Chapter 32: Creating Reports for Printing
• Displaying page numbers
• Using layered controls
• Using links
• Using the Properties sheet
• Displaying reports
• Using input parameters to pass variables and other data at runtime
• Configuring RDS
For more information, including troubleshooting tips, see Report Builder online Help.
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.