- Building Basic Charts
- Dressing Up Your Charts
- Building Dynamic Charts with CFQUERY
- Building Dynamic Applications with Clickable Charts
This chapter is from the book
This chapter is from the book
This chapter is from the book
Building Dynamic Applications with Clickable Charts
In the preceding example, you learned how to show the proportion of national parks in Alaska to the number of parks in the rest of the United States. Assume that you run a Web site dedicated to parks and travel. Some of your visitors will care not a whit for grizzly bears and glaciers. In fact, it's simply not possible to guess the interests of each site user. With dynamic charts, you don't even have to try.
In previous chapters, you learned how to use a form to pass data to queries, whether you're building a simple search interface or updating a record. In this section, you use precisely the same skill to let your site's users specify a graph's query parameters.
You also can let your users explore your data by making your charts clickable. The <cfchart> tag includes the attribute URL, which allows you to specify the page your chart links to, as well as what dynamic data to pass along with the link.
To create a dynamic chart using form data:
Create a new ColdFusion page, title it, and save it.
Build a simple form with a drop-down list populated by a recordset of the distinct national park regions.
You can use the code listed in Figure 13.31 to create your page.
Figure
13.31 This code queries a list of distinct regions and uses the recordset
values in a simple form.Save the page and then preview it in a browser.
You will see a simple form with a drop-down list and Submit Query button (Figure 13.32).
Figure
13.32 This shows a select form control populated dynamically from a
query.Create a new page, title it, and save it. This page will be the action page for the form you created. Next you give the page a dynamic title.
In place of the standard <title> tags, type this code:
<cfoutput><title>US National Parks in
#form.select#</title></cfoutput>At the top of the page, create a new recordset:
<cfquery name="q_ParksByRegion" datasource="exampleapps"> SELECT STATE, COUNT(*) AS state_count FROM tblParks WHERE REGION = '#form.select#' AND STATE IS NOT NULL GROUP BY STATE ORDER BY STATE; </cfquery>
This recordset is very similar to the other GROUP BY recordsets you've built in this chapter, with one exception: You want to gather data only in the region the user selected from the previous page. Let's give the page a dynamic header.
Immediately following the opening Graphing Your Data with CFCHART body> tag, type this code:
<cfoutput><h2>US Parks in #form.select#</h2></cfoutput>
Save the page, view your form page in a browser, and then select a Park region to test your pages. You'll see the name of the region you selected appear in the title and header of the form's action page. All that remains is the build the chart.
In the body of your action page, insert a pie chart that gets its data from the recordset q_ParksByRegion:
<cfchart showborder="yes" show3d="yes" chartwidth="600"
chartheight="400" pieslicestyle="sliced">
<cfchartseries type="pie" query="q_ParksByRegion"
itemcolumn="STATE" valuecolumn="state_count">
</cfchartseries>
</cfchart>You don't need to learn any new tags for this chart; the query is doing all the heavy lifting. The <cfchartseries> tag simply shows a list of the states returned by q_ParksByRegion, and the number of parks within each state. Your code should look like Figure 13.33.
Figure
13.33 Now you have all the code that's needed to build a dynamic
chart.Save the page, browse to the form page, select Pacific Northwest Region from the list of regions, and click Submit Query. Presto! You have a dynamically generated chart of states within the region, along with the number of parks in each state (Figure 13.34).
Figure
13.34 This pie chart is built on the fly from form data.
)
Figure
13.31 )
Figure
13.32 )
Figure
13.33 )
Figure
13.34 TIP
If you need a quick refresher on forms and working with form variables, see Chapter 7, "Inserting Data."
To make your charts clickable:
Open the form action page you created for the previous section.
You'll build upon the code you've already written.
Add the url attribute to the <cfchart> tag:
url="ParksByState.cfm?state= $ITEMLABEL$"
In this case, the $ITEMLABEL$ will be the two digit abbreviation for the state that's clicked within the graph. This example shows a link to a page named ParksByState.cfm. You can use any URL you like; just ensure that the page you create in step 4 has the same name as the page in the URL.
Save your page.
Create a new page and save it with the name of the URL in step 3.
Don't give it a title yet since you're going to make the title dynamic.
Using the query builder or coding by hand, create this query at the top of the page:
<cfquery name="q_GetParks" datasource="exampleapps"> SELECT PARKNAME, CITY, STATE FROM tblParks WHERE STATE = '#url.state#' ORDER BY PARKNAME; </cfquery>
This query looks for the variable state passed in the URL scope from the chart on the previous page. Because you specified the state as the $ITEMLABEL$ value in your graph, the query will show only those parks in the state you've selected. Now the only step that remains is to display the data on the page.
Add this code to the title properties:
<cfoutput>Parks in #url.state#</cfoutput>
Just like the query, our page's title will be dynamically set by the state you've selected.
If you like, you also can put a dynamic header just below the opening <body> tag:
<cfoutput><h2>Parks in #url.state#</h2></cfoutput>
Place a <cfdump> tag in the page's body to show the query results:
<cfdump var="#q_getParks#" label="List
of Parks in Selected State">Your page should look like Figure 13.35.
Figure
13.35 Your page shows a query filtered from data passed by the URL.Save the page, browse to your form page, and test the complete application: First, select Rocky Mountain Region from the drop-down list, and then click Submit Query (Figure 13.36).
Figure
13.36 Selecting a region in the form...
Next, when the Rocky Mountain Region graph appears, click the slice representing South Dakota, abbreviated as SD (Figure 13.37).
Figure
13.37 ...reveals the states in the region. Clicking a
state...
You'll see a table listing all the parks in the state of South Dakota (Figure 13.38).
Figure
13.38 ...reveals the parks in the state.
)
Figure
13.35 )
Figure
13.36 )
Figure
13.37)
Figure
13.38 Tips
The three variables used by the url attribute of the <cfchart> tag are the only standard ColdFusion variables that use the $ sign.
In addition to the $ITEMLABEL$ variable, the URL attribute supports $VALUE$, to pass the value of the selected item, and $SERIESLABEL$, to pass the label of the selected series. To consider how you might use the $SERIESLABEL$ value, return for a moment to the demographic chart in the first half of this chapter. In order to make the chart clickable, you would want to pass the $SERIESLABEL$ along with the $ITEMLABEL$ so the application would know whether your user wanted to drill down to data about the United States (one series) or Western Europe (the other series) (Figure 13.39). Use this format to pass more than one variable with the URL attribute:
url="drilldownpage.cfm?Series=$SERIES
LABEL$&Item=$ITEMLABEL$&Value=$VALUE$"
Figure
13.39 These links show the same year but different regions. Examine the
URLs closely. Don't be afraid to experiment with your graphs as you develop other ColdFusion skills. For example, ColdFusion includes a tag called <cfschedule> that allows you to run your ColdFusion pages at regular intervals. You easily can build an application that stores a value in your database each day (for example, the price of a stock) and graphs the value on demand.
)
Figure
13.39