Programming ColdFusion

Graphing Query Results

In the previous section, we saw how simple it is to create a graph from static data. Let’s take things one step further now and talk about generating graphs from query results. After all, this is where the real power of the ColdFusion’s graphing capabilities lies. If you remember, I mentioned earlier that there are three additional CFGRAPH tag attributes common to all graph types that generate graphs from query results. These attributes are QUERY, VALUECOLUMN, and ITEMCOLUMN. Example 24-2 show how they are used to graph data from an aggregate query that retrieves the average salary for each department in the EmployeeDirectory of our sample database.

Example 24-2. Graphing Query Results

<CFQUERY NAME="GetSalary" DATASOURCE="ProgrammingCF">
  SELECT Department, AVG(Salary) AS AvgSalary FROM EmployeeDirectory
  GROUP BY Department
</CFQUERY>

<CFGRAPH TYPE="HorizontalBar" QUERY="GetSalary" VALUECOLUMN="AvgSalary"
         ITEMCOLUMN="Department" FILEFORMAT="Flash" GRAPHHEIGHT="360"
         GRAPHWIDTH="480" BACKGROUNDCOLOR="White" BORDERWIDTH="1"
         BORDERCOLOR="Black" DEPTH="10" TITLE="Average Salary by Department"
         TITLEFONT="Arial">
</CFGRAPH>

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, and the average salary for that department. This time, the graph is a HorizontalBar chart. The QUERY attribute is required for dynamic graphs and ...