One of the most important considerations when you chart data is the way that you supply the data to the cfchart tag. You can supply data in the following ways:

• Specify individual data points by using cfchartdata tags.
• Provide all the data in a single query by using cfchartseries tags.
• Combine data from a query with additional data points from cfchartdata tags.
• Provide all the data in a report created with Report Builder. For more information, see Creating Reports and Documents for Printing.

Charting individual data points

When you chart individual data points, you specify each data point by inserting a cfchartdata tag in the cfchartseries tag body. For example, the following code creates a simple pie chart:

<cfchart>
    <cfchartseries type="pie">
        <cfchartdata item="New Vehicle Sales" value=500000>
        <cfchartdata item="Used Vehicle Sales" value=250000>
        <cfchartdata item="Leasing" value=300000>
        <cfchartdata item="Service" value=400000>
    </cfchartseries>
</cfchart>

This pie chart displays four types of revenue for a car dealership. Each cfchartdata tag specifies a department's income and a description for the legend.

The cfchartdata tag lets you specify the following information about a data point:

Charting a query

Each bar, dot, line, or slice of a chart represents data from one row/column coordinate in your result set. A related group of data is called a chart series.

Because each bar, dot, line, or slice represents the intersection of two axes, you must craft the query result set such that the row and column values have meaning when displayed in a chart. This often requires that you aggregate data in the query. You typically aggregate data in a query using one of the following:

• Specify a SQL aggregate function (SUM, AVG, MAX, and so on) using a GROUP BY clause in the SELECT statement.
• Use a Query of Queries.
• Retrieve data from a view, instead of a table.

When you chart a query, you specify the query name using the query attribute of the cfchartseries tag. For example, the code for a simple bar chart might be as follows:

<cfchart
        xAxisTitle="Department"
        yAxisTitle="Salary Average"
    >
    <cfchartseries 
        type="bar" 
        query="DataTable" 
        valueColumn="AvgByDept" 
        itemColumn="Dept_Name"
        />
</cfchart>

This example displays the values in the AvgByDept column of the DataTable query. It displays the Dept_Name column value as the item label by each bar.

The following table lists the attributes of the cfchartseries tag that you use when working with queries:

Charting a query of queries

In addition to charting the results of a query, you can also chart the results of a queries of queries. For more information about using query of queries, see Using Query of Queries. Query of queries provides significant power in generating the data for the chart. For example, you can use aggregating functions such as SUM, AVG, and GROUP BY to create a query of queries with statistical data based on a raw database query. For more information, see Using Query of Queries.

You can also take advantage of the ability to dynamically reference and modify query data. For example, you can loop through the entries in a query column and reformat the data to show whole dollar values.

The example in the following procedure analyzes the salary data in the cfdocexamples database using a query of queries, and displays the data as a bar chart.

1. Create a new ColdFusion page with the following content:

   <!--- Get the raw data from the database. --->
   <cfquery name="GetSalaries" datasource="cfdocexamples">
       SELECT Departmt.Dept_Name, 
           Employee.Salary
       FROM Departmt, Employee
       WHERE Departmt.Dept_ID = Employee.Dept_ID
   </cfquery>
   
   <!--- Generate a query with statistical data for each department. --->
   <cfquery dbtype = "query" name = "DeptSalaries">
       SELECT 
           Dept_Name,
           AVG(Salary) AS AvgByDept
       FROM GetSalaries
       GROUP BY Dept_Name
   </cfquery>
   
   <!--- Reformat the generated numbers to show only thousands. --->
   <cfloop index="i" from="1" to="#DeptSalaries.RecordCount#">
       <cfset DeptSalaries.AvgByDept[i]=Round(DeptSalaries.AvgByDept[i]/1000)*1000>
   </cfloop>
   
   <html>
   <head>
       <title>Employee Salary Analysis</title>
   </head>
   
   <body>
   <h1>Employee Salary Analysis</h1> 
   
   <!--- Bar chart, from DeptSalaries Query of Queries. --->
   <cfchart 
           xAxisTitle="Department"
           yAxisTitle="Salary Average"
           font="Arial"
           gridlines=6
           showXGridlines="yes"
           showYGridlines="yes"
           showborder="yes"
           show3d="yes" 
       > 
   
       <cfchartseries 
           type="bar" 
           query="DeptSalaries" 
           valueColumn="AvgByDept" 
           itemColumn="Dept_Name"
           seriesColor="olive" 
           paintStyle="plain"
       />
   </cfchart>
   
   <br>
   </body>
   </html>
   

   
   
2. Save the page as chartdata.cfm in the myapps directory under the web root directory. For example, the directory path in Windows might be C:\Inetpub\wwwroot\myapps.
3. Return to your browser and enter the following URL to view the chartdata.cfm page:

   http://localhost/myapps/chartdata.cfm

   The following figure appears:

   
   Note: If a query contains two rows with the same value for the itemColumn attribute, ColdFusion graphs the last row in the query for that value. For the preceding example, if the query contains two rows for the Sales department, ColdFusion graphs the value for the last row in the query for Sales.

Reviewing the code

The following table describes the code and its function:

You can also rewrite this example to use the cfoutput and cfchartdata tags within the cfchartseries tag, instead of using the loop, to round the salary data, as the following code shows:

<cfchartseries 
    type="bar" 
    seriesColor="olive" 
    paintStyle="plain">

    <cfoutput query="deptSalaries">
        <cfchartdata item="#dept_name#" value=#Round(AvgByDept/1000)*1000#>
    </cfoutput>

</cfchartseries>

Combining a query and data points

To chart data from both query and individual data values, you specify the query name and related attributes in the cfchartseries tag, and provide additional data points by using the cfchartdata tag.

ColdFusion displays the chart data specified by a cfchartdata tag before the data from a query, for example, to the left on a bar chart. You can use the sortXAxis attribute of the cfchart tag to sort data alphabetically along the x axis.

One use of combining queries and data points is to provide data that is missing from the database; for example, to provide the data for one department if the data for that department is missing. The example in the following procedure adds data for the Facilities and Documentation departments to the salary data obtained from the query shown in the previous section:

1. Open the chartdata.cfm file in your editor.
2. Edit the cfchart tag so that it appears as follows:

   <cfchart chartwidth="600">
       <cfchartseries 
           type="bar"
           query="DeptSalaries"
           itemColumn ="Dept_Name" 
           valueColumn="AvgByDept"
           >
           <cfchartdata item="Facilities" value="35000">
           <cfchartdata item="Documentation" value="45000">
       </cfchartseries>
   </cfchart>
   

   
   
3. Save the page as chartqueryanddata.cfm in the myapps directory under the web root directory. For example, the directory path in Windows might be C:\Inetpub\wwwroot\myapps.
4. Return to your browser and enter the following URL to view the chartqueryanddata.cfm page:

   http://localhost/myapps/chartqueryanddata.cfm