With Report Builder, you can include data in reports in a variety of formats, and perform calculations on the information. 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 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. The group break completes the old group by displaying the group footer 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 to 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 with 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.
Define a group
- Select Report > Group Management from the menu bar.
- Click Add.
- Specify a group name in the Name field.
- Specify the value that controls grouping (also called a group expression) in the Group on field. At run time, 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.
- 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.
- 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.
- Click OK.
The Report Builder adds the group to the report and creates header and footer bands for the group.
- Click OK again.
- Add headings, text, query fields, calculated fields, and other information to the group's header and footer.
Create group subtotals
- Create a calculated field to contain the group subtotal. Create the calculated field that uses 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.
- Place the calculated field on the report.
For more information on calculated fields, see the Report Builder online Help.
Defining, modifying, and using fields and input parameters
The Report Builder supports variable data through query fields, input parameters, and calculated fields, as follows:
Query field: Maps to columns in the database result set associated with the report. You define one query field for each column in the associated database query.
Calculated field: Analyzes or sums multiple detail rows in a report. ColdFusion dynamically generates calculated field values at report-generation time, optionally recalculating the value with each new report, page, column, or group.
Input parameter: Specifies data fields that you pass to the report at run time through the cfreportparam tag or from a main report to a subreport. You can place input parameters directly on a report band or you can use them as input to a calculated field.
Define a query field
- Choose Window > Fields and Parameters.
- Click Query Fields.
- Click the plus sign (+) at the upper edge of the tab.
- Type a value for the name field. This must match a column name in the corresponding cfquery statement and cannot contain a period.
- Type a default label.
- Specify the data type of the corresponding database column, as follows:
|
Object
|
Time
|
Long
|
|
Boolean
|
Double
|
Short
|
|
Byte
|
Float
|
Big Decimal
|
|
Date
|
Integer
|
String
|
|
Time Stamp
|
BLOB
|
CLOB
|
- Click OK.
Note: The Query Builder defines query fields automatically for all database columns in the result set (this does not apply to the Advanced Query Builder). Also, if you run the Query Builder as part of the Report Creation Wizard, the wizard places query fields on the report.
Define a calculated field
- Choose Window > Fields and Parameters.
- Click Calculated Fields.
- Click the plus sign (+) at the upper edge of the tab.
- Specify a name, default label text, and data type. Data type options are the same as for query fields.
- Specify calculation options:
Calculation: Specifies the type of calculation that ColdFusion performs. Valid values are: Average, Count, DistinctCount, First, Highest, Lowest, Nothing, Standard Deviation, Sum, System, and Variance. If you specify Nothing, you typically use the Perform Calculation On field to specify a dynamic expression. With the exception of Nothing (for which you use the Perform Calculation On field) and System (for which you write a customized scriptlet class), you use these calculations for group, page, and report totals.
Perform Calculation On: Specifies a field or expression. Click the ... button to display the Expression Builder.
Initial Value: Specifies an initial value for the calculated field.
- Specify the following reset options, and click OK:
Reset Field When: Specifies when to reset the calculated field value. Valid values are: None, Report, Page, Column Group.
Reset Group: If Reset Field When is set to Group, use this field to specify the group whose group break triggers the reset.
For additional information on calculated fields, see the Report Builder online Help.
Define an input parameter
- Choose Window > Fields and Parameters.
- In the Fields and Parameters panel, click Input Parameters.
- Click the plus sign (+) at the upper edge of the tab.
- In the Add Input Parameter dialog box, enter a value for the name field. This must match an input parameter, such as the name attribute of a cfreportparam tag included in the cfreport tag that invokes the report definition.
- Enter the default label text.
- Specify a data type and default value, and click OK. Data type options are the same as for query fields.
For more information on using input parameters, see Using input parameters to pass variables and other data at run time and Using subreports.
Place a query field, calculated field, or input parameter on a report band
- In the Fields and Parameters panel, use the radio buttons to specify whether to place the label, the field, or both.
- Drag the query field, calculated field, or input parameter from the Fields and Parameters tab to the appropriate report band.
- Drag the query field, calculated field, or input parameter to the desired band.
- (Optional) Use the Properties panel to customize the field display.
For example, you might have a query field named query.emp_salary and a calculated field that sums query.emp_salary, resetting it with each group. Place query.emp_salary in the detail band, and the associated calculated field in the group footer band.
