Programming ColdFusion MX, 2nd Edition

Drilling Down on Graph Data

Another useful feature of the cfchart tag is the ability to create graphs with clickable data points that can link to other templates. This allows you to implement drill-down functionality as we did in Chapter 11, using a graph instead of an HTML table. In order to create a clickable graph, you have to set format to Flash. Examples 17-6 and 17-7 demonstrate how to create a pie chart with drill-down capabilities. The pie chart displays the total amount of incentive award dollars given out per department, and it allows you to click on any pie slice and have it drill down to that specific department’s data.

Example 17-6. Pie chart displaying total incentive awards by department with drill-down capability

<cfquery name="GetAwards" datasource="ProgrammingCF">
  SELECT EmployeeDirectory.Department, Sum(IncentiveAwards.Amount) AS TheAmount 
  FROM EmployeeDirectory, IncentiveAwards
  WHERE EmployeeDirectory.ID = IncentiveAwards.ID  
  GROUP BY Department
</cfquery>         
   
<h2>Total Incentive Awards by Department</h2>
<cfchart format="Flash" chartheight="300" chartwidth="450" 
         show3d="Yes" pieslicestyle="Sliced"  
         URL="17-7.cfm?department=$itemlabel$">
  <cfchartseries type="Pie" query="GetAwards" itemcolumn="Department" 
                 valuecolumn="TheAmount" colorlist="Red, Yellow, Green, Purple, 
                 Aqua, Blue, Silver, Maroon, Navy, Olive" />
</cfchart>

In this example, a query is run that returns the sum of all the incentive awards for each department. The results are stored in two columns, Department ...