Generating Graphs

We'll start with an overview of the charting features included in ColdFusion. ColdFusion comes with a series of tags that will allow you to:

• Create many different types of graphs, including pie charts, bar graphs, line graphs, and scatter charts.

• Format your graphs options that control fonts, colors, labels, and more.

• Display the graphs on any ColdFusion page as JPEG images, PNG images, or interactive Flash charts.

• Allow users to drill down on data shown in your charts. For instance, you could have people click the wedges in a pie chart, revealing the data represented in that wedge.

• Combine several different charts, displaying them together on the page. For instance, you might create a scatter chart that shows individual purchases over time, and then add a line chart on top of it that shows users' average spending.

• Save the charts to the server's drive for later use.

Building Simple Charts

Now that you have an idea of what you can do with ColdFusion's charting features, let's get started with some basic examples. Most of the time, you will create charts with just two CFML tags, <cfchart> and <cfchartseries>.

NOTE

In ColdFusion 5, you created graphs with <cfgraph>, whereas you now create them with <cfchart>. If you already use <cfgraph> in your applications, your <cfgraph> code should continue to work in ColdFusion MX or later, although the graphs may look slightly different. In any case, you should start using <cfchart> as soon as possible, since <cfgraph> has been retired and may not work in future versions of the product.

Introducing <cfchart> and <cfchartseries>

To display a chart on a ColdFusion page, you use the <cfchart> tag. This tag controls the height, width, and formatting of your chart, but it doesn't display anything. Within the <cfchart> tag, you use the <cfchartseries> tag, which determines the type of chart (like bar or pie) and the actual data to show on the chart.

NOTE

Actually, you will occasionally want to place multiple <cfchartseries> tags within a <cfchart> tag. See the "Combining Multiple Chart Series" section, later in this chapter.

Table 16.1 shows the most important attributes for the <cfchart> tag, and Table 16.2 shows the most important attributes for <cfchartseries>.

Table 16.1. Basic <cfchart> Tag Syntax

ATTRIBUTE	DESCRIPTION
chartwidth	Optional. The width of the chart, in pixels. The default is 320.
chartheight	Optional. The height of the chart, in pixels. The default is 240.
xaxistitle	Optional. The text to display along the chart's x-axis.
yaxistitle	Optional. The text to display along the chart's y-axis.
url	Optional. The URL of a page to send the user to when various sections of the chart are clicked. You can pass variables in the URL so you know what part of the chart the user clicked. See "Drilling Down from Charts," later in this chapter.
format	Optional. The type of image format in which the chart should be created. The valid choices are flash (the default), jpg, and png.
seriesplacement	Optional. For charts that have more than one data series, you can use this attributecluster, stacked, percent, or defaultto control how the series are combined visually. Use cluster if the data series represent related pieces of information that should be presented next to one another, rather than added together visually. Use stacked or percent if the data series represent values that should be added up to a single whole value for each item you're plotting. See "Combining Multiple Chart Series," later in this chapter.

Table 16.2. Basic <cfchartseries> Syntax

ATTRIBUTE	DESCRIPTION
type	Required. The type of chart to create. Usually, you will set this to either bar, line, area, or pie. Other chart types are cone, curve, cylinder, scatter, step, and pyramid. The ColdFusion documentation includes some nice pictures of these more unusual types of graphs.
query	Optional. The name of a query that contains data to chart. If you don't provide a query attribute, you will need to provide <cfchartdata> tags to tell ColdFusion the data to display in the chart.
valuecolumn	Required if a query is provided. The name of the column that contains the actual value (the number to represent graphically) for each data point on the chart.
itemcolumn	Required if a query is provided. The name of the column that contains labels for each data point on the chart.

NOTE

Because these tags have a large number of attributes (more than 40 in all), we are introducing only the most important attributes in this table. Others are introduced later in this chapter.

NOTE

In this chapter, you will often see the term data point. Data points are the actual pieces of data that are displayed on a chart. If you're creating a pie chart, the data points are the slices of the pie. In a bar chart, the data points are the bars. In a line or scatter chart, the data points are the individual points that have been plotted on the graph.

NOTE

You don't have to have a query object to create a chart. You can also create data points manually using the <cfchartdata> tag. See "Plotting Individual Points with <cfchartdata>," near the end of this chapter.

Creating Your First Chart

Listing 16.1 shows how to use <cfchart> and <cfchartseries> to create a simple bar chart. The resulting chart is shown in Figure 16.1. As you can see, it doesn't take much code to produce a reasonably helpful bar chart. Anyone can glance at this chart and instantly understand which films cost more than the average, and by how much.

Listing 16.1. Chart1.cfmCreating a Simple Bar Chart from Query Data

 <!--- Name:        Chart1.cfm Author:      Nate Weiss & Ben Forta Description: Basic bar chart Created:     01/10/05 ---> <!--- Get information from the database ---> <cfinvoke component="ChartData"           method="GetBudgetData"           returnvariable="ChartQuery"           maxrows="10"> <html> <head> <title>Chart: Film Budgets</title> </head> <body> <h2>Chart: Film Budgets</h2> <!--- This defines the size and appearance of the chart ---> <cfchart chartwidth="750"          chartheight="500"          yaxistitle="Budget">  <!--- within the chart --->  <cfchartseries type="bar"                 query="chartquery"                 valuecolumn="amountbudgeted"                 itemcolumn="movietitle"> </cfchart> </body> </html> 

Figure 16.1. It's easy to create simple charts with <cfchart> and <cfchartdata>.

First, an ordinary <cfinvoke> tag is used to select invoke a CFC method that returns film and budget information from the database. Then a <cfchart> tag is used to establish the size of the chart, and to specify that the word Budget appear along the y-axis (that is, at the bottom of the chart). Finally, within the <cfchart> block, a <cfchartseries> tag is used to create a bar chart. ColdFusion is instructed to chart the information in the ChartQuery record set, plotting the data in the AmountBudgeted column and using the MovieTitle column to provide a label for each piece of information.

NOTE

For this example and the next few listings, we are using maxrows="10" (passed to the <cfquery> tag) to limit the number of films displayed in the chart to ten. This keeps the pictures of the graphs simple while you're learning how to use the charting tags. Just eliminate the maxrows attribute to see all films displayed in the chart.

The charts created here are Macromedia Flash charts, as that is the default chart format generated by ColdFusion. Charts may also be generated as static images (in JPG and PNG formats), although static charts aren't as functional or feature rich.

TIP

Data is presented in the chart in the order in which it's retrieved from the database. To change the order, just sort the query differently using the appropriate SQL ORDER BY clause.

Changing the Chart Type

The first chart we created was a bar chart. It's easy to change your code so that it displays a different kind of graph. Just change the type attribute of the <cfchartseries> tag. Figure 16.2 shows the pie chart created by the Chart2.cfm, the code for which is shown in Listing 16.2. The differences are that type="Pie: is used in the <cfchartseries> tag.

Listing 16.2. Chart2.cfmCreating a Simple Pie Chart from Query Data

 <!--- Name:        Chart2.cfm Author:      Nate Weiss & Ben Forta Description: Basic pie chart Created:     01/10/05 ---> <!--- Get information from the database ---> <cfinvoke component="ChartData"           method="GetBudgetData"           returnvariable="ChartQuery"           maxrows="10"> <html> <head> <title>Chart: Film Budgets</title> </head> <body> <h2>Chart: Film Budgets</h2> <!--- This defines the size and appearance of the chart ---> <cfchart chartwidth="750"          chartheight="500"          yaxistitle="Budget">  <!--- within the chart --->  <cfchartseries type="pie"                 query="chartquery"                 valuecolumn="amountbudgeted"                 itemcolumn="movietitle"> </cfchart> </body> </html> 

Figure 16.2. Changing the chart type is simply a matter of changing the type attribute.

Formatting Your Charts

Now that you understand the basics of how to produce simple charts, let's learn some formatting options to make your charts look better and more closely meet your users' needs. In general, your goal should be to make the charts as easy on the eyes as possibleit helps people concentrate on the data.

Adding Depth with 3D Charts

One of the easiest ways to make a basic chart look more sophisticated is to give it a 3D look. Table 16.3 shows the <cfchart> options available for adding a 3D effect to your charts. Get out the red-and-blue glasses!

Table 16.3. <cfchart> Options for a 3D Effect

ATTRIBUTE	DESCRIPTION
show3d	Whether to show the chart with a 3D effect. The default is No.
xoffset	The amount that the chart should be rotated on the x-axis. In other words, this controls to what extent the chart appears to be viewed from the side. You can use a value anywhere from -1 to 1, but in general you will want to experiment with low, positive numbers (between .01 and .3) for best results. A value of 0 means no 3D effect horizontally. The default is .1.
yoffset	Similarly, the amount that the chart should be turned around its y-axis. This controls the extent the chart seems to be viewed from the top. Again, you will probably want to experiment with low, positive numbers (between .01 and .3) for best results. A value of 0 means no 3D effect vertically. The default is .1.

NOTE

Whether the 3D effect makes a chart easier to read depends on the situation. It tends to look nice in simple creations, especially bar charts with relatively few data points. Once a chart displays many data points, the 3D effect becomes distracting.

NOTE

The xoffset and yoffset attributes have no discernible effect on pie charts. You can make a pie chart display with a 3D appearance using show3d="Yes", but you can't control the offsets.

Listing 16.3 shows how to produce a 3D graph by adding these attributes to the code from Listing 16.1. The xoffset and yoffset have been tweaked to make the bars look like they are being looked at from the top a bit more than from the side. The results are shown in Figure 16.3.

Listing 16.3. Chart3.cfmAdding a 3D Appearance

 <!--- Name:        Chart3.cfm Author:      Nate Weiss & Ben Forta Description: 3D bar chart Created:     01/10/05 ---> <!--- Get information from the database ---> <cfinvoke component="ChartData"           method="GetBudgetData"           returnvariable="ChartQuery"           maxrows="10"> <html> <head> <title>Chart: Film Budgets</title> </head> <body> <h2>Chart: Film Budgets</h2> <!--- This defines the size and appearance of the chart ---> <cfchart chartwidth="750"          chartheight="500"          yaxistitle="Budget"          show3d="yes"          xoffset=".03"          yoffset=".06">  <!--- within the chart --->  <cfchartseries type="bar"                 query="chartquery"                 valuecolumn="amountbudgeted"                 itemcolumn="movietitle"> </cfchart> </body> </html> 

Figure 16.3. Add 3D chart effects to better display chart details.

Controlling Fonts and Colors

ColdFusion provides a number of formatting attributes that you can use to control fonts, colors, and borders. Some of the attributes are applied at the <cfchart> level and others at the <cfchartseries> level, as listed in Table 16.4 and Table 16.5, respectively.

Table 16.4. <cfchart> Formatting Options

ATTRIBUTE	DESCRIPTION
showborder	Whether a border should be drawn around the entire chart. The default is No.
showlegend	Whether to display a legend that shows the meaning of each color used in the graph. This is applicable only to pie charts, or charts that use more than one <cfchartseries> tag. The default is Yes.
backgroundcolor	The background color of the portion of the chart that contains the actual graph (that is, excluding the space set aside for axis labels and legends).
databackgroundcolor	The background color of the space set aside for axis labels and legends (everywhere except the part where the actual graph is shown).
tipbgcolor	The background color for the pop-up tip window that appears when you hover the pointer over a data point.
foregroundcolor	The foreground color to use throughout the chart. This controls the color of all text in the chart, as well as the lines used to draw the x- and y-axes, the lines around each bar or pie slice, and so on.
font	The font to use for text in the chart, such as legends and axis labels. In ColdFusion, you can choose between Arial, Times, and Courier. In addition, you can choose arialunicodeMS, which you should use when using double-byte character sets. The default is Arial.
fontsize	The size of the font, expressed as a number. The default is 11.
fontbold	Whether text is displayed in bold. The default is No.
fontitalic	Whether text is displayed in italics. The default is No.
tipstyle	Optional. Can be set to mouseOver (the default), mouseDown, or off. By default, a hint or tip message will display when users hovers their pointer over a data point in a graph (an example of this is shown in Figure 25.1). The tip message includes the label and value of the data point, as well as the series label, if given (see Table 25.5). If you want the tip to be shown only when the user clicks a data point, you can use tipstyle="mouseDown", but this works only if format="flash". If you don't want any tip to be shown at all, use tipstyle="off".
pieslicestyle	Relevant only for pie charts. If sliced (the default) is used, the pie is shown with its slices separated by white space (this effect is sometimes called exploded). If solid is used, the pie is shown with its slices together in a circle, the way you normally think of a pie chart. Unfortunately, there is no way to explode just one slice at a time, which is a common way to present pie charts. In general, you will probably want to use pieslicestyle="solid".

Table 16.5. <cfchartseries> Formatting Options

ATTRIBUTE	DESCRIPTION
seriescolor	A color to use for the main element of the data series.
serieslabel	A label or title for the data series.
paintstyle	A style to use when filling in solid areas on the chart for this series. The default is plain, which uses solid colors. You can also use raise, which gives each area a raised, button-like appearance; shade, which shades each area with a gradient fill; or light, which is a lighter version of shade.
colorlist	Relevant for pie charts only. A comma-separated list of colors to use for the slices of the pie. The first slice will have the first color in the list, the second slice will have the second color, and so on.
markerstyle	Relevant only for line, curve, and scatter charts. The look of the marker that appears at each data point. Can be set to rectangle (the default), triangle, diamond, circle, letter, mcross, snow, or rcross.

NOTE

All of the attributes that control color can accept Web-style hexadecimal color values, such as FFFFFF for white or 0000FF for blue. In addition, any of the following named colors can be used: Aqua, Black, Blue, Fuchsia, Gray, Green, Lime, Maroon, Navy, Olive, Purple, Red, Silver, Teal, White, and Yellow.

Listing 16.4 and Figure 16.4 (below) show how some of these formatting attributes can be combined to improve the appearance of the bar charts you have seen so far.

Figure 16.4. The ColdFusion charting tags provide extensive control over chart formatting.

Controlling Grid Lines and Axis Labels

One of the most important aspects of nearly any chart are the numbers and labels that surround the actual graphic on the x- and y-axes. The graphic itself is what lends the chart its ability to convey a message visually, but it's the numbers surrounding the graphic that give it a context. ColdFusion provides you with a number of options for controlling the scale of each axis (that is, the distance between the highest and lowest values that could be plotted on the chart), and for controlling how many different numbers are actually displayed along the axes.

Table 16.6 shows the <cfchart> attributes related to grid lines and axis labels.

Table 16.6. <cfchart> Options for Grid Lines and Labels

ATTRIBUTE	DESCRIPTION
scalefrom	The lowest number to show on the y-axis. For instance, if you want one of the budget chart examples shown previously to start at $20,000 instead of $0, you can do so with scalefrom="20000".
scaleto	The highest number to show on the y-axis. If the highest budget shown in the budget chart examples is $750,000, providing scaleto="1000000" will cause the scale to go all the way up to 1 million, even though there aren't any data points that go up that high. The result is extra "empty space" above the highest value, giving the viewer the sense that the values plotted in the chart could have been higher than they actually are.
gridlines	The number of grid lines to show for the data axis (generally the y-axis). This also affects the number of labeled tick marks along the axis. If you don't provide a value, ColdFusion attempts to use a sensible default value based on the size of the graph. For instance, in Figure 25.2 there are ten grid lines and tick marks (one for 0, one for 83,300, and so on), which seems about right.
showygridlines	Whether to display grid lines for the y-axis. On most charts, grid lines make it easier to grasp the value of each piece of data. These grid lines are shown in Figure 25.1 (the horizontal lines) and Figure 25.2 (the vertical lines). The default is Yes.
showxgridlines	Whether to display grid lines for the x-axis. The default is No.
sortxaxis	Sorts the data in the x-axis (that is, the labels) alphabetically. In general, I recommend that you use order by to reorder the records within a normal <cfquery> tag, before your code gets to the <cfchart> tag; that approach will be much more flexible (see the "Sorting the Data First" section earlier in this chapter).
labelformat	The format for the labels along the y-axis (in our examples so far, the labels that show the increasing amounts of money). You can set this to number (the default), currency (which on English-language systems adds a dollar sign [$]), percent (which multiplies by 100 and adds a percent sign [%]), or date (appropriate only if the data you're plotting are dates).

NOTE

You can't adjust the scale in such a way that it would obscure or chop off any of the actual data being shown in the chart. If your scalefrom value is higher than the lowest data point on the graph, ColdFusion will use the data point's value instead. For instance, if the lowest budget being plotted in one of the budget chart examples is $34,000 and you provide scalefrom="50000", ColdFusion will start the scale at $34,000. The inverse is also true; if you provide a scaleto value that is lower than the highest data point, that point's value will be used instead.

Listing 16.4 shows how formatting, axis, and grid line options can be added to a chart to give it more appeal, and the axis labels have been formatted as currency. (Figure 16.4). You can't see the colors in this book, but different shades of light blue have been used for the data background and the overall chart background. The text is in a dark navy type, and the bars of the chart themselves have a green gradient. Also note that the number of grid lines (that is, the number of numbered tick marks along the horizontal axis) has been set to 6 with the gridlines attribute. This means that there will be five tick marks (in addition to the first one), evenly distributed across the range.

Listing 16.4. Chart4.cfmAdd Formatting, Grid Line, and Axis Options

 <!--- Name:        Chart4.cfm Author:      Nate Weiss & Ben Forta Description: Extensive chart formatting Created:     01/10/05 ---> <!--- Get information from the database ---> <cfinvoke component="ChartData"           method="GetBudgetData"           returnvariable="ChartQuery"           maxrows="10"> <html> <head> <title>Chart: Film Budgets</title> </head> <body> <h2>Chart: Film Budgets</h2> <!--- This defines the size and appearance of the chart ---> <cfchart chartwidth="750"          chartheight="450"          yaxistitle="Budget"          <!--- 3D appearance --->          show3d="yes"          xoffset=".04"          yoffset=".04"          <!--- Fonts and colors --->          showborder="yes"          foregroundcolor="003366"          backgroundcolor="99dddd"          databackgroundcolor="66bbbb"          tipbgcolor="ffff99"          fontsize="11"          fontbold="yes"          fontitalic="yes"          <!--- gridlines and axis labels --->          scalefrom="0"          scaleto="1500000"          gridlines="6"          showygridlines="yes"          labelformat="currency">  <!--- within the chart --->  <cfchartseries type="bar"                 seriescolor="green"                 serieslabel="Budget Details:"                 query="chartquery"                 valuecolumn="amountbudgeted"                 itemcolumn="movietitle"                 paintstyle="light"> </cfchart> </body> </html> 

NOTE

When providing hexadecimal color values, the traditional number sign (#) is optional. If you provide it, though, you must escape the # by doubling it, so ColdFusion doesn't think you're trying to reference a variable. In other words, you could provide backgroundcolor="99DDDD" or backgroundcolor="##99DDDD" as you prefer, but not backgroundcolor= "#99DDDD".

Using Multiple Data Series

Now that you've been introduced to the basic principles involved in creating and formatting charts, we'd like to explain some of the more advanced aspects of ColdFusion charting support. In the next section, you will learn how to combine several chart types into a single graph. Then you will learn how to create charts that users can click, so they can drill down on information presented in the graph.

Combining Multiple Chart Series

So far, all the charts you've seen have contained only one <cfchartseries> tag. This makes sense, considering that the charts have presented only one set of information at a time. But it's also possible to create charts that represent more than one set of information, simply by adding additional <cfchartseries> tags within the <cfchart> block. The additional <cfchartseries> tags can each display different columns from the same query, or they can display information from different queries or data sources altogether.

The bar chart examples so far all show the budget for each film. It might be helpful to show not only the budget for each film but also the actual expenses to date, so that a glance at the chart will reveal which films are over budget and by how much.

Figure 16.5 shows just such a chart. There are now two bars for each film, clustered in pairs. One bar shows the budget for the film and the other shows the actual expenses for the film to date, as recorded in the Expenses table. Listing 16.5 shows the code used to produce this chart.

Listing 16.5. Chart5.cfmPlotting Two Related Sets of Data

 <!--- Name:        Chart5.cfm Author:      Nate Weiss & Ben Forta Description: Using multiple data series Created:     01/10/05 ---> <!--- Get information from the database ---> <cfinvoke component="ChartData"           method="GetExpenses"           returnvariable="ChartQuery"           maxrows="5"> <html> <head> <title>Chart: Film Budgets</title> </head> <body> <h2>Chart: Film Budgets</h2> <!--- This defines the size and appearance of the chart ---> <cfchart chartwidth="750"          chartheight="450"          yaxistitle="Budget"          seriesplacement="cluster"           <!--- 3D appearance --->          show3d="yes"          xoffset=".01"          yoffset=".03"          <!--- Fonts and colors --->          showborder="yes"          databackgroundcolor="dddddd"          fontbold="yes"          fontitalic="yes"          <!--- gridlines and axis labels --->          scaleto="800000"          gridlines="5"          showxgridlines="yes"          showygridlines="no"          labelformat="currency">  <!--- Budget chart --->  <cfchartseries type="horizontalbar"                 seriescolor="99ff99"                 serieslabel="Amount Budgeted:"                 query="chartquery"                 valuecolumn="amountbudgeted"                 itemcolumn="movietitle">  <!--- Expenses chart --->  <cfchartseries type="horizontalbar"                 seriescolor="ff4444"                 serieslabel="Actual Expenses:"                 query="chartquery"                 valuecolumn="expensetotal"                 itemcolumn="movietitle"                 paintstyle="light"> </cfchart> </body> </html> 

Figure 16.5. Multiple data series can be used in a single chart.

Nearly any time you have multiple columns of information in the same query, you can display them using code similar to that used in this listing. The unspoken assumption is that the data in the first row of the AmountBudgeted and ExpenseTotal columns are related to the same real-world item. In this case, that real-world item is the first film.

Combining Series of Different Types

You're free to use different type values (line, bar, area, scatter, and so on) for each <cfchartseries> tag in the same chart. For instance, you might want to modify Listing 16.5 so that one series uses area and the other line. Line graphs are generally used to represent a single concept that changes over time, rather than blocks of individual data like film budgets, but in this particular case the result is rather effective. You're invited to experiment with different combinations of bar charts to see the various possibilities for yourself.

NOTE

You can't combine pie charts with other types of charts. Any <cfchartseries> tags that try to mix pie charts with other types will be ignored.

You can also experiment with the seriesplacement attribute to tell ColdFusion to change the way your chart series are combined. For instance, you can use seriesplacement="stacked" to have the bars shown stacked (as seen in Figure 16.6).

Figure 16.6. Multiple data series can be used in a single chart.

Drilling Down from Charts

The <cfchart> tag supports a URL attribute that you can use to create clickable charts, where the user can click the various data points in the chart to link to a different page. Of course, the page you bring users to when they click the chart is up to you. Generally, the idea is to allow users to zoom in or drill down on the data point they clicked.

For instance, if a chart displays information about film expenses, as in the examples above, then clicking one of the expense bars might display an HTML table that lists the actual expense records. Or it might bring up a second chart, this one a pie chart that shows the distribution of the individual expenses for that particular film. In either case, your clickable chart can be thought of as a navigation element, not unlike a toolbar or a set of HTML links. It's a way for users to explore your data visually.

Creating a Clickable Chart

To create a clickable chart, simply add a URL attribute to the <cfchart> tag. When the user clicks one of the data points in the chart (the slices of a pie chart, the bars in a bar chart, the points in a line graph, and so on), they will be sent to the URL you specify. So, if you want the browser to navigate to a ColdFusion page called FilmExpenseDetail.cfm when a chart is clicked, you would use url="FilmExpenseDetail.cfm". You can use any type of relative or absolute URL that is acceptable to use in a normal HTML link.

For the detail page to be dynamic, however, it will need to know which data point the user clicked. To make this possible, ColdFusion lets you pass the actual data that the user is clicking as URL variables. To do so, include any of the special values shown in Table 16.7 in the url attribute. ColdFusion will create a dynamic URL for each data point by replacing these special values with the actual data for that data point.

Table 16.7. Special Values for Passing in <cfchart> URLs

VARIABLE	DESCRIPTION
$value$	The value of the selected row (that is, the value in the valuecolumn attribute of the <cfchartseries> tag for the data point that was clicked). This is typically the value that you're most interested in passing in the URL.
$itemlabel$	The label of the selected row (that is, the value in the itemcolumn for the data point that was clicked).
$serieslabel$	The series label (that is, the value of the serieslabel attribute of the <cfchartseries> tag). It's usually only necessary to include this value in the URL if you have multiple <cfchartseries> tags in your chart; this value becomes the way that the target page knows which series the user clicked.

For instance, consider the following <cfchartseries> tag:

 <cfchartseries type="pie"                query="ChartQuery"                valuecolumn="AmountBudgeted"                itemcolumn="MovieTitle"                url="FilmExpenseDetail.cfm?MovieTitle=$itemlabel$"> 

When the user clicks the slices in this pie chart, the title of the film they clicked on will be passed to the FilmExpenseDetail.cfm page as a URL parameter named MovieTitle. Within FilmExpenseDetail. cfm, the value will be available as URL.MovieTitle, which can be used just like any other variable in the URL scope.

Listing 16.6 shows how the URL attribute can be used to create a clickable chart. This listing creates a pie chart that breaks down the overall budget for Orange Whip Studios by film. When users click a slice of the pie, they are presented with the detail page shown in Figure 16.7. You'll see the code for the detail page in a moment.

Listing 16.6. Chart6.cfmCreating Chart with Drill-Down Functionality

 <!--- Name:        Chart6.cfm Author:      Nate Weiss & Ben Forta Description: Display a pie chart with drill-down support Created:     01/10/05 ---> <!--- Get information from the database ---> <cfinvoke component="ChartData"           method="GetExpenses"           returnvariable="ChartQuery"           maxrows="10"> <html> <head> <title>Chart: Film Budgets</title> </head> <body> <h2>Chart: Film Budgets</h2> <!--- This defines the size and appearance of the chart ---> <cfchart chartwidth="550"          chartheight="300"          pieslicestyle="solid"          show3d="yes"          yoffset=".9"          url="FilmExpenseDetail.cfm?MovieTitle=$ITEMLABEL$">   <!--- Within the chart --->   <cfchartseries type="pie"                  query="chartquery"                  valuecolumn="amountbudgeted"                  itemcolumn="movietitle"> </cfchart> </body> </html> 

Figure 16.7. Graph details can be displayed using chart drill-down functions.

Creating the Detail Page

Creating the detail page shown in Figure 16.7 is relatively straightforward. Just use the URL parameters passed by the URL attribute of the <cfchart> in Listing 16.6 to query the database for the appropriate Expense records. The records can then be displayed using normal <cfoutput> and HTML table tags.

There is one bit of unpleasantness to deal with, though. Unfortunately, <cfchart> doesn't provide a straightforward way to pass a unique identifier in URLs generated by <cfchart>. The only things you can pass are the actual label and value of the data point displayed on the graph (with the special $ITEMLABEL$ and $VALUE$ values, respectively).

So, for the example at hand (see Listing 16.7), the only pieces of information that can be passed to the FilmExpenseDetail.cfm page are the film's title and budget, since those are the only values that the chart is aware of. Ordinarily, it would be far preferable to pass the FilmID in the URL, thereby eliminating any problems that would come up if there were two films with the same title. Since this isn't currently possible in ColdFusion, the film will have to be identified by its title (and budget) alone.

NOTE

Keep this limitation in mind when creating drill-down applications with <cfchart>. If a data point can't be safely and uniquely identified by the combination of the label and value displayed in the graph, you probably won't be able to implement drill-down.

Listing 16.7. FilmExpenseDetail.cfmDetail Page Displayed on Drill-Down

 <!--- Name:        FilmExpenseDetail.cfm Author:      Nate Weiss & Ben Forta Description: Movie drill-down Created:     01/10/05 ---> <!--- These URL parameters will be passed by the chart ---> <cfparam name="URL.MovieTitle" type="string"> <!--- Get information from the database ---> <cfinvoke component="ChartData"           method="GetFilmID"           returnvariable="FilmID"           movietitle="#URL.MovieTitle#"> <!--- Show an error message if we could not determine the FilmID ---> <cfif FilmID IS -1>   <cfthrow message="Could not retrieve film information."            detail="Unknown movie title provided."> </cfif> <!--- Now that we know the FilmID, we can select the ---> <!--- corresponding expense records from the database ---> <cfinvoke component="ChartData"           method="GetExpenseDetails"           returnvariable="ExpenseQuery"           film> <html> <head> <title>Expense Detail</title> </head> <body> <cfoutput>  <!--- page heading --->  <h3>#URL.MovieTitle#</h3>  <!--- html table for expense display --->  <table border="1" width="500">   <tr>    <th width="100">Date</th>    <th width="100">Amount</th>    <th width="300">Description</th>   </tr>   <!--- for each expense in the query... --->   <cfloop query="expensequery">    <tr>     <td>#LSDateFormat(ExpenseDate)#</td>     <td>#LSCurrencyFormat(ExpenseAmount)#</td>     <td>#Description#</td>    </tr>   </cfloop>  </table> </cfoutput> </body> </html> 

The purpose of the method invocation is to determine the FilmID that corresponds to the Movie Title parameter passed to the page (from the chart in Listing 16.6). As a precautionary measure, the Budget parameter is also included in the query criteria. This means that if two films happen to have the same title, they can still be correctly identified as long as their budgets are different. For the rest of the page, FilmID holds the ID number for the film and can be used to retrieve any related information from the database.

NOTE

If no films are retrieved from the database (or if more than one is retrieved), an error message is displayed with the <cfthrow> tag.

Drilling Down to another Chart

You may want to drill down to a different chart that shows a different view or subset of the data, rather just drilling down to a simple HTML page. The second chart page, in turn, could drill down to another page, and so on. You could use any of the drill-down techniques discussed in this section to put together such a multilayered data-navigation interface.

Additional Charting Topics

The remainder of this chapter introduces various topics related to ColdFusion charting features.

Plotting Individual Points with <cfchartdata>

The most common way to provide the actual data to a <cfchartseries> tag is to specify a QUERY attribute, then tell ColdFusion which columns of the query to look in by specifying itemcolumn and valuecolumn attributes. All of the examples you've seen so far in this chapter have supplied their data in this way.

It's also possible to omit the query, itemcolumn, and valuecolumn attributes and instead plot the data points individually using the <cfchartdata> tag, nested within your <cfchartseries>. The <cfchartdata> approach can come in handy if you want to permanently hard-code certain data points onto your charts, if you need to format your data in a special way, or if you come across a situation where you can't extract the desired data from a query in a completely straightforward manner.

Table 16.8 shows the syntax for the <cfchartdata> tag.

Table 16.8. <cfchartdata> Syntax

ATTRIBUTE	DESCRIPTION
item	The item associated with the data point you're plotting, such as a film title, a category of purchases, or a period of timein other words, the information you would normally supply to the itemcolumn attribute of the <cfchartseries> tag.
value	The value of the data point (a number). This is what you would normally supply to the valuecolumn attribute of <cfchartseries>.

For instance, if you have a query called ChartQuery with two columns, ExpenseDate and ExpenseAmount, and you wanted to make sure the date was formatted to your liking when it was displayed on the chart, you could use:

 <cfchartseries type="line">  <cfloop query="ChartQuery">   <cfchartdata item="#DateFormat(ExpenseDate, 'm/d/yy')#"                value="#ExpenseAmount#">  </cfloop> </cfchartseries> 

instead of:

 <cfchartseries type="line"                query="ChartQuery"                valuecolumn="ExpenseAmount"                itemcolumn="ExpenseDate"> 

This technique is also useful when creating charts based on data that are not query based. Using <cfchartdata> you can pass any data to a chart.

Using Charts with Flash Remoting

It's possible to use the name attribute to capture the binary content of a chart and then make it available to the Macromedia Flash Player via Flash Remoting. This capability allows you to create a Flash movie that displays dynamically generated charts on the fly, perhaps as a part of a sophisticated data-entry or reporting interface, all without reloading the page to display an updated or changed chart. This topic is beyond the scope of this book, but you can consult Chapter 26, "Integrating with Macromedia Flash," for general information about connecting ColdFusion to Flash via Flash Remoting.

Controlling the Chart Cache

ColdFusion automatically caches charts for later use. Conceptually, the chart cache is the charting equivalent of the query-caching feature you will learn about in Chapter 25, "Improving Performance." Its purpose is to improve performance by automatically reusing the results of a <cfchart> tag if all of its data and attributes are the same, rather than having to re-render each chart for every page request.

The Charting page of the ColdFusion Administrator contains a number of options that you can use to tweak the chart cache's behavior:

• Cache type. You can set this to Disk Cache (the default value) or Memory Cache. The Memory Cache setting will perform better under a high load, but it will require more of the server's memory to do so. The Disk Cache setting may not perform quite as quickly, but it will not have much of an impact on the server's RAM. We recommend leaving this value alone unless you're specifically experiencing performance problems with <cfchart> under a heavy load.

• Maximum number of images in cache. You can increase this number to allow ColdFusion to store more charts in its cache, thereby improving performance if your application is serving up a number of different charts. If you're using the Memory Cache option, keep in mind that this will cause even more of the server's memory to be used for chart caching.

• Maximum number of chart requests. This is the maximum number of <cfchart> tags that you want ColdFusion to be willing to process at the same time. Under a high load, a higher number here may improve responsiveness for individual page requests, but it will put more strain on your server.

• Disk cache location. If you're using the Disk Cache option, you may want to adjust this value. It's the location in which ColdFusion stores charts for later reuse.

For more information about the ColdFusion Administrator, see Chapter 29, "ColdFusion Server Configuration."