Adobe ColdFusion 8

Creating a simple report

The following example shows how to create a simple report by using the Report Wizard and then modifying it. The example uses the cfartgallery database, which is installed with ColdFusion.

The example shows how to perform the following tasks:

  • Create a base report by using the Report Wizard and the Query Builder.
  • Use the Expression Builder to modify the data presentation in the report.
  • Modify the display text for column data.
  • Add a text field to the report and format text and data elements by using report styles.
  • Add an image file and images from a database.
  • Create and add a calculated field to display the total sales by artist.
  • Add group-level and report-level pie charts that show the ratio of sold and unsold art for each artist and for all the artists in the database.
  • Export report styles to a Cascading Style Sheet (CSS) file.

Create a report by using the Report Wizard

  1. Start Report Builder.
  2. Click the Query Builder button:
    1. From the list of data sources in the database pane, expand the cfartgallery database.
    2. Expand the Tables folder.
    3. Double-click the APP.ART table in the database pane. Report Builder adds the APP.ART table to the table pane.
    4. Double-click on the APP.ARTISTS table in the database pane. Report Builder adds the APP.ARTISTS table to the table pane. Notice that it automatically creates the join between the two tables based on the ARTISTID column.
    5. In the APP.ARTISTS table, double-click the FIRSTNAME and LASTNAME columns. The Query Builder adds the fields to the select statement in the SQL pane.
    6. In the ART table, double-click the ARTNAME, DESCRIPTION, PRICE, and ISSOLD columns. The following example shows the completed query in the Query Builder:
    1. Click the Test Query button to preview the results.
    2. Close the test query window and click the Save button in the Query Builder window.
  3. Double-click on the FIRSTNAME column to add it to the Non-printed Fields pop-up menu and click the Next button.
  4. In the Available Fields list, double-click LASTNAME to group the records by the artists' last names.
  5. Click the Next button three times to accept the default values.
  6. Choose Silver and click the Next button.
  7. Change the title of the report to Sales Report and click the Finish button. The Report Creation Wizard generates the report and displays it in the Report Builder work space.
  8. Choose File > Save As and save the report as ArtSalesReport1 in the default directory. Report Builder automatically adds the CFR extension.
  9. Press F12 to preview the report. Report Builder displays the records grouped by the artists' last names.
  10. Click the close box to close the Preview Report window and return to the Report Builder work space.

Changing the column heading labels

By default, the Report Wizard uses the column name for the column headers in the report, but you can change the label text for column headings.

Edit the heading label text

  1. Double-click the LASTNAME field in the Column Header band.
  2. Replace the column name with Artist Name, and click OK.
  3. Replace the remaining column labels as follows:
    • ARTNAME > Title
    • DESCRIPTION > Description
    • PRICE > Price
    • ISSOLD > Sold?

Using expressions to format data

Use the Expression Builder to perform the following tasks:

  • Change the display of the ISSOLD value to a yes/no expression. By default, Report Builder displays 0 (not sold) or 1 (sold) for the ISSOLD column based on how the data is stored in the database. You can use a function to change the display to yes or no.
  • Change the value of the PRICE column to a dollar format.
  • Concatenate the artists' first and last names. Even though the FirstName field is a nonprinted field in the report, you can add it to an expression because it is part of the SQL query that you created.

Change a Boolean value to yes/no

  1. Double-click the query.ISSOLD element in the detail band. Report Builder displays the Expression Builder for that element.
  2. In the Expression Builder, expand the Functions folder.
  3. Choose Display and Formatting from the Functions list. Report Builder displays the list of functions in the right pane of the Expression Builder.
  4. Double-click YesNoFormat from the list of functions. Report Builder automatically completes the following expression in the expression pane:
    YesNoFormat(query.ISSOLD)

  5. Click OK to close the Expression Builder and return to the report.
  6. Choose File > Save to save your changes to the report.
  7. Press F12 to preview the report. Yes or no appears in the Sold? column based on whether the artwork sold.

Display numbers in dollar format

  1. Double-click the field in the PRICE column of the detail band.
  2. In the expression pane, change the expression to the following text:
    DollarFormat(query.PRICE)

  3. Click OK to close the Expression Builder and return to the report.

Concatenate the FIRSTNAME and LASTNAME fields

  1. Double-click the query.LASTNAME field in the LASTNAME group header.
  2. In the Expression Builder, type the following expression:
    query.FIRSTNAME &" "& query.LASTNAME

    Notice that the Expression Builder prompts you with the available field names as you type.

  3. Click the OK button in the Expression Builder.
  4. Choose File > Save from the Report Builder menu bar to save your changes to the report.
  5. Press F12 to preview the report.

    Report Builder displays the first and last name for each of the artists. Notice that the report still is grouped alphabetically by last name.

  6. Close the preview window.

Adding page breaks before group changes

Create a page break so that each artist name starts on at the top of a page in the report output.

Add page breaks between artist names

  1. Choose Report > Group Management from the main menu bar. The Group Management dialog box appears with LASTNAME selected.
  2. Click the Edit button.
  3. Select the Start New Page option and click OK.

Adding a calculated field

Calculate the sum of the artwork sold by artist

  1. Choose Window > Fields and Parameters.
  2. Report Builder displays the Fields and Parameters panel.
  3. Expand the list of calculated fields.
  4. With Calculated Fields selected, click the (+) button at the upper edge of the Fields and Parameters panel.
  5. Make the following changes in the Add Calculated Field dialog box:
    1. Change the name of the calculated field to Sold.
    2. Change the label text to Sold.
    3. Change the Data Type to Float.
    4. Change the Calculation to Sum.
    5. In the Perform Calculation On field, enter the following expression: 
      Iif(IsBoolean(query.ISSOLD) and query.ISSOLD, query.Price,0)

      This expression multiplies the total price of the artwork per artist by the number of items sold to calculate the total sales per artist. If the ISSOLD value for a record is 1 (sold), the value is multiplied by 1 and added to the total; if the ISSOLD value for a record is 0 (unsold), the value is multiplied by 0.

    6. Change the Reset Field When value to Group.
    7. Change the Group Name value to LASTNAME, and click OK. Report Builder adds the calculated field definition in the Fields and Parameters panel.

Add the calculated field to your report

  1. Insert a field in the LASTNAME Footer band.
  2. In the Add Field dialog box, select calc.Sold from the pop-up menu.
  3. In the Expression Builder, type the following code:
    DollarFormat(calc.Sold)

  4. Press F12 to preview the report. Report Builder displays the sum of the artwork sold for each artist.

Adding and formatting fields

You can add a text field to your report and define a style for it. When you define a style, you can reuse it throughout your report or export the style so that you can use it in other reports. Also, you can override report styles at run time by using the cfreport and the cfreportparam tags. For more information, see Overriding report styles.

Add a text field

  1. In the Controls toolbox on the left side of the Report Builder window, click the text icon (the button with abc on it) and place the text field to the left of the calculated field in the LASTNAME footer.
  2. In the Edit Label dialog box, type Total Sales, and click OK.

Create a style

  1. Choose Window > Report Styles from the main menu.
  2. Click the (+) button.
  3. In the Name field, enter GroupFooter.
  4. Click the Color and Style tab and change the color to #9999CC.
  5. Click the Font tab and change the Font to Tahoma and click the bold option. Then click OK. Report Builder adds GroupFooter style to the pop-up menu of available styles in the report.
  6. Choose File > Save from the menu bar to save your changes to the report.

Apply the style to text and data elements in the report

  1. Select the Total Sales text box in the LASTNAME Footer band.
  2. Choose Window > Properties Inspector.
  3. Choose GroupFooter from the Style pop-up menu.
  4. Select the calculated field element and apply the GroupFooter Style to it.
  5. Press F12 to preview your report:

Adding images

When you add images with Report Builder, you can perform the following types of tasks:

  • Replace the company name text box with a company logo in the report header.
  • Use the Query Builder to add images from a database.
  • Display the report in RTF format for faster display.

Add a logo to the report header

  1. Select the Company Name text box located in the header band above Sales Report.
  2. Choose Edit > Cut to remove the text box from the report.
  3. Click the Add Image icon in the Controls toolbox. (The icon has a picture of a tree on it.)
  4. Click and drag the mouse in the header band above the Sales Report text box. When you release the mouse, the Image File Name dialog box appears.
  5. Navigate to the Art World logo file:

    C:\ColdFusion8\wwwroot\cfdocs\getting_started\photos\somewhere.jpg

  6. Click Open. Report Builder displays the Art World logo in the area that you selected.
  7. With the image selected in the work space, choose Windows > Properties Inspector. The Properties Inspector for the image appears:
    1. Under Colors and Style, change the Transparency to Transparent.
    2. Under Formatting, change Scale Image to Retain Shape.
  8. In the Header band, control-click the logo image and the Sales Report text box in the work space to select them.
  9. Click the Align Left Sides icon in the Controls toolbox.
  10. Choose File > Save to save your changes.
  11. Press F12 to preview the report.
  12. Close the preview window and readjust the image size and location as needed.

Add images from a database

  1. From the menu bar, choose Report > Report Query.
  2. In the Art table, double-click LARGEIMAGE. The Query Builder adds the LARGEIMAGE column to the select statement.
  3. Click the Test Query button. A list of image filenames appears to the right of the ISSOLD column.
  4. Close the Test Query window and click the Save button in the Query Builder.
  5. In the Report window, expand the Detail band by clicking on the lower splitter bar and dragging down.
  6. Click the Add Image icon in the Controls toolbox and drag the mouse in Detail band of the report to the left of the query.ARTNAME field. When you release the mouse, the Image File Name dialog box appears.
  7. Navigate to the cfartgallery images directory:

    C:\ColdFusion8\wwwroot\cfdocs\images\artgallery

  8. In the File Name field, type #query.largeimage#.
  9. Click the Open button. Report Builder adds the column to the Detail band of the report.
  10. Align the image column with the top of the Detail band.
  11. With the image element selected in the detail band, choose Window > Properties Inspector.
  12. Change the following properties:
    1. Transparency: Transparent.
    2. Scale Image: Retain Shape. This option scales the images proportionately within the bounding box.
    3. Error Control: No Image. This option ensures that Report Builder displays blank images rather than generates an error for images missing from the database.
    4. Using Cache: False. This option enforces a refresh each time you preview the report output in the browser.
  13. Choose File > Save to save your changes.

Change the report output format

  1. Choose Report > Report Properties from the menu bar.
  2. From the Default Output Format pop-up menu, choose RTF. Use this format for faster display in a web browser.
  3. Click OK to close the Report Properties dialog box and return to the report.
  4. Choose File > Save to save your changes.
  5. Press F12 to preview the report. The images are displayed beneath Artist name and to the left of the art title.
  6. Change the Default Output Format to HTML and preview the results.