Download Project Files - The Project should be connected to the Intranet example database that comes with CodeCharge Studio.

In Cold Fusion MX, the CFChart tag in conjunction with the CFChartSeries tag can be used to generate and render graphic charts that are based on information drawn from database queries. This means that you can produce charts to go along with the grid forms you create in CodeCharge Studio. This is made possible by the use of Query of Queries which are special Cold Fusion queries that are based on existing queries. As such, you can use the default query for a grid form to produce a Query of Queries which is then used to create a chart. In certain situations where the query fits the bill, you can produce a chart based on the original query used for a grid instead of producing a query of queries.

In this article, we will demonstrate how to enhance the Default page of the Task Manager example solution such that it shows four graphs based on information used to create the Tasks grid. In the Task Manager example, each task is assigned a status, priority, project, as well as employee. The graphs we will produce will show the distribution of all the tasks based on the four properties above.

To begin the process, we will first add a table with four Label type fields which will eventually contain the charts. The idea here is that after the chart has been generated and saved to a file, we shall construct a <img> tag and use it to display the chart. This <img> tag will be the value of the Label field. The diagram below shows the table with the four Labels. Note that this is in design mode and this new table should appear below the current tasks grid in the default page.

For each of the Labels above, the Content property has to be set to HTML as opposed to Text. This is so that the Label can be used to hold the contents of the <img> tag.

After setting up the Labels, we will then proceed to add the event code that will create the charts and save them to image files. Since we will be making use of the query that is used in the tasks grid form, we have to place our code in the After Execute Select event which occurs after the query has been processed. This way, we can use the existing query as the basis for the query of queries that will be contain data for the charts. The first chart to be created will be the status chart.

<!--- Generate a query of queries for the status chart. --->
<!--- Note that queryTasks is the name of the query that is used to populate the tasks grid form --->
<cfquery dbtype = "query" name = "Status_chart_query">
SELECT status_name, Count(status_name) AS StatusCount
FROM queryTasks
GROUP BY status_name
</cfquery>

<!--- The CFChart and CFChartSeries tags set the properties of the chart. Note that the CFChartSeries tag uses the Status_chart_query created above--->
<cfchart
xAxisTitle="Status"
yAxisTitle="Task Count"
font="Arial"
gridlines=8
showXGridlines="yes"
showYGridlines="yes"
showborder="yes"
show3d="yes"
name="statusChart"
format="jpg">

<cfchartseries
type="bar"
query="Status_chart_query"
valueColumn="StatusCount"
itemColumn="status_name"
seriesColor="olive"
paintStyle="plain"
/>
</cfchart>

<!--- After the chart has been produced, the CFFile tag is used to save it to disk so that it can later be used in a HTML <img> tag --->
<cffile
action="WRITE"
charset="ISO-8859-1"
file="D:\inetpub\wwwroot\CCS\TaskManagerCF\statuschart.jpg"
output="#statusChart#">

The above code will thus create an image file containing the status chart. The process of creating the 3 other charts is essentially the same except that the query of queries is adjusted to retrieve the appropriate data and the grid properties are assigned appropriate names. The remaining of the code in the After Execute Select event is as follows:

<!--- Generate a query of queries for the priority chart. --->
<!--- Note that queryTasks is the name of the query that is used to populate the tasks grid form --->
<cfquery dbtype = "query" name = "priority_chart_query">
SELECT
priority_name,
Count(priority_name) AS priorityCount
FROM queryTasks
GROUP BY priority_name
</cfquery>

<!--- The CFChart and CFChartSeries tags set the properties of the chart. Note that the CFChartSeries tag uses the priority_chart_query created above--->
<cfchart
xAxisTitle="priority"
yAxisTitle="Task Count"
font="Arial"
showXGridlines="yes"
showYGridlines="yes"
showborder="yes"
show3d="yes"
name="priorityChart"
format="jpg">

<cfchartseries
type="pie"
query="priority_chart_query"
valueColumn="priorityCount"
itemColumn="priority_name"
paintStyle="plain"
/>
</cfchart>

<!--- After the chart has been produced, the CFFile tag is used to save it to disk so that it can later be used in a HTML <img> tag --->
<cffile
action="WRITE"
charset="ISO-8859-1"
file="D:\inetpub\wwwroot\CCS\TaskManagerCF\prioritychart.jpg"
output="#priorityChart#">

<!--- Generate a query of queries for the project chart. --->
<!--- Note that queryTasks is the name of the query that is used to populate the tasks grid form --->
<cfquery dbtype = "query" name = "project_chart_query">
SELECT
project_name,
Count(project_name) AS projectCount
FROM queryTasks
GROUP BY project_name
</cfquery>

<!--- The CFChart and CFChartSeries tags set the properties of the chart. Note that the CFChartSeries tag uses the project_chart_query created above--->
<cfchart
xAxisTitle="project"
yAxisTitle="Task Count"
font="Arial"
showXGridlines="yes"
showYGridlines="yes"
showborder="yes"
show3d="yes"
name="projectChart"
format="jpg">

<cfchartseries
type="cylinder"
query="project_chart_query"
valueColumn="projectCount"
itemColumn="project_name"
seriesColor="##cccffd"
paintStyle="light"
/>
</cfchart>

<!--- After the chart has been produced, the CFFile tag is used to save it to disk so that it can later be used in a HTML <img> tag --->
<cffile
action="WRITE"
charset="ISO-8859-1"
file="D:\inetpub\wwwroot\CCS\TaskManagerCF\projectchart.jpg"
output="#projectChart#">

<!--- Generate a query of queries for the AssignedTo chart. --->
<!--- Note that queryTasks is the name of the query that is used to populate the tasks grid form --->
<cfquery dbtype = "query" name = "AssignedTo_chart_query">
SELECT
emp_name,
Count(emp_name) AS AssignedToCount
FROM queryTasks
GROUP BY emp_name
</cfquery>

<!--- The CFChart and CFChartSeries tags set the properties of the chart. Note that the CFChartSeries tag uses the AssignedTo_chart_query--->
<cfchart
xAxisTitle="AssignedTo"
yAxisTitle="Task Count"
font="Arial"
gridlines=6
showXGridlines="yes"
showYGridlines="yes"
showborder="yes"
show3d="yes"
name="AssignedToChart"
format="jpg">

<cfchartseries
type="cone"
query="AssignedTo_chart_query"
valueColumn="AssignedToCount"
itemColumn="emp_name"
seriesColor="##CC0066"
paintStyle="shade"
/>
</cfchart>

<!--- After the chart has been produced, the CFFile tag is used to save it to disk so that it can later be used in a HTML <img> tag --->
<cffile
action="WRITE"
charset="ISO-8859-1"
file="D:\inetpub\wwwroot\CCS\TaskManagerCF\AssignedTochart.jpg"
output="#AssignedToChart#">

Now that we have the four charts generated to image files, the next step is to display them in the table we created earlier on for this purpose. To do this, we shall add a Retrieve Value for Control action to the Before Show event of each of the Label fields. In the Source Name property of the action, we will specify the HTML code for the <img> tag which will display the image containing the chart. For instance, for the status chart Label field, the properties of the Retrieve Value for Control action will be as follows:

Control Name: statusChartLabel
Source Type: Expression
Source Name: "<img src='D:\Inetpub\wwwroot\ccs\TaskManagerCF\statusChart.jpg' </img>"

and the rest of the chart Label fields will be configured as follows:

Control Name: priorityChartLabel
Source Type: Expression
Source Name: "<img src='D:\Inetpub\wwwroot\ccs\TaskManagerCF\priorityChart.jpg' </img>"

Control Name: ProjectChartLabel
Source Type: Expression
Source Name: "<img src='D:\Inetpub\wwwroot\ccs\TaskManagerCF\projectchart.jpg' </img>"

Control Name: AssignedToChartLabel
Source Type: Expression
Source Name: "<img src='D:\Inetpub\wwwroot\ccs\TaskManagerCF\AssignedToChart.jpg' </img>"

After making all the above changes, you can then generate and view the page. The output should be similar to the first image at the top of this page but the charts will vary depending on the information in the database.

Conclusion:
For Cold Fusion 5.0 users, the CFGraph and CFGraphSeries tags serve the same purpose as the CFChart and CFChartSeries tags in Cold Fusion MX. However, CFGraph and CFGraphSeries are deprecated in Cold Fusion MX in preference to CFChart and CFChartSeries.