One of the most important considerations when you chart data is the way you supply the data to the cfchart
tag. You can supply data in the following ways:
cfchartseries
tags.
cfchartdata
tags.cfchartdata
tags. Note: The cfchart
tag charts 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.
When you chart a query, you specify the query name using the query
attribute of the cfchartseries
tag. For example, the code for a simple bar chart might be as follows:
<cfchart
xAxisTitle="Department" yAxisTitle="Salary Average" > <cfchartseries type="bar" query="DataTable" valueColumn="AvgByDept" itemColumn="Dept_Name" /> </cfchart>
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.
You use the following attributes of the cfchartseries
tag when working with queries:
Using queries 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 Chapter 22, "Using Query of Queries".
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.
The example in the following procedure analyzes the salary data in the CompanyInfo database using a query of queries and displays the data as a bar chart.
<!-- 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 chart, from DeptSalaries Query of Queries ---> <cfchart xAxisTitle="Department" yAxisTitle="Salary Average" font="Arial" gridlines=6 showXGridlines="yes" showYGridlines="yes" showborder="yes" show3d="yes" > <cfchartseries type="bar" query="DeptSalaries" valueColumn="AvgByDept" itemColumn="Dept_Name" seriesColor="olive" paintStyle="plain" /> </cfchart> <br> </body> </html>
http://127.0.0.1/myapps/chartdata.cfm
The following figure appears:
Note: If a query contains two rows with the same value for the itemColumn
, ColdFusion graphs the last row in the query for that value. For the previous example, if the query contains two rows for the Sales department, ColdFusion graphs the value for the last row in the query for Sales.
The following table describes the code and its function:
You can also rewrite this example to use the cfoutput
and cfchartdata
tags within the cfchartseries
tag, instead of using the loop, to round the salary data, as the following code shows:
<cfchartseries
type="bar" seriesColor="olive" paintStyle="plain"> <cfoutput query="deptSalaries"> <cfchartdata item="#dept_name#" value=#Round(AvgByDept/1000)*1000#> </cfoutput> </cfchartseries>
When you chart individual data points, you specify each data point by inserting a cfchartdata
tag in the cfchartseries
tag body. For example, the following code creates a simple pie chart:
<cfchart>
<cfchartseries type="pie"> <cfchartdata item="New Vehicle Sales" value=500000> <cfchartdata item="Used Vehicle Sales" value=250000> <cfchartdata item="Leasing" value=300000> <cfchartdata item="Service" value=400000> </cfchartseries> </cfchart>
This pie chart displays four types of revenue for a car dealership. Each cfchartdata
tag specifies a department's income and description for the legend.
Note: If two data points have the same item name, ColdFusion graphs the value for the last one specified within the cfchart
tag.
The cfchartdata
tag lets you specify the following information about a data point:
To chart data from both query and individual data values, you specify the query name, and related attributes, in the cfchartseries
tag, and provide additional data points using the cfchartdata
tag.
ColdFusion displays the chart data specified by a cfchartdata
tag before the data from a query; for example, to the left on a bar chart. You can use the sortXAxis
attribute of cfchart
to sort data alphabetically long the x-axis.
One use of combining queries and data points could be if the database is missing data for one department, you can add the information manually. The following example adds data for the Facilities and Documentation departments to the salary data obtained from the query shown in the previous section:
<cfchart
<cfchartseries type="bar" query="DataTable" itemColumn ="Dept_Name" valueColumn="AvgByDept" > <cfchartdata item="Facilities" value="35000"> <cfchartdata item="Documentation" value="725000"> </cfchartseries> </cfchart>
Sometimes, you might have more than one series of data to display on a single chart, or you want to compare two sets of data on the same chart. In some cases, you might want to use different charting types on the same chart. For example, you might want to include a line chart on a bar chart.
To combine multiple data series into a single chart, insert multiple cfchartseries
tags within a single cfchart
tag. You control how the multiple data collections are charted using the seriesPlacement
attribute of the cfchart
tag. Using this attribute, you can specify the following options:
default
Let ColdFusion determine the best mehtod for combining the data.
cluster
Place corresponding chart elements from each series next to each other. stacked
Combine the corresponding elements of each series.percent
Show the elements of each series as a percentage of the total of all corresponding elements.The following figure shows these options for combining two bar charts:
You can also combine chart types. The following is a combination bar and line chart:
The only chart type that you cannot mix with others is the pie chart. If you define one of the data series to use a pie chart, no other chart will appear.
The following example creates the previous figure showing a bar chart with a line chart added to it. In this example, you chart the salary of permanent employees (bar) against contract employees (line).
Note: The layering of multiple series depends on the order that you specify the cfchartseries
tags. For example, if a bar chart is specified first and a line chart second, the bar chart appears in front of the line chart in the final chart.
cfchart
tag so that it appears as follows:<cfchart backgroundColor="white" xAxisTitle="Department" yAxisTitle="Salary Average" font="Arial" gridlines=6 showXGridlines="yes" showYGridlines="yes" showborder="yes" > <cfchartseries type="line" seriesColor="blue" paintStyle="plain" seriesLabel="Contract Salaries" > <cfchartdata item="HR" value=70000> <cfchartdata item="Marketing" value=95000> <cfchartdata item="Sales" value=80000> <cfchartdata item="Training" value=93000> </cfchartseries> <cfchartseries type="bar" query="DeptSalaries" valueColumn="AvgByDept" itemColumn="Dept_Name" seriesColor="gray" paintStyle="plain" seriesLabel="Dept. Average Salaries" /> </cfchart>
http://127.0.0.1/myapps/chart2queries.cfm
In some cases, your application might have charts that are static or charts that, because of the nature of the data input, take a long time to render. In this scenario, you can create a chart and write it to a variable.
Once written to a variable, other ColdFusion pages can access the variable to display the chart, or you can write the variable to disk to save the chart to a file. This lets you create or update charts only as needed, rather than every time someone requests a page containing a chart.
You use the name attribute of the cfchart
tag to write a chart to a variable. If you specify the name attribute, the chart is not rendered in the browser but is written to the variable.
You can save the chart as a Flash movie (.swf file), or as a JPG or PNG image file. If you save the image as a Flash movie, you can pass the variable back to a Flash client using ColdFusion Flash Remoting. For more information, see Chapter 29, "Using the Flash Remoting Service".
Note: If you write the chart to a JPG or PNG file, mouseover tips and URLs embedded in the chart for data drill-down will not work when you redisplay the image from the file. However, if you save the image a Flash movie, both tips and drill-down URLs will work. For more information on data drill-down, see "Linking charts to URLs".
<cfchart name="myChart" format="jpg" > <cfchartseries type="pie"> <cfchartdata item="New Vehicle Sales" value=500000> <cfchartdata item="Used Vehicle Sales" value=250000> <cfchartdata item="Leasing" value=300000> <cfchartdata item="Service" value=400000> </cfchartseries> </cfchart> <cffile action="WRITE" charset="ISO-8859-1" file="c:\inetpub\wwwroot\charts\vehicle.jpg" output="#myChart#"><img src="c:\inetpub\wwwroot\charts\vehicle.jpg" height=240
width=320>
http://127.0.0.1/myapps/chartToFile.cfm
The chart is saved to disk as c:\inetpub\wwwroot\charts\vehicle.jpg
The following table describes the highlighted code and its function: