Graphing Data

One of the most important considerations when you graph data is the way you supply the data to the cfgraph tag. You can supply data in several ways:

Provide all the data in a single query.
Specify individual data points using cfgraphdata tags.
Combine data from a query with additional data points from cfgraphdata tags.

Note

The cfgraph tag graphs numeric data only. As a result, you must convert any dates, times, or preformatted currency values, such as $3,000.53, to integers or real numbers.

Graphing a query

When you graph a query, you specify the query name in the cfgraph tag query attribute. In this format the cfgraph tag body is empty. However, you must still provide the <\cfgraph> end tag. For example, a simple cfgraph tag for a bar chart might look like this:

<cfgraph type="bar" title="Salaries by Department" 

query="DataTable" 

itemColumn="Dept_Name"

valueColumn="AvgByDept">

</cfgraph>

This tag displays the values in the AvgByDept column of the DataTable query. It displays the Dept_Name column value as the item label by each bar. The title "Salaries by Department" appears above the chart.

The cfgraph tag can take the following information from a query:

Attribute

Description

query
The query containing the data.

valueColumn
The query column containing the values to be graphed.

itemColumn
(Optional) The query column containing the description for this data point. The item normally appears on the horizontal axis of bar and line graphs, on the vertical axis of horizontal bar graphs, and in the legend in pie charts.

URL
(Optional) Works only with bar, horizontal bar, and pie charts in Flash file format.
A static prefix for all data point URLs. When the user clicks a bar or pie wedge, the page links to a URL created by appending the data from the data point's URLColumn value.
Use this attribute to specify a string that is part of all links on a chart, such as
http://www.mycompany.com/myapp/salary_info/chart_details/.

URLColumn
(Optional) Works only with bar, horizontal bar, and pie charts in Flash file format.
The query column containing the data point-specific part of a URL to load when the user clicks the corresponding data point the graph. ColdFusion encodes the contents of the query field, in URL format (for example, replacing space characters with %20) and appends it to any static URL string in the URL attribute to create a full URL link.
Use this attribute to do data drill-down from your charts.
For more information on using URLs in graphs, see "Linking Dynamically from Graphs".

Attribute	Description
query	The query containing the data.
valueColumn	The query column containing the values to be graphed.
itemColumn	(Optional) The query column containing the description for this data point. The item normally appears on the horizontal axis of bar and line graphs, on the vertical axis of horizontal bar graphs, and in the legend in pie charts.
URL	(Optional) Works only with bar, horizontal bar, and pie charts in Flash file format. A static prefix for all data point URLs. When the user clicks a bar or pie wedge, the page links to a URL created by appending the data from the data point's `URLColumn` value. Use this attribute to specify a string that is part of all links on a chart, such as http://www.mycompany.com/myapp/salary_info/chart_details/.
URLColumn	(Optional) Works only with bar, horizontal bar, and pie charts in Flash file format. The query column containing the data point-specific part of a URL to load when the user clicks the corresponding data point the graph. ColdFusion encodes the contents of the query field, in URL format (for example, replacing space characters with %20) and appends it to any static URL string in the `URL` attribute to create a full URL link. Use this attribute to do data drill-down from your charts. For more information on using URLs in graphs, see "Linking Dynamically from Graphs".

The ability to use queries of queries, as described in Chapter 3, "Using Query Results in Queries" provides significant power in generating the data for the chart. For example, you can use aggregating clauses operators such as SUM, AVG, and GROUP BY to create a query of queries with statistical data based on a raw database query.

You can also take advantage of the ability to reference and modify query data dynamically. For example, you can loop through the entries in a query column and reformat the data to show whole dollar values

Example: graphing a query of queries

The example in the following procedure analyzes the salary data in the CompanyInfo database and displays three graphs:

To graph a query of queries:

Create a new application page in ColdFusion Studio.

Edit the page so that it appears as follows:

<!-- Get the raw data from the database. -->

<cfquery name="GetSalaries" datasource="CompanyInfo">

  SELECT Departmt.Dept_Name, 

    Employee.Salary

  FROM Departmt, Employee

  WHERE Departmt.Dept_ID = Employee.Dept_ID

</cfquery>

<!-- Generate a query with statistical data for each department. -->

<cfquery dbtype = "query" name = "DeptSalaries">

  SELECT 

    Dept_Name,

    AVG(Salary) AS AvgByDept

  FROM GetSalaries

  GROUP BY Dept_Name

</cfquery>



<!--- Reformat the generated numbers to show only thousands --->

<cfloop index="i" from="1" to="#DeptSalaries.RecordCount#">

  <cfset DeptSalaries.AvgByDept[i]=Round(DeptSalaries.AvgByDept[i]/1000)*1000>

</cfloop>



<html>

<head>

  <title>Employee Salary Analysis</title>

</head>



<body>

<h1>Employee Salary Analysis</h1> 



<!--- Bar graph, from DeptSalaries Query of Queries --->

<cfgraph type="bar" 

  query="DeptSalaries" 

  valueColumn="AvgByDept" 

  itemColumn="Dept_Name">

</cfgraph>

<br>



</body>

</html>

Save the page as graphdata.cfm in myapps under the Web root directory. For example, the directory path on your machine might be:
C:\Inetpub\wwwroot\myapps on Windows NT
Return to your browser and enter the following URL to view graphdata.cfm:
http://127.0.0.1/myapps/graphdata.cfm

Reviewing the code

The following table describes the code and its function:

Code

Description

<cfquery name="GetSalaries" datasource="CompanyInfo"> SELECT Departmt.Dept_Name, Employee.Salary FROM Departmt, Employee WHERE Departmt.Dept_ID = Employee.Dept_ID </cfquery>
Query the CompanyInfo database to get the Dept_Name and Salary for each employee. Because the Dept_Name is in the Departmt table and the Salary is in the Employee table, you need a table join in the WHERE clause. The raw results of this query could be used elsewhere on the page.

<cfquery dbtype = "query" name = "DeptSalaries"> SELECT Dept_Name, AVG(Salary) AS AvgByDept FROM GetSalaries GROUP BY Dept_Name </cfquery>
Generate a new query from the GetSalaries query. Use the AVG aggregating function to get statistical data on the employees. Use the GROUP BY statement to ensure that there is only one row for each department.

<cfloop index="i" from="1" to="#DeptSalaries.RecordCount#"> <cfset DeptSalaries.AvgByDept[i]= Round(DeptSalaries.AvgByDept[i] /1000)*1000> </cfloop>
Loop through all the rows in DeptSalaries query and round the salary data to the nearest thousand. This loop uses the query variable RecordCount to get the number of rows and changes the contents of the query object directly.

<cfgraph type="bar" query="DeptSalaries" valueColumn="AvgByDept" itemColumn="Dept_Name" > </cfgraph>
Create a bar graph using the data from the AvgByDept column of the DeptSalaries query. Label the bars with the Department names.

Code	Description
<cfquery name="GetSalaries" datasource="CompanyInfo"> SELECT Departmt.Dept_Name, Employee.Salary FROM Departmt, Employee WHERE Departmt.Dept_ID = Employee.Dept_ID </cfquery>	Query the CompanyInfo database to get the Dept_Name and Salary for each employee. Because the Dept_Name is in the Departmt table and the Salary is in the Employee table, you need a table join in the WHERE clause. The raw results of this query could be used elsewhere on the page.
<cfquery dbtype = "query" name = "DeptSalaries"> SELECT Dept_Name, AVG(Salary) AS AvgByDept FROM GetSalaries GROUP BY Dept_Name </cfquery>	Generate a new query from the GetSalaries query. Use the AVG aggregating function to get statistical data on the employees. Use the GROUP BY statement to ensure that there is only one row for each department.
<cfloop index="i" from="1" to="#DeptSalaries.RecordCount#"> <cfset DeptSalaries.AvgByDept[i]= Round(DeptSalaries.AvgByDept[i] /1000)*1000> </cfloop>	Loop through all the rows in DeptSalaries query and round the salary data to the nearest thousand. This loop uses the query variable RecordCount to get the number of rows and changes the contents of the query object directly.
<cfgraph type="bar" query="DeptSalaries" valueColumn="AvgByDept" itemColumn="Dept_Name" > </cfgraph>	Create a bar graph using the data from the AvgByDept column of the DeptSalaries query. Label the bars with the Department names.

Graphing individual data points

When you graph individual data points you specify each data point by putting a cfgraphdata tag in the cfgraph tag body. For example, the following code creates a simple pie chart:

<cfgraph type="pie" title="Income by Department">

  <cfgraphdata item="New Vehicle Sales" value=500000>

  <cfgraphdata item="Used Vehicle Sales" value=250000>

  <cfgraphdata item="Leasing" value=300000>

  <cfgraphdata item="Service" value=400000>

</cfgraph>

This pie chart displays the income values of four departments. Each cfgraph tag specifies a department's income and the corresponding item description for the legend. The values are specified by individual ColdFusion variables. The title "Income by Department" appears above the chart.

The cfgraphdata tag lets you specify the following information about a data point:

Attribute

Description

value
The data value to be graphed.

item
(Optional) The description for this data point. The item normally appears on the horizontal axis of bar and line graphs, on the vertical axis of horizontal bar graphs, and in the legend in pie charts.

color
(Optional) The color of the bar or pie slice. Ignored for line and area graphs.

URL
(Optional) Works only with bar, horizontal bar, and pie charts in Flash file format.
A URL to load when the user clicks this data point. Use this attribute to do data drill-down from your charts.
For more information on using URLs in graphs, see "Linking Dynamically from Graphs".

Attribute	Description
value	The data value to be graphed.
item	(Optional) The description for this data point. The item normally appears on the horizontal axis of bar and line graphs, on the vertical axis of horizontal bar graphs, and in the legend in pie charts.
color	(Optional) The color of the bar or pie slice. Ignored for line and area graphs.
URL	(Optional) Works only with bar, horizontal bar, and pie charts in Flash file format. A URL to load when the user clicks this data point. Use this attribute to do data drill-down from your charts. For more information on using URLs in graphs, see "Linking Dynamically from Graphs".

Combining a query and data points

To graph data from both query and individual data value, you specify the query name and related attributes in the cfgraph tag and provide the additional data points and their appearance attributes in cfgraphdata tags.

Data specified by a cfgraphdata tag is graphed before (for example, to the left on a bar chart) the data from a query.

For example, if the database is missing data for one department, you can add the information manually:

<cfgraph type="bar"  title="Salaries by Department" query="DataTable"

itemColumn ="Dept_Name" valueColumn="AvgByDept"

  <cfgraphdata item="Facilities" value="35000">

</cfgraph>

Developing ColdFusion Applications
Graphing Data