Linking Dynamically from Graphs

You can make Flash-format bar and pie charts interactive so that ColdFusion displays a new data point-specific Web page when the user clicks a bar or pie wedge. ColdFusion provides two methods for specifying the destination page:

Using ColdFusion you can combine a static URL component with a query column component. This lets you link dynamically based on query column data without having to format the column contents as a URL. For example, you can use the values of the Dept_Name field in the CompanyInfo database to determine the data to display. To do this, follow these guidelines:

The example code in the following procedure illustrates this technique.

Example: dynamically linking from a pie chart

In the following example, when you click a pie wedge, ColdFusion displays a table containing the detailed salary information for the departments represented by the wedge. The example is divided into two parts: creating the detail page and making the graph dynamic.

Part 1: Creating the detail page

  1. Create a new application page in ColdFusion Studio.

    This page displays the drill-down information on the selected department based on the department name passed as the URL parameter.

  2. Edit the page so that it appears as follows:
    <cfquery name="GetSalaryDetails" datasource="CompanyInfo">
      SELECT Departmt.Dept_Name, 
        Employee.FirstName, 
        Employee.LastName, 
        Employee.StartDate,
        Employee.Salary,
        Employee.Contract
      FROM Departmt, Employee
      WHERE Departmt.Dept_Name = '#URL.Dept_Name#'
      AND Departmt.Dept_ID = Employee.Dept_ID
      ORDER BY Employee.LastName, Employee.Firstname
    </cfquery>
    
    <html>
    <head>
      <title>Employee Salary Details</title>
    </head>
    
    <body>
    
    <h1><cfoutput>#GetSalaryDetails.Dept_Name[1]# Department
    Salary Details</cfoutput></h1>
    <table border cellspacing=0 cellpadding=5>
    <tr>
      <th>Employee Name</td>
      <th>StartDate</td>
      <th>Salary</td>
      <th>Contract?</td>
    </tr>
    <cfoutput query="GetSalaryDetails" >
    <tr>
      <td>#FirstName# #LastName#</td>
      <td>#dateFormat(StartDate, "mm/dd/yyyy")#</td>
      <td>#numberFormat(Salary, "$999,999")#</td>
      <td>#Contract#</td>
    </tr>
    </cfoutput>
    </table>
    </body>
    </html>
    
  3. Save the page as Salary_details.cfm in myapps under the Web root directory.

Reviewing the code

The following table describes the code and its function:
Code
Description
<cfquery name="GetSalaryDetails"
    datasource="CompanyInfo"> 
  SELECT
    Departmt.Dept_Name,  
    Employee.FirstName,  
    Employee.LastName,  
    Employee.StartDate, 
    Employee.Salary, 
    Employee.Contract 
  FROM Departmt, Employee 
  WHERE
    Departmt.Dept_Name =
      '#URL.Dept_Name#' 
  AND Departmt.Dept_ID =
    Employee.Dept_ID 
  ORDER BY Employee.LastName,
    Employee.Firstname 
</cfquery> 
Get the salary data for the department whose name was passed in the URL parameter string. Sort the data by the employee's last and first names.
<table border cellspacing=0 cellpadding=5> 
<tr> 
  <th>Employee Name</td> 
  <th>StartDate</td> 
  <th>Salary</td> 
  <th>Contract?</td> 
</tr> 
<cfoutput query="GetSalaryDetails" > 
<tr> 
  <td>#FirstName# #LastName#</td> 
  <td>#dateFormat(StartDate, 
    "mm/dd/yyyy")#</td> 
  <td>#numberFormat(Salary, "$999,999")#</td>
  <td>#Contract#</td> 
</tr> 
</cfoutput> 
</table> 
Display the data retrieved by the query as a table. Format the start date into standard month/date/year format, and format the salary with a leading dollar sign comma separator, and no decimal places.

Part 2: Making the graph dynamic

  1. Open graphdata.cfm in ColdFusion Studio.
  2. Edit the cfgraph tag for the pie chart so it appears as follows:
    <cfgraph type="pie" 
      query="DeptSalaries" 
      valueColumn="SumByDept" 
      itemColumn="Dept_Name" 
      URL="Salary_Details.cfm?Dept_Name="
      URLColumn="Dept_Name"
      title="Total Salaries by Department" 
      titleFont="Times"
      showValueLabel="rollover" 
      valueLabelFont="Times"
      backgroundColor = "##CCFFFF"
      borderWidth = 0
      colorlist="##6666FF,##66FF66,##FF6666,##66CCCC"
      LegendFont="Times">
    </cfgraph>
    
  3. Save the page.
  4. Return to your browser and enter the following URL to view graphdata.cfm:

    http://127.0.0.1/myapps/graphdata.cfm. Click the slices of the pie chart.

Reviewing the code

The following table describes the highlighted code and its function:
Code
Description
URL="Salary_Details.cfm?
  Dept_Name=" 
When the user clicks a data point, call the Salary_Details.cfm page in the current directory, and pass it the parameter named Dept_Name. The parameter value must come from the URLColumn attribute.
URLColumn="Dept_Name" 
Complete the URL string with the value from the query Dept_Name field. So, if the Dept_Name is HR, ColdFusion calls the following URL: Salary_Details.cfm?Dept_Name=HR