Free - Adventures in DMXzone Google Charting

A couple of weeks ago, I wrote an article on the new DMXzone Google Charts extension that makes it quick and easy to add Google Charts to your web pages.  My first hour playing with that extension gave me one huge question:  what about data driven web sites?  Does this work when the data is fed from a database table or query?

Turns out I wasn't the only one with that question as I was quickly shown the DMX Zone forum where this question was fairly prevalent.  In my first article, I touched on a way to hand code this functionality into a page with the extension.  This article is completely devoted to charting with a database and the DMX Zone Google Charting Extension.  This week covers the Line Chart and uses ASP as the example.  PHP and Cold Fusion examples will provided later in the series.

The first thing you need to address with this workflow is the database.  For this example, I am using a simple Access database because it's quick to create and simple to show, but you can use MSSQL, MySql, or whatever database you are most familiar with.  I have included my example database with this article so it can be provided with the sample pages as a download to accompany this article.

My database consists of one table called Sales with the following fields: ID, which is an autonumber and StartingValue, MaxValue, MondaySales, TuesdaySales, WednesdaySales, ThursdaySales, FridaySales and TotalSales. Which are all Number fields.  I have entered values into a couple of rows to give us some data to work with in all but the TotalSales field.  That field is calculated from the following Access Query:

SELECT Sales.MondaySales, Sales.TuesdaySales, Sales.WednesdaySales, Sales.ThursdaySales, Sales.FridaySales, [MondaySales]+[TuesdaySales]+[WednesdaySales]+[ThursdaySales]+[FridaySales] AS TotalSales
FROM Sales;

This simple query simply says to select the fields for each day of the week and finally to select the total of each of those fields as TotalSales, which gives us the sum of the other fields' values to fill in the value of the last field.

Now we flip over to Dreamweaver to create a recordset that will allow us to easily use this data in our page. 

It is assumed that the reader has already created a Dreamweaver site definition for this exercise and specified the Testing Server as ASP/VB with a localhost testing environment.  If you do not know how to do this, refer to this article for step by step instructions. 

You can use the database included with this article or create one of your own.  The table in place was created for the line chart example so we will do that one first.   Put the database on your computer and create a local connection to it.  Test the connection and get ready to go.  For the sake of consistency, my connection is a local DSN for ease of use and it is called conndmxzone.asp with the following code:

<%
' FileName="Connection_odbc_conn_dsn.htm"
' Type="ADO"
' DesigntimeType="ADO"
' HTTP="false"
' Catalog=""
' Schema=""
Dim MM_conndmxzone_STRING
MM_conndmxzone_STRING = "dsn=dsnSales;uid=;pwd=;"
%>

You can either borrow my code or create your own with the name you prefer to use.  The important part is that you can create a working connection and that it tests successfully.  With that done, we are ready to roll on the first example page.

The first example is ASP/VBScript so I create an ASP/VB page and save it as chart1.asp.  We are going to create a line chart using the data from the data table.  Step 1 is to create the recordset.  I'll step through this quickly because most readers do this every day of the week.  On the Data Bindings tab, click the + and select Recordset.  In the dialogue that opens, you can use the simple recordset interface.  Select the connection and then the table in the dropdown boxes that should have only the one selection and select the fields desired with no filter.   For the Google Charts to function correctly, we need to include the StartingValue and MaxValue and whatever Sales we wish to include to show the trend.  I have included Monday, Tuesday and Wednesday Sales only.    Other charts will require the total, but the line chart is showing trend so the total isn't necessary this time.  The recordset dialogue, once filled in, should resemble Figure 1 below:

Figure1:  The recordset and SQL test for the first example in ASP

The code for the recordset looks like this:

<%
Dim rssales
Dim rssales_cmd
Dim rssales_numRows
Set rssales_cmd = Server.CreateObject ("ADODB.Command")
rssales_cmd.ActiveConnection = MM_conndmxzone_STRING
rssales_cmd.CommandText = "SELECT StartingValue, MaxValue, MondaySales, TuesdaySales, WednesdaySales FROM Sales"
rssales_cmd.Prepared = true
Set rssales = rssales_cmd.Execute
rssales_numRows = 0
%>

At this point, I like to save the page, just in case I make a mistake putting in the chart.  If I do that, I can easily revert back to what I saved by simply closing my test page without saving and then reopening it again automatically at this point.

Now we're going to insert the Google Chart to get the code on the page.  This part of the exercise can be done in Design View and the basic chart settings can be added.  After that point, because the extension currently does not support dynamic data, we will be changing to Code View and working with the code inserted by the extension to get it to work like we need it to.

Click in the page where you want the chart inserted and then select the DMXZone dropdown in the Insert Bar (or the item on the Menu if you are using Menu view of the Insert bar).   Select line chart and the default line chart used by the extension now appears in your document.  Click on the chart and look through the options in the Property Inspector.  We used either the Graphical or the Data editor to enter the data points for the chart in the past, but we're going to ignore those options this time.  Flip to Code View and look with me for a moment at the default line chart code produced by the Google Charts extension.

<img width="250" height="100" src="http://chart.apis.google.com/chart?cht=lc&chd=t:10,30,20,40&chds=0,101&chs=250x100&chco=FF6600" />

While this may look confusing at first glance, after you study the code, you realize that it's just a string of parameters attached to an image tag.  First, of course, is the width and height of the chart and you could have modified this in the Property Inspector or you can do it here to give you the size line chart that you want.  After the height and width is the URL of Google Charts which is necessary to create the dynamic chart.  To dynamically update, we won't be exporting the chart to a gif as you may have done with the static chart.  The values are going to change as different data is loaded by our recordset so it will be necessary to maintain the live connection to Google to dynamically create differing charts.  Let's break down the parameters a little at a time.

In a dynamic URL, the important part (the part we can modify) is the part after the question (?) mark so it is to that part that we give our attention.  The first parameter is "cht" for chart and it is equal to "lc".  It isn't rocket science to figure out that "lc" stands for Line Chart and it is this first parameter that tells the application what type chart it is going to create here:  a line chart.

Parameters are separated by an "&" sign so that is next and the next parameter is "chd" with a value of t: for text string and the values in numbers separated by commas.  This is the format for a static data string that gives values for our various sales figures by day.  After the next & is the chds (chart data space) and it goes from 0 (StartingValue) to 101 (MaxValue).  The next parameter is "chs" or chart size and takes the value of grid coordinates for width and height.  The last parameter is "chco" which is the Chart Color.  Our Value is "FF6600" which is orange.

All very well and good when you're using static values with the extension.  You would now have a nice looking line chart in the browser when you tested your page.  But the whole point is that we want our chart to reflect the datas in the database table.  At this point in time, since the Google Charts Extension does not support dynamic input, we have to make some changes by hand in the code.  What we have in the Code figure above needs to be this:

<img width="250" height="100" src="http://chart.apis.google.com/chart?cht=lc&amp;chd=t:<%=dvalues%>&amp;chds=<%=dstart%>,<%=dmax%>&amp;chs=250x100&amp;chco=FF6600" />

Remember that the code in our page is ASP so our notations reflect that programming language.  We can (and will in later installments) do the same thing in PHP or Cold Fusion by using the variable syntax required by those programming languages.  Let's dissect the second code block above:

The two blocks look the same up to the ? mark, as well they should.  The parameters begin right after the question mark and it is here that we will input the code that will change the chart depending on the data being pulled from the recordset.  The chart type is the same; it's still a line chart.  The first change is the chart data value.  Instead of using "chd=t:10,30,20,40" which were static values for the line values, our code is this:

chd=t:<%=dvalues%> which is telling the image link to use data that is equal to the current value of a variable called dvalues.  This variable value will change depending on what row of data we are telling the page to use in rendering the chart.  The value of chds will also change.  Instead of the never-changing 0 and 101 for starting and max values, we use two more variables, dstart and dmax to pull the current value of those variables into the chart.  The final two parameters, size of the chart and its color are not going to change so we can leave the static values for those in place.  If you wanted to make them dynamic, you could add three columns to the database table, one for width, one for height, which together would give the size ("chs") of the chart and one for the hexadecimal color of the chart, which is set equal to the value of "chco".

So far this makes pretty good sense, but we have forgotten one important step that is going to make this all work.  We haven't told the application what these things are called "dvalues", "dstart" and "dmax".  We need to initialize and define the values of these variables so the correct database values are pulled on request.  We do this right under the recordset code and before the dynamic chart code.  If we don't do that first, our application will get to the dynamic URL, stumble over the first variable and say "HUH?"

This code takes care of that.  We'll pick it apart in a moment.

<%
dim dstart, dmax, dvalues
dstart = (rssales.Fields.Item("StartingValue").Value)
dmax = (rssales.Fields.Item("MaxValue").Value)
dvalues = (rssales.Fields.Item("MondaySales").Value) & "," & (rssales.Fields.Item("TuesdaySales").Value) & "," & (rssales.Fields.Item("WednesdaySales").Value)
%>

The first line of the definition code starts with the keyword "dim".  This is how all variables are defined in ASP.  "Dim" means "define" and the 3 names of the variables are what we are about to define.  The three lines after that defines each of the preceding variables by setting the variable name equal to a value in the appropriate recordset column.  We set "dstart" equal to the StartingValue column for the current record, we set "dmax" equal to the MaxValue for the record and we set "dvalues" equal to the Monday Sales, the Tuesday Sales and the Wednesday sales which is what we are tracking with our line.  Because every character in ASP has to be accounted for, we place a joining & after the first value and then "," which means "add a comma".  The comma is in quotation marks because we want the comma to appear as it is and not be interpreted by the code.  Then we add another & sign to join the next value which is the Tuesday Sales value from the recordset, followed by another & and a comma (",") a third adjoining & and the last recordset value.  That whole thing is what will give us our comma separated values in the variable "dvalues".

If you have followed this code to the letter, your entire page should look like this:

<%@LANGUAGE="VBSCRIPT" CODEPAGE="65001"%>
<!--#include file="Connections/conndmxzone.asp" -->
<%
Dim rssales
Dim rssales_cmd
Dim rssales_numRows
Set rssales_cmd = Server.CreateObject ("ADODB.Command")
rssales_cmd.ActiveConnection = MM_conndmxzone_STRING
rssales_cmd.CommandText = "SELECT StartingValue, MaxValue, MondaySales, TuesdaySales, WednesdaySales FROM Sales"
rssales_cmd.Prepared = true
Set rssales = rssales_cmd.Execute
rssales_numRows = 0
%>
<%
dim dstart, dmax, dvalues
dstart = (rssales.Fields.Item("StartingValue").Value)
dmax = (rssales.Fields.Item("MaxValue").Value)
dvalues = (rssales.Fields.Item("MondaySales").Value) & "," & (rssales.Fields.Item("TuesdaySales").Value) & "," & (rssales.Fields.Item("WednesdaySales").Value)
%>
<!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.0 Transitional//EN" "http://www.w3.org/TR/xhtml1/DTD/xhtml1-transitional.dtd">
<html xmlns="http://www.w3.org/1999/xhtml">
<head>
<meta http-equiv="Content-Type" content="text/html; charset=utf-8" />
<title>Untitled Document</title>
</head>
<body>
<img width="250" height="100" src="http://chart.apis.google.com/chart?cht=lc&amp;chd=t:<%=dvalues%>&amp;chds=<%=dstart%>,<%=dmax%>&amp;chs=250x100&amp;chco=FF6600" />
</body>
</html>
<%
rssales.Close()
Set rssales = Nothing
%>

Section by section, we connected to the database, we defined a recordset, then we defined the variables we are going to use in the chart.  Then in the body of the page, we put in the image tag that will create the Google Chart.  It connects to Google Charting and creates a line chart using the values we have defined in StartingValue, MaxValue and the three days of Sales that we are tracking in this chart.  Lastly, it closes the recordset and ends the page.

Simple enough?  This is what the completed line chart looks like in the browser using the values from line 1 of the database that I created for this article:

The finished Line Chart using the Code above.

That is the line chart example hopefully in enough detail to allow you to replicate it on your site.  I have included my database and finished page in a zip to accompany this article.  In the next instalment, we will work through some dynamic pie charts and show you some possibilities for using this sort of data in your data driven applications.  Then the last article will focus on bar charts of all shapes and sizes.  Until next time.