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:

For data points from queries, ColdFusion takes the value of the cfgraph URL attribute, appends the value of the query column specified by the URLColumn attribute, and sends the resulting Web request.
For data points from cfgraphdata tags, ColdFusion uses the value of the tag's URL attribute as the page to link to.

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:

In the cfgraph tag, specify a single Web page in the URL attribute.
In the URL attribute, include the name of a parameter, but not its value, in the form ParameterName=
In the URLColumn attribute, specify a query column that contains the value of the parameter being passed.
In the target page, determine the data to be displayed based on the parameter that gets passed.

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

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.

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>

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.

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

Open graphdata.cfm in ColdFusion Studio.

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>

Save the page.
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

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

Developing ColdFusion Applications
Graphing Data