Programming ColdFusion

Drilling Down on Graph Data

Another useful feature of the CFGRAPH 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 the FILEFORMAT to Flash. Examples 24-3 and 24-4 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.

Example 24-3. Pie Chart Displaying Total Incentive Awards by Department with Drill-Down Capability

<CFQUERY NAME="GetEmployeeInfo" DATASOURCE="ProgrammingCF">
         SELECT EmployeeDirectory.Department, IncentiveAwards.Amount
         FROM EmployeeDirectory, IncentiveAwards
         WHERE EmployeeDirectory.ID = IncentiveAwards.ID
</CFQUERY>

<CFQUERY NAME="GroupIt" DBTYPE="Query">
         SELECT Department, Sum(Amount) AS TheAmount FROM GetEmployeeInfo
         GROUP BY Department
</CFQUERY>         

<CFGRAPH TYPE="Pie" QUERY="GroupIt" VALUECOLUMN="TheAmount"
         ITEMCOLUMN="Department" FILEFORMAT="Flash" GRAPHHEIGHT="480"
         GRAPHWIDTH="640" DEPTH="10"
         TITLE="Total Incentive Awards By Department (In Dollars)"
         TITLEFONT="Arial" URL="24-4.cfm"
         URLCOLUMN="Department" SHOWLEGEND="Left" LEGENDFONT="Arial"
         SHOWVALUELABEL="Yes" VALUELABELFONT="Arial" VALUELABELSIZE="8"
         VALUELOCATION="outside"
         COLORLIST="Red,Yellow,Pink,Green,Purple,Orange,Blue,Gray,Goldenrod">
</CFGRAPH>

In this example, the first query is ...