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?

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.

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.

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.

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.

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.

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.

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.

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.

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:

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.

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.

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.

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.

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.

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

   Name	TotalSold	TotalUnsold
   Default Label Text:	Total Sold	Total Unsold
   Data Type:	Big Decimal	Big Decimal
   Calculation:	Sum	Sum
   Perform Calculation On:	Iif(IsBoolean(query.ISSOLD) and query.ISSOLD, query.Price,0)	Iif(IsBoolean(query.ISSOLD) and not(query.ISSOLD), query.Price,0)
   Initial Value:	0	0
   Reset Field When:	Report (Changes)	Report (Changes)
   Reset Group:	LASTNAME	LASTNAME

2. Expand the Report Footer band, which is located directly below the Page Footer band.

3. Copy the pie chart from the Group Footer and paste it in the Report Footer.

4. Double-click the pie chart and click the Next button.

5. Double-click Total Sales to display the Edit Chart Series dialog box.

6. Change the Series Label to Total Sales for Artists.

7. Change the chart series values:

   Label	Value
   Sold	#calc.TotalSold#
   Unsold	#calc.TotalUnsold#

8. Click the Next button, and then Click the Title & Series tab.

9. Change the Chart Title to Total Sales for Artists, and click Finish.

10. Choose File > Save from the menu bar to save your changes to the report.

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

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:

   ReportTitle 
   { 
           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-weight attribute, as the following code shows:

   ReportTitle 
   { 
           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.

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.

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):

<h3>Artist Login Form</h3> 
<p>Please enter your last name and password.</p> 
<cfform name="loginform" action="artSalesReport.cfm" method="post"> 
<table> 
    <tr> 
        <td>Last Name:</td> 
        <td><cfinput type="text" name="username" required="yes" message="A username is 
            required."></td> 
    </tr> 
    <tr> 
        <td>Password:</td> 
        <td><cfinput type="password" name="password" required="yes" message="A password is 
            required."></td> 
    </tr> 
</table> 
    <br /> 
    <cfinput type="submit" name="submit" value="Submit"> 
</cfform>

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.

<cfquery name="artsales" datasource="cfartgallery"> 
SELECT * 
FROMAPP.ART, APP.ARTISTS  
WHERE APP.ART.ARTISTID = APP.ARTISTS.ARTISTID 
         AND APP.ARTISTS.LASTNAME= <cfqueryparam value="#FORM.username#"> 
ORDER BY ARTISTS.LASTNAME 
</cfquery> 
 
<cfreport query="#artsales#" template="ArtSalesReport1.cfr" format="RTF"/>

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.

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

<cfreport template="ArtSalesReport1.cfr" format="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:

<cfreport query="#artsales#" template="ArtSalesReport1.cfr" format="HTML" resourceTimespan="#CreateTimeSpan(0,1,0,0)#"/>

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:

<cfreport template="ArtSalesReport1.cfr" format="HTML" filename="artSales.html" overwrite="yes"/>

ColdFusion creates an image directory relative to the HTML output file in the format filename_files. 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.

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:

<cfreport template="ArtSalesReport1.cfr" style="artStyles.css" format="PDF"/>

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

<cfreport template="ArtSalesReport1.cfr" style='ReportTitle {defaultStyle: false;  
    font-family:"Tahoma"; color: "lime";}' format="FlashPaper"> 
</cfreport>

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

<cfset mystyle='DetailData { defaultStyle: true; font-family: "Tahoma"; color: ##00FFF0;}'> 
<cfreport template="ArtSalesReport1.cfr" style="#mystyle#" format="HTML"> 
</cfreport>

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