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. Select Report Creation Wizard and click OK.

3. Click the Query Builder button:

   Note: If you have RDS configured, provide the RDS password.

    

1. 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 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:
      
   7. Click the Test Query button to preview the results.
   8. Close the test query window and click the Save button in the Query Builder window.
2. Double-click the FIRSTNAME column to add it to the Non-printed Fields pop-up menu and click the Next button.
3. In the Available Fields list, double-click LASTNAME to group the records by the artists' last names.
4. Click the Next button three times to accept the default values.
5. Choose Silver and click the Next button.
6. 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 workspace.
7. Choose File > Save As and save the report as ArtSalesReport1 in the default directory. Report Builder automatically adds the CFR extension.
8. Press F12 to preview the report. Report Builder displays the records grouped by the artists' last names.
9. Click the close box to close the Preview Report window and return to the Report Builder workspace.

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 Reset Group 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 preceding 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. Drag the mouse in the header band preceding 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:\ColdFusion9\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 workspace, 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 workspace 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 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:\ColdFusion9\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 refreshes the display 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.

Adding charts

You can use the Chart Builder to add two pie charts to your report: the first pie chart shows the total dollar amount of the art sold versus the total dollar amount unsold art for each artist; the second pie chart shows the sum of artwork sold versus unsold for all of the artists.
The two pie charts are the same except for the scope. To apply a pie chart to a group (the ratio of sold to unsold art for each artist), add the pie chart to the group footer band. To apply the pie chart to the report (the ratio of sold to unsold art for all artists), add the pie chart to the report footer band.
In Adding a calculated field, you added a calculated field for the total dollar amount of artwork sold. Before you can create the pie chart for this example, create a second calculated field for the total dollar amount of unsold art.

Add a calculated field for the sum of unsold art

1. Choose Window > Fields and Parameters.
2. Select the Calculated Fields heading in the Fields and Parameters panel.
3. Click the icon at the upper edge of the panel:
   1. In the Name field, type Unsold.
   2. In the Default Label Text field, type Unsold.
   3. In the Data Type field, choose Big Decimal from the pop-up menu.
   4. In the Calculation field, choose Sum from the pop-up menu.

   5. In the Perform Calculation On field, enter the following expression to calculate the dollar amount of unsold art:

      Iif(IsBoolean(query.ISSOLD) and not(query.ISSOLD), query.Price,0)

       

   6. In the Reset Field When field, choose Group from the pop-up menu.
   7. In the Reset Group field, choose LASTNAME.
   8. Click OK to close the Add Calculated Field dialog box and return to the report.
4. Choose File > Save from the menu bar to save your changes to the report.

Add a pie chart to the group footer

1. Expand the LASTNAME Footer band.
2. Choose Insert > Chart from the Report Builder menu bar:
   1. Choose Pie from the Base Chart Type list. The Chart Sub-Type appears to the right of the Base Chart Type.
   2. Choose the 3D chart.
3. Click the Next button. Then click the Add button:
   1. In the Series Label field, type Total Sales.
   2. In the Paint Style field, choose Light.
   3. In the Data Label field, choose Value.
   4. In the Color List, type Teal,Gray.
   5. In the Chart Data Source area, ensure that the Data From A Fixed List of Values option is selected.
4. Click the Add button:
   1. In the Label field, type Sold.
   2. In the Value field, choose #calc.Sold# from pop-up menu.
   3. Click OK.
5. Click the Add button again:
   1. In the Label field, type Unsold.
   2. In the Value field, choose #calc.Unsold# from the pop-up menu.
   3. Click OK twice to return to the Chart Series dialog box.
6. Click the Next button. In the Chart Formatting dialog box, click the Titles & Series tab and make the following changes:
   1. In the Chart Title field, type Total Sales for #query.LASTNAME#.
   2. In the X Axis Title field, type Sold.
   3. In the Y Axis Title field, type Unsold.
   4. In the Label Format field, choose Currency from the pop-up menu.
   5. Click the 3-D Appearance tab and ensure that Show 3-D is selected.
7. Click the Font tab and make the following changes:
   1. Change the Font Name to Arial.
   2. Change the Font Size to 9.
8. Click the Finish button. Report Builder adds a place holder for the pie chart in the report.
9. Resize and move the chart to the desired location within the LASTNAME Footer band.
10. Choose File > Save to save your changes to the report.
11. Press F12 to preview the report.

Add a pie chart to the report footer

1. Create two calculated fields to use in the report footer pie chart with the following parameters:

1. Expand the Report Footer band, which is located directly below the Page Footer band.
2. Copy the pie chart from the Group Footer and paste it in the Report Footer.
3. Double-click the pie chart and click the Next button.
4. Double-click Total Sales to display the Edit Chart Series dialog box.
5. Change the Series Label to Total Sales for Artists.
6. Change the chart series values:

1. Click the Next button, and then Click the Title & Series tab.
2. Change the Chart Title to Total Sales for Artists, and click Finish.
3. Choose File > Save from the menu bar to save your changes to the report.
4. Press F12 to preview the report.
   The Total Sales for Artists pie chart appears only on the last page of the report. Verify that the calculations are correct.

Using Cascading Style Sheets

The Report Creation Wizard automatically creates and applies the following styles to your report:

• ReportTitle
• CompanyName
• PageTitle
• ReportDate
• SubTitle
• DetailData (default style)
• DetailLabel
• PageFooter
• RectangleStyle
• LineStyle
  The instructions on Adding and formatting fields show how to add a field called GroupFooter and apply it to a text field and a data field in the GroupFooter band. You can export the styles in a report to a CSS file. Report Builder automatically generates the CSS code for the styles. This technique is an efficient way to maintain a single set of styles to use with multiple reports. You can modify the styles in the CSS file by using any text editor and either import the CSS file in Report Builder or override the styles in the report at run time.

Export report styles to a CSS file

1. Choose Window > Report Styles.
2. Click the export icon (the icon with the orange arrow).
3. In the File Name field, type artstyles. Report Builder automatically adds the CSS extension.

4. Navigate the artStyles.css file and double-click it to open it. The following example shows the generated CSS code:

   { color:Black; font-size:24pt; } CompanyName { color:#6188A5; font-weight:bold; } PageTitle { color:#333333; font-size:14pt; font-weight:bold; } ReportDate { color:#333333 } SubTitle { color:#6089A5; font-size:12pt; font-weight:bold; } DetailLabel { color:Black; background-color:#E3EDEF; font-weight:bold; } DetailData { default-style:true; color:Black; line-size:thin; } PageFooter { color:#2F2F2F; font-size:8pt; } RectangleStyle { color:#E3EDEF; background-color:#E3EDEF; } LineStyle { color:#CCCCCC; background-color:#CCCCCC; } GroupFooter { color:Blue; font-weight:bold; font-family:Tahoma;

    

5. Change the ReportTitle style color attribute to Red and add the font-weightattribute, as the following code shows:

   { color:Red; font-size:24pt; font-weight: bold; }

    

6. Save the CSS file.
   Also, you can override report styles from ColdFusion. Form more information, see Overriding report styles.

Import the CSS file

1. Choose Window > Report Styles.
2. Click the import styles icon (the one with the blue arrow).
3. Navigate to the location of the artStyles.css file, and click OK. Report Builder automatically updates the report style definition and applies the updated style to report title.
4. Press F12 to preview the report.

Overriding report settings at run time

You can use the cfreport tag in ColdFusion to override report settings in a Report Builder report at run time. The examples use the CFR file that you created in Creating a simple report.

Overriding the report query

This example filters the data in the report based on the login ID of the artist. When the artist logs on, the report displays the data and pie chart for that artist. The report also includes the pie chart with data from all the artists.
The following code creates a simple login page in ColdFusion. The form uses artist's last name as the user ID. (The code does not include password verification):

On the processing page, add a query like the one you created in the Report Builder report. The ColdFusion query must contain at least all of the columns included in the Report Builder query; however, the ColdFusion query can contain additional data.
The query in the following example selects all of the data from the ART and ARTISTS tables based on the artist's last name. The cfreport tag uses the pathname of the CFR file as the report template.

ColdFusion displays the report for the artist in RTF format. Notice that the value of the format attribute overrides the Default Output format defined in the CFR file.

Exporting the report in HTML

To generate a report in HTML and display it directly in the browser, change the format attribute to HTML:

ColdFusion automatically generates a temporary directory where it stores all of the image files in the report (charts are saved as PNG files). The location of the temporary directory is:
C:\ColdFusion9\tmpCache\CFFileServlet_cfreport_report_unique_identifier_
You can specify when the temporary directory is removed from the server by using the CreateTimeSpan function as a value for the resourceTimespan attribute:

You can specify the time span in days, hours, minutes, and seconds. In this example, the temporary directory is deleted after one hour. For more information, see the CFML Reference.
To export the report output to an HTML file, specify the filename attribute. The following code writes the report output to an HTML file called artSales.html:

ColdFusion creates an image directory relative to the HTML output file in the format filenamefiles. In this example, ColdFusion automatically generates PNG files for the charts in the report and saves them to a directory called artSales_files. Also, it generates copies of all of the JPG images extracted from the cfartgallery database and stores them in the artSales_files directory. For more information, see the _CFML Reference.

Overriding report styles

To override the report styles in a report, specify the style attribute of the cfreport tag. The value must contain valid CSS syntax, the pathname to a CSS file, or a variable that points to valid CSS code. The CSS style names must match the report style names defined in Report Builder.
The following code shows how to override the styles in the ArtSalesReport1.cfr report with the styles defined in the artStyles.css file:

The following code shows how to apply a CSS style as a value of the style attribute:

The following code shows how to create a variable called myStyle and use it as a value of the style attribute:

For more information, see the cfreport tag in the CFML Reference.