MACROMEDIA COLDFUSION MX 61-DEVELOPING COLDFUSION MX, Develop Manual

Page: 635 / 860

Controlling chart appearance 635
<!--- Query of Queries for average salary by start year --->
<cfquery dbtype = "query" name = "HireSalaries">
SELECT
StartDate,
AVG(Salary) AS AvgByStart
FROM GetSalaries
GROUP BY StartDate
</cfquery>
<!--- Round average salaries to thousands --->
<cfloop index="i" from="1" to="#HireSalaries.RecordCount#">
<cfset HireSalaries.AvgByStart[i]=Round(HireSalaries.AvgByStart[i]/
1000)*1000>
</cfloop>
4
Add the following
cfchart
tag before the end of the
body
tag block:
<!--- Area-style Line chart, from HireSalaries Query of Queries --->
<cfchart
chartWidth=400
BackgroundColor="##FFFF00"
show3D="yes"
>
<cfchartseries
type="area"
query="HireSalaries"
valueColumn="AvgByStart"
itemColumn="StartDate"
/>
</cfchart>
<br>
5
Save the page.
6
Return to your browser and enter the following URL to view chartdata.cfm:
http://127.0.0.1/myapps/chartdata.cfm
Reviewing the code
The following table describes the highlighted code and its function:
Code Description
Employee.StartDate,
Add the employee start date to the data in the
GetSalaries query.
<cfloop index="i" from="1"
to="#GetSalaries.RecordCount#">
<cfset GetSalaries.StartDate[i]=
NumberFormat(DatePart("yyyy",
GetSalaries.StartDate[i]) ,9999)>
</cfloop>
Use a cfloop tag to extract the year of hire from
each employee’s hire data, and convert the result to a
four-digit number.
<cfquery dbtype = "query" name =
"HireSalaries">
SELECT
StartDate,
AVG(Salary) AS AvgByStart
FROM GetSalaries
GROUP BY StartDate
</cfquery>
Create a second query from the GetSalaries query.
This query contains the average salary for each start
year.