986
DEVELOPING
COLDFUSION 9 APPLICATIONS
Working with Documents, Charts, and Reports
Las
t
upda
te
d 8/5/2010
This example 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 following table lists the attributes of the
cfchartseries
tag that you use when working with queries:
Charting a query of queries
In addition to charting the results of a query, you can also chart the results of a query of queries. For more information
about using query of queries, see “
Using Query of Queries
” on page 428. Query of queries provides significant power
in generating the data for the chart. For example, you can use aggregating functions such as SUM, AVG, and GROUP
BY to create a query of queries with statistical data based on a raw database query. For more information, see “
Using
Query of Queries
” on page 428.
You can also take advantage of the ability to dynamically reference and modify query data. For example, you can loop
through the entries in a query column and reformat the data to show whole dollar values.
The example in the following procedure analyzes the salary data in the cfdocexamples database using a query of
queries, and displays the data as a bar chart.
1
Create a ColdFusion page with the following content:
<!--- Get the raw data from the database. --->
<cfquery name="GetSalaries" datasource="cfdocexamples">
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>
Attribute
Description
query
The query that contains the data. Also specify the
valueColumn
and
itemColumn
.
valueColumn
The query column that contains the values to chart.
itemColumn
The query column that contains the description for this data point. The item normally appears on the horizontal
axis of bar and line charts, on the vertical axis of horizontalbar charts, and in the legend in pie charts.