Programming ColdFusion MX, 2nd Edition

Charting Dynamic Data

In the previous examples, you saw how simple it is to chart static values supplied by the cfchartdata tag. We’ll take things one step further now and talk about charting dynamic values obtained from a query. The process is similar to what we did in the previous section. This time, however, instead of hardcoding each data point, we’ll specify a query to use to populate the chart. Example 17-3 shows how to use query data to graph the average salary for each department in the EmployeeDirectory table.

Example 17-3. Charting dynamic data

<cfquery name="GetSalary" datasource="ProgrammingCF">
  SELECT Department, AVG(Salary) AS AvgSalary 
  FROM EmployeeDirectory
  GROUP BY Department
</cfquery>
   
<h3>Average Salary by Department</h3>
<cfchart format="Flash" chartheight="360" chartwidth="480" scalefrom="0" 
         scaleto="500000" gridlines="11" labelformat="Number" 
         xaxistitle="Department" yaxistitle="Salary" rotated="Yes">
  <cfchartseries type="Bar" query="GetSalary" itemcolumn="Department" 
                 valuecolumn="AvgSalary" serieslabel="Average Salary by 
                              Department" 
                 seriescolor="##0000FF" paintstyle="Plain" />
</cfchart>

The query in this example retrieves the average salary for each department in the EmployeeDirectory table using the AVG aggregate function. Two columns of data are returned, the department name (Department) and the average salary for that department (AvgSalary). This time, we’re going to create a bar graph and rotate it 90 degrees clockwise, making it a horizontal bar graph. ...