Using a Query to Create a Chart | ColdFusion MX Professional Projects

You can build a chart from the database table by using the <cfquery> tag. To use the database query, you need to create a database and data source name (DSN). The following example is based on the emp database and the empdsn data source. The emp database has an employee table whose columns are empname, salary, department, and incentive. Here's the syntax to create a query-based chart:

 <CFCHART attribute. . . .> <CFCHARTSERIES TYPE="type"     QUERY="query to base the chart on"     VALUECOLUMN="name of the column to use its values."     ITEMCOLUMN="name of the column to derive labels"     Any other attributes= . . . .> </CFCHARTSERIES> </CFCHART>

Note

Databases and the <cfquery> tag attributes are covered in Chapter 5, "Accessing Databases Using ColdFusion MX."

The code to create a chart based on query result is as follows:

 <!--- chartquery.cfm template---> <CFQUERY NAME="GetSalaries" DATASOURCE="empdsn">    SELECT salary, department from employee </CFQUERY> <HTML> <HEAD>    <TITLE>Employee Salary Analysis</TITLE> </HEAD> <BODY> <H1>Employee Salary Analysis</H1> <!--- Bar chart, from GetSalaries Query ---> <CFCHART       XAXISTITLE="Department"       YAXISTITLE="Salary"       FONT="Arial"       GRIDLINES=6       SHOWXGRIDLINES="yes"       SHOWYGRIDLINES="yes"       SHOWBORDER="yes"       SHOW3D="yes"    >    <CFCHARTSERIES       TYPE="bar"       QUERY="GetSalaries"       VALUECOLUMN="salary"       ITEMCOLUMN="department"       SERIESCOLOR="olive"       PAINTSTYLE="plain"    /> </CFCHART> <BR> </BODY> </HTML>

Figure 26.5 shows the output of the preceding code.

click to expand
Figure 26.5: Creating a chart based on a query.

Creating a Query and Data Points Chart

You can use a query along with individual data points to build a chart. The following example shows how to combine them:

 <!--- ind_querychart.cfm template ---> <CFQUERY NAME="GetSalaries" DATASOURCE="empdsn">    SELECT salary, department from employee </CFQUERY> <HTML> <HEAD>    <TITLE>Example of using data points and a query to create a chart</TITLE> </HEAD> <BODY><H1>Employee Salary Analysis</H1> <!--- Line chart, from GetSalaries Query and data point values---> <CFCHART       XAXISTITLE="Department"       YAXISTITLE="Salary"       FONT="Arial"       GRIDLINES=6       SHOWXGRIDLINES="yes"       SHOWYGRIDLINES="yes"       SHOWBORDER="yes"       SHOW3D="No">    <CFCHARTSERIES       TYPE="Line"       QUERY="GetSalaries"       VALUECOLUMN="salary"       ITEMCOLUMN="department"       SERIESCOLOR="olive"       PAINTSTYLE="plain">       <CFCHARTDATA ITEM="First Value" VALUE=5000>       <CFCHARTDATA ITEM="Second Value" VALUE=2500>    </CFCHARTSERIES> </CFCHART> <BR> </BODY> </HTML>

Figure 26.6 shows the output of the preceding code.

click to expand
Figure 26.6: Query and data point chart example.

Charting Multiple Data Series

You can display more than one data series or more than one chart type on a single chart by using multiple <cfchartseries> tags within a single <cfchart> tag. Use the seriesplacement attribute of the <cfchart> tag to control the placement of the multiple data series. The seriesplacement attribute can take the following values:

default. Allows ColdFusion to determine the best method for combining the data in the chart.
cluster. Places the series next to each other for the corresponding elements.
stacked. Combines the corresponding elements of each series.
percent. Shows the elements of each series as a percentage of the total of all corresponding elements.

Figure 26.7 shows the cluster chart.

click to expand
Figure 26.7: The cluster chart.

Figure 26.8 shows the stacked chart.

click to expand
Figure 26.8: The stacked chart

Figure 26.9 shows the percent chart.

click to expand
Figure 26.9: The percent chart.

You can combine all the chart types except the pie chart. Here's the code to combine a bar chart and a line chart:

 <!--- barlinechart.cfm template ---> <CFQUERY NAME="GetSalaries" DATASOURCE="empdsn">    SELECT salary, department from employee </CFQUERY> <HTML> <HEAD> <TITLE>Combined Bar and Line Chart Example</TITLE> </HEAD> <BODY> <H2>Bar and Line Chart</H2> <CFCHART       XAXISTITLE="Department"       YAXISTITLE="Salary"       FONT="Arial"       GRIDLINES=6       SHOWXGRIDLINES="yes"       SHOWYGRIDLINES="yes"       SHOWBORDER="yes"       SHOW3D="No">    <CFCHARTSERIES       TYPE="Line"       QUERY="GetSalaries"       VALUECOLUMN="salary"       ITEMCOLUMN="department"       SERIESCOLOR="red"       PAINTSTYLE="plain">    </CFCHARTSERIES> <CFCHARTSERIES       TYPE="Bar"       QUERY="GetSalaries"       VALUECOLUMN="salary"       ITEMCOLUMN="department"       SERIESCOLOR="blue"       PAINTSTYLE="plain">    </CFCHARTSERIES> </BODY> </HTML>

Figure 26.10 shows the combined bar and line chart created by the preceding code.

click to expand
Figure 26.10: The combined line and bar chart.

Specify the chart type in <cfchartseries> in the same sequence as you want it to appear in the browser. For example, if you want a bar chart to appear before a line chart, specify the bar type option in <cfchartseries> first within the <cfchart> tag.

Writing a Chart to a Variable

You can write the chart to a variable by using the name attribute of the <cfchart> tag. Using this variable, you can save the chart to a disk file. This reduces the rendering time of the chart. Also, you can control the chart updating as per the requirement rather than displaying it with every page request. You can save the charts as Flash, JPG, or PNG image files. You can use the ColdFusion Flash Remoting service if you save the image as a Flash file. For more details on the Flash Remoting service, refer to the ColdFusion MX documentation.

The code to write a chart to a variable and save the variable in a file is as follows:

 <!--- Chartfile.cfm template ---> <HTML> <HEAD> <TITLE>writing the Chart to a Variable </TITLE> </HEAD> <BODY> <H2>Example of writing the chart to a variable</H2> <!--- name attribute writes the chart to a variable chartvar in and format attribute specifies to save the chart in jpg format ---> <CFCHART NAME="chartvar" FORMAT="jpg">    <CFCHARTSERIES TYPE="PIE">       <CFCHARTDATA ITEM="Manager" VALUE=5000>       <CFCHARTDATA ITEM="Executive" VALUE=2500>       <CFCHARTDATA ITEM="Senior Executive" VALUE=3500>       <CFCHARTDATA ITEM="CEO" VALUE=6000>    </CFCHARTSERIES> </CFCHART> <BR> <!--- the <cffile> tag writes the variable to a disk file named incentive.jpg ---> <CFFILE ACTION="Write" CHARSET="ISO-8859-1" FILE="c:\CFusionMX\wwwroot\errorsandexceptions\incentive.jpg" OUTPUT="#chartvar#"> <!--- displays the image in the browser using <img> tag ---> <img src="/books/1/524/1/html/2/c:\CFusionMX\wwwroot\errorsandexceptions\incentive.jpg" height= 300"> </BODY> </HTML>

Figure 26.11 shows the output of the preceding template.

click to expand
Figure 26.11: Output of the Chartfile.cfm template.