ColdFusion 9.0 Resources

Creating charts: examples

Contents [Hide]

Creating a bar chart
Creating a pie chart
Creating an area chart
Setting curve chart characteristics

Creating a bar chart

The example in the following procedure adds a title to the bar chart, specifies that the chart is three-dimensional, adds grid lines, sets the minimum and maximum y-axis values, and uses a custom set of colors.

Open the chartdata.cfm file in your editor.

Edit the cfchart tag so that it appears as follows:

<!--- Bar chart, from Query of Queries ---> 
<cfchart  
        scaleFrom=40000 
        scaleTo=100000 
        font="arial" 
        fontSize=16 
        gridLines=4  
        show3D="yes" 
        foregroundcolor="##000066" 
        databackgroundcolor="##FFFFCC" 
        chartwidth="450" 
    > 
 
    <cfchartseries  
        type="bar"  
        query="DeptSalaries"  
        valueColumn="AvgByDept"  
        itemColumn="Dept_Name" 
        seriescolor="##33CC99" 
        paintstyle="shade" 
        /> 
 
</cfchart>

Save the file as chartdatastyle1.cfm.
View the chartdatastyle1.cfm page in your browser.

Reviewing the code

The following table describes the code in the preceding example.

Code	Description
`scaleFrom=40000`	Set the minimum value of the vertical axis to 40000.
`scaleTo=100000`	Set the maximum value of the vertical axis to 100000. The minimum value is the default, `0`.
`font="arial"`	Displays text using the Arial font.
`fontSize=16`	Makes the point size of the labels 16 points.
`gridLines = 4`	Displays four grid lines between the top and bottom of the chart.
`show3D = "yes"`	Shows the chart in 3D.
`foregroundcolor="##000066"`	Sets the color of the text, gridlines, and labels.
`databackgroundcolor="##FFFFCC"`	Sets the color of the background behind the bars.
`seriescolor="##33CC99"`	Sets the color of the bars.
`paintstyle="shade"`	Sets the paint display style.

Creating a pie chart

The example in the following procedure adds a pie chart to the page.

Open the chartdata.cfm file in your editor.

Edit the DeptSalaries query and the cfloop code so that it appears as follows:

<!--- A query to get statistical data for each department. ---> 
<cfquery dbtype = "query" name = "DeptSalaries"> 
    SELECT  
        Dept_Name, 
        SUM(Salary) AS SumByDept, 
        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.SumByDept[i]=Round(DeptSalaries.SumByDept[i]/ 
    1000)*1000> 
    <cfset DeptSalaries.AvgByDept[i]=Round(DeptSalaries.AvgByDept[i]/ 
    1000)*1000> 
</cfloop>

Add the following cfchart tag:

<!--- Pie chart, from DeptSalaries Query of Queries. ---> 
<cfchart  
        tipStyle="mousedown"  
        font="Times" 
        fontsize=14 
        fontBold="yes" 
        backgroundColor = "##CCFFFF" 
        show3D="yes" 
        > 
         
    <cfchartseries  
        type="pie"  
        query="DeptSalaries"  
        valueColumn="SumByDept"  
        itemColumn="Dept_Name"  
        colorlist="##6666FF,##66FF66,##FF6666,##66CCCC" 
        /> 
</cfchart> 
<br>

Save the file as chartdatapie1.cfm.
View the chartdatapie1.cfm page in your browser:

Reviewing the code

The following table describes the code and its function:

Code	Description
SUM(Salary) AS SumByDept,	In the DeptSalaries query, add a SUM aggregation function to get the sum of all salaries per department.
<cfset DeptSalaries.SumByDept[i]=Round(DeptSalaries.SumByDept[i]/ 1000)*1000>	In the cfloop tag, round the salary sums to the nearest thousand.
<cfchart tipStyle="mousedown" font="Times" fontsize=14 fontBold="yes" backgroundColor = "##CCFFFF" show3D="yes" >	Show a tip only when a user clicks the chart, display text in Times bold font, set the background color to light blue, and display the chart in three dimensions.
<cfchartseries type="pie" query="DeptSalaries" valueColumn="SumByDept" itemColumn="Dept_Name" colorlist="##6666FF,##66FF66,##FF6666,##66CCCC" />	Create a pie chart using the SumByDept salary sum values from the DeptSalaries query. Use the contents of the Dept_Name column for the item labels displayed in the chart legend. Get the pie slice colors from a custom list, which uses hexadecimal color numbers. The double number signs prevent ColdFusion from trying to interpret the color data as variable names.

Creating an area chart

The example in the following procedure adds an area chart to the salaries analysis page. The chart shows the average salary by start date to the salaries analysis page. It shows the use of a second query of queries to generate a new analysis of the raw data from the GetSalaries query. It also shows the use of additional cfchart attributes.

Open the chartdata.cfm file in your editor.

Edit the GetSalaries query so that it appears as follows:

<!-- Get the raw data from the database. --> 
<cfquery name="GetSalaries" datasource="cfdocexamples"> 
    SELECT Departmt.Dept_Name,  
        Employee.StartDate, 
        Employee.Salary 
    FROM Departmt, Employee 
    WHERE Departmt.Dept_ID = Employee.Dept_ID 
</cfquery>

Add the following code before the html tag:

<!--- Convert start date to start year. ---> 
<!--- Convert the date to a number for the query to work ---> 
<cfloop index="i" from="1" to="#GetSalaries.RecordCount#"> 
<cfset GetSalaries.StartDate[i]=NumberFormat(DatePart("yyyy", GetSalaries.StartDate[i]) ,9999)> 
</cfloop> 
 
<!--- 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>

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>

Save the page.
View the chartdata.cfm page in your browser.

Reviewing the code

The following table describes the 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 the 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.
<cfloop index="i" from="1" to="#HireSalaries.RecordCount#"> <cfset HireSalaries.AvgByStart[i]=Round(HireSalaries.AvgByStart[i]/1000)*1000> </cfloop>	Round the salaries to the nearest thousand.
<cfchart chartWidth=400 BackgroundColor="##FFFF00" show3D="yes" > <cfchartseries type="area" query="HireSalaries" valueColumn="AvgByStart" itemColumn="StartDate" /> </cfchart>	Create an area chart using the HireSalaries query. Chart the average salaries against the start date. Limit the chart width to 400 pixels, show the chart in three dimensions, and set the background color to white.

Setting curve chart characteristics

Curves charts use the attributes already discussed. However, curve charts require a large amount of processing to render. For fastest performance, create them offline, write them to a file or variable, and then reference them in your application pages. For information on creating offline charts, see Writing a chart to a variable.