Adobe ColdFusion 8

Using charts

Charts can help clarify large or complex data sets. The Report Builder lets you place a chart in any report band and supports many types of charts.

To add a chart to a report, you use the Chart Wizard, which steps you through the chart building process. The Chart Wizard, which is fully integrated with the Query Wizard to facilitate database-driven charts, helps you define the chart type, the data used for the report and other formatting options.

As you use the Chart Wizard to choose and define the various aspects of a given chart, the Report Builder uses RDS to generate chart images in real time. However, the data in these chart images is not real.

The Chart Wizard includes the following panels:

  • Chart Types: Select the chart type (for example, bar) and subtype (for example, 3D-stacked).
  • Chart Series: Select the data for the series. When you add a series, the Report Builder lets you hard-code series data or open the Query Builder to populate the series using a database query.
  • Chart Formatting: Specifies title and series, general appearance, 3D appearance, lines and markers, and font.

The data you specify through the Chart Wizard corresponds to the attributes specified in the cfchart, cfchartseries, and cfchartdata tags. For more information on these tags, see the CFML Reference.

For complete information on ColdFusion charting capabilities, see Creating Charts and Graphs. For more information on charting using the Report Builder, see Report Builder online Help.

Using subreports

Subreports let you nest a report within your report. The data that you display in a subreport is typically related to the data in the main report, and you enable this by passing one or more subreport parameters to the subreport. However, the data displayed in a subreport can also be unrelated to the data in the main report.

Reasons to use subreports including the following:

  • You prefer to avoid complex SQL, such as a RIGHT OUTER JOIN.
  • Your report requires data from multiple databases.

The following example shows the use of subreport parameters and the relationship between a report and a subreport:

Note: Although the Report Builder supports multiple levels of nesting, it displays one level of nesting only.

For additional information on subreports, see the Report Builder online Help.

Defining a subreport

You can define a subreport and include it in a report, or you can define it as part of inserting the subreport in the main report.

A subreport has the following characteristics:

  • Data displayed in the detail band only. A subreport uses no header or footer bands.
  • If the subreport is related to the main report, it must include an internal query that uses a SELECT statement with a WHERE clause specifying the name of the input parameter used in the main report's Subreport Expression property.

If you have already defined a subreport, you add it to the main report and define subreport parameters, as necessary.

Add an existing subreport

  1. Define or open your main report.
  2. Click the Subreport icon in the toolbox.
  3. Drag an area for the subreport in the desired report band.
  4. Select From An Existing Report, specify the subreport, and click Next.
  5. Select the fields in the main report that correspond to fields in the subreport and click Next.
  6. Click Finish.

    The Report Builder adds the subreport to the main report, saving the report to subreport mappings as subreport parameters.

  7. To modify subreport parameter settings, select the subreport and click on Subreport Parameters in the Properties panel.

If you are certain about the data required for a subreport, you can define a new subreport while adding it to the main report.

Add a new subreport

  1. Define or open your main report.
  2. Click the Subreport icon in the toolbox.
  3. Drag an area for the subreport in the report band.
  4. Select As A New Report and click Next.
  5. Click Query Builder.
  6. Select the tables and columns for the subreport.
  7. Specify a WHERE clause for the report by using the Condition and Criteria columns for the key columns.

    Specify a WHERE for Condition and either ='#CFVariable#' (string column) or =#CFVariable# (numeric column) for Criteria, and then overtype CFVariable with the name of the input parameter for the subreport (you define the input parameter name later in the procedure.)

  8. Click Save, and then click Next.
  9. Specify grouping fields, if appropriate for your subreport, and click Next.
  10. Specify Free Form or Grid, and click Next.
  11. Specify Only Detail Band, and click Next.
  12. Specify a color scheme, and click Next.
  13. Specify headings, as appropriate, and click Next.
  14. For each parameter required by the subreport, specify the following:
    • Parameter name.
    • Associated value from the main report (select from the pop-up menu).
    • Data type.
  15. Click Next.
  16. Specify a fully qualified filename for the subreport, and then click Next.
  17. Click Finish.

    Report Builder adds the subreport to the main report. Report Builder lets you change subreport name and modify subreport parameters in a main report.

Modify subreport settings

  1. Click the subreport element in the main report.
  2. To change the subreport, modify Subreport Expression.
  3. To modify subreport parameters:
    1. Click the Subreport Parameters property.
    2. Click the ... button.
    3. Add, modify, or delete subreport parameters, and click OK.