Developing ColdFusion MX Applications with CFML Previous Up One Level Next Home Search
Charting and Graphing Data


Charting data

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:

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.

Charting a query

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:
Attribute
Description
query
The query that contains the data. You must also specify valueColumn and itemColumn.
valueColumn
The query column that contains the values to be charted.
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 horizontal bar charts, and in the legend in pie charts.

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.

To chart a query of queries:

  1. Create a new ColdFusion page with the following content:
    <!-- 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>
  2. Save the page as chartdata.cfm in myapps under the web root directory. For example, the directory path on Windows might be C:\Inetpub\wwwroot\myapps.
  3. Return to your browser and enter the following URL to view chartdata.cfm:

    http://127.0.0.1/myapps/chartdata.cfm

    The following figure appears:

    Three dimentional bar chart showing the results of a query of queries

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.

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.
 
<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>
Create a bar chart using the data from the AvgByDept column of the DeptSalaries query. Label the bars with the Department names.

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>

Charting individual data points

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:
Attribute
Description
value
The data value to be charted. This attribute is required.
item
(Optional) The description for this data point. The item appears on the horizontal axis of bar and line charts, on the vertical axis of horizontal bar charts, and in the legend in pie charts.

Combining a query and data points

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>

Charting multiple data collections

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:

The following figure shows these options for combining two bar charts:

The three options for combining two bar charts in a single chart

You can also combine chart types. The following is a combination bar and line chart:

Example cobining a bar chart and a line chart in a single 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.

To create a combination bar and a line chart:

  1. Open chartdata.cfm in your editor.
  2. Edit the 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>
  3. Save the page as chart2queries.cfm in myapps under the web root directory. For example, the directory path on Windows might be C:\Inetpub\wwwroot\myapps.
  4. Return to your browser and enter the following URL to view chart2queries.cfm:

    http://127.0.0.1/myapps/chart2queries.cfm

Writing a chart to a variable

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".

To write a chart to a variable and a file:

  1. Create a new ColdFusion page with the following content:
    <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>
  2. Save the page as chartToFile.cfm in myapps under the web root directory.
  3. Return to your browser and enter the following URL to view chartToFile.cfm:

    http://127.0.0.1/myapps/chartToFile.cfm

    The chart is saved to disk as c:\inetpub\wwwroot\charts\vehicle.jpg

Reviewing the code

The following table describes the highlighted code and its function:
Code
Description
 
<cfchart
  name="myChart"
  format="jpg">
Define a chart written to the variable myChart using the JPG format. 
<cffile 
  action="WRITE"
  charset="ISO-8859-1"
  file=
    "c:\inetpub\wwwroot\charts\vehicle.jpg" 
  output="#myChart#">
Use cffile to write the chart to a file.
You must specify a charset of ISO-8859-1 when writing binary chart data to a file. 
<img
  src=
    "c:\inetpub\wwwroot\charts\vehicle.jpg"
  height=240 
  width=320>
Use the HTML img tag to display the chart.

Copyright © 2002, Macromedia Inc. All rights reserved. Previous Up Next Home Search

Comments