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 runtime 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.

To define a query field:

  1. Click the Fields and Parameters tab. By default, this tab is in the lower-right area of the Report Builder window.
  2. Click Query Fields.
  3. Click the plus sign (+) at the top of the tab.

    The Add Query Field dialog box appears.

  4. Type a value for the name field. This must match a column name in the corresponding cfquery statement and cannot contain a period.
  5. Type a default label.
  6. Specify the data type of the corresponding database column, as follows:

    Object

    Time

    Long

    Boolean

    Double

    Short

    Byte

    Float

    Big Decimal

    Date

    Integer

    Image

    Time Stamp

    BLOB

    String
  7. Click OK.

Note: The Query Builder defines query fields automatically for all database columns in the result set (does not apply to the Advanced Query Builder). Additionally, if you run the Query Builder as part of the Report Creation Wizard, the wizard places query fields on the report.

To define a calculated field:

  1. Click the Fields and Parameters tab. By default, this tab is in the lower-right area of the Report Builder window.
  2. Click Calculated Fields.
  3. Click the plus sign (+) at the upper edge of the tab.

    The Add Calculated Field dialog box appears.

  4. Specify a name, default label text, and data type. Data type options are the same as for query fields.
  5. Specify calculation options:

    Calculation Specifies the type of calculation that ColdFusion performs. Valid values are: Average, Count, 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.

  6. Specify reset options, as follows:

    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.

  7. Click OK.

For additional information on calculated fields, see "Add Calculated Field dialog box" in Report Builder online Help and Expressions and the Expression Builder.

To define an input parameter:

  1. Click the Fields and Parameters tab. By default, this tab is in the lower-right area of the Report Builder window.
  2. Click Input Parameters.
  3. Click the plus sign (+) at the top of the tab.
  4. The Add Input Parameter dialog box appears.

    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.

  5. Enter the default label text.
  6. Specify a data type and default value. Data type options are the same as for query fields.
  7. Click OK.

For more information on using input parameters, see Using input parameters to pass variables and other data at runtime and Using subreports.

To place a query field, calculated field, or input parameter on a report band:

  1. In the Fields and Parameters tab, use the radio buttons to specify whether to place the label, the field, or both.
  2. Drag the query field, calculated field, or input parameter from the Fields and Parameters tab to the appropriate report band.
  3. Drag the query field, calculated field, or input parameter to the desired band.
  4. (Optional) Use the Properties sheet 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.

View comments in LiveDocs