Creating Charts with the Google Chart API

Introduction

I've always wondered how the phrase "A picture is worth a thousand words" came about. I like to think that it was coined by some mid-level manager
viewing a sales figures report that consisted of metrics from the past 1,000 days. After scanning this long list of numbers, he found, at the
bottom of the page, a line chart that summarized the numbers, and uttered that now well-known adage. Charts and graphs provide a succinct synopsis of
large amounts of data. With charts a person can quickly spot trends, compare different resultsets, or recognize patterns.

This article looks at how to use the Google Chart API to create charts. The Google Chart API is a
free service from Google that enables web developers to generate chart images on the fly by creating an <img> element with a
src attribute that points to a URL that includes the chart data, labels, and other information in the querystring. For instance, the
chart on the right is available at the URL http://chart.apis.google.com/chart?cht=p&chs=225x150&chd=t:100,30,70,25&chl=Q1|Q2|Q3|Q4&chtt=2008%20Sales%20By%20Quarter.
Read on to learn how to use the Google Chart API in your ASP.NET website!

An Overview of the Google Chart API

The Google Chart API allows developers to generate various types of charts on the fly. The Google Chart API is hosted as a URL on Google's web servers
and when a properly formatted URL is sent, an image of a chart is returned. The chart's details - the colors, title, axes, data points, dimensions, and
so forth - are specified via the URL's querystring. The resulting image can be displayed using an <img> element or may be stored
on your web server's file system or in a database for later use, if needed. Best of all the Google Chart API is free to use and does not require any
sort of account or sign up process!

The base URL for the Google Chart API is: http://chart.apis.google.com/chart?. The parameters that define the chart's layout
follow after the ? character. There are a variety of parameters that you can specify through the querystring. The only required parameters
are the chart size (chs), the chart data (chd), and the chart type (cht). The following table summarizes
some of the more germane parameters:

Google Chart API Options

Parameter

Description

cht

The chart type. Google offers around a dozen different chart types, including line charts, bar charts, pie charts, and others.

chs

The chart size. This value is expressed as chs=WIDTHxHEIGHT, where WIDTH and HEIGHT are the number of pixels
wide and tall to draw the chart. E.g., chs=250x100. The maximum height and width is 1,000 pixels, and the product of the height and width
cannot exceed 300,000 pixels.

The chart data. When using this parameter you must specify the data format.
The Google Chart API allows for different data encodings. The simplest to use is text encoding and is denoted by the letter t. Following the encoding place a colon (:)
and then a comma-delimited list of data point values.

The default text encoding requires that the data points be floating point values between zero (0.0) and one hundred (100.0). To correctly scale the data convert
each data point into a percentage of the largest data point. Ergo, the largest value will have a value of 100.0, whereas the others will be expressed in
terms of a percentage of the largest - 50.0 for one that's half as large as the largest, 25.0 for one that's 25% of the largest, and so forth.
To render a chart with data points of 10, 20, and 8, you'd send: chd=t:50,100,40. Note the
t:, which indicates that the data is formatted using text encoding.

You can alternatively use the text encoding method with data scaling, which allows data points of any positive or negative floating point number.
With this approach you must specify a scaling parameter (chds). The examples in this article use the default text encoding, limiting
all data point values between zero and one hundred.

Using the above information you can tinker with creating your own charts using the Google Chart API. For instance, the following URL generates a
line chart that's 300x200 pixels in dimension with data points 43, 23, 12, 62, 34, and 39, and a title of, "Monthly Traffic":
http://chart.apis.google.com/chart?cht=lc&chs=300x200&chd=t:69.3,37.1,19.4,100.0,54.8,62.9&chtt=Monthly%20Traffic.
Note that the data points sent in the chd parameter are not the literal data points - 43, 23, 12, 62, 34, and 39 - but are instead
the percentages of the largest data point. In other words, the literal data point x is turned into the percentage data point
by doing (x / maxDataPoint) * 100. For example, literal data point 43 turns into 43/62 * 100 = 69.3.

Generating a Google Chart From Database Data In An ASP.NET Page

With a little bit of work it is possible to generate a chart based on database data using the Google Chart API. We simply need to construct the
appropriate querystring based on the data to be plotted, the type of chart, and the X and Y axis labels (if any). The remainder of this article
looks at building the querystring in an ASP.NET web page for a database query that returns sales amount per month for the Northwind Traders company.
You can download the complete working code at the end of this article.

The first step is to create the query that returns the data to plot. My demo uses the Northwind database, which maintains information on products, customers,
orders, and so on. For this demo I decided to chart the gross sales per month for a given year. The following query returns this information for a
specified year:

SELECT MONTH(o.OrderDate) AS MonthVal, SUM(od.UnitPrice * od.Quantity) AS Total
FROM Orders AS o
INNER JOIN [Order Details] AS od ON
od.OrderID = o.OrderID
WHERE (YEAR(o.OrderDate) = @Year)
GROUP BY MONTH(o.OrderDate)
ORDER BY MonthVal

The Northwind database has sales for the years 1996, 1997, and 1998. If you pass in one of these values for the @Year parameter you'll
get results like:

MonthVal

Total

1

66692.8000

2

41207.5500

3

39979.9000

...

The demo specifies the above query in a SqlDataSource control and displays the results on the page in a GridView control. The GridView has been
customized to display the results of the Total column formatted as a currency and to format the MonthVal column values
as the three-letter month name abbreviation. This MonthVal formatting is handled by a helper function in the code-behind class,
DisplayMonthName, which takes in the month as an integer input (1, 2, ..., 12) and returns the formatted value ("Jan", "Feb", ..., "Dec").
I also added a DropDownList control to the page for the user to specify the value of the @Year parameter; the DropDownList has the
hard-coded values 1996, 1997, and 1998.

In addition to the SqlDataSource control, GridView, and DropDownList for the year, the page includes an Image Web control for displaying the chart.
The Image control's ImageUrl property is programmatically set to the appropriate URL in the code-behind class. This is handled in the
DisplayChart method. This method starts by constructing the base portion of the URL - the chart type (cht), chart size (chs),
and chart title. The chart type and size are dictated by values selected by the user via two DropDownList controls.

Protected Sub DisplayChart()
'Build up the chart URL
Dim chartUrl As New StringBuilder("http://chart.apis.google.com/chart?")

'Add the title
chartUrl.AppendFormat("&chtt={0}", Server.UrlEncode("Sales for " & ddlYear.SelectedValue))

...

Next, the SqlDataSource control's Select method is called, returning the results. These results are enumerated and their
values are collected into a list of Decimal values (literalDataPointsList) and the labels for each data point - the
three-letter month abbreviation - are recorded in a list of string (xAxisLabels). Once the literal data point values have been recorded
they are re-expressed as percentages of the largest value and stored in a list of strings called relativeDataPointValues. The values
in this list are then concatenated (delimited by commas) and assigned to the chart data parameter (chd).
(For more information on using the Select method, read
Programmatically Accessing Data using the Data Source Controls.)

...

'Add the data points... First, get the data from the SqlDataSource control
Dim dataPointsView As DataView = CType(dsChartData.Select(DataSourceSelectArguments.Empty), DataView)
Dim xAxisLabels As New List(Of String)
Dim literalDataPointsList As New List(Of Decimal)
Dim maxValue As Decimal = 0

'Read in the data points and store the maximum value
For Each point As DataRowView In dataPointsView
'Remove any cents from the amount
literalDataPointsList.Add(Convert.ToDecimal(point("Total")))

'See if we have a new maximum
If literalDataPointsList(literalDataPointsList.Count - 1) > maxValue Then
maxValue = literalDataPointsList(literalDataPointsList.Count - 1)
End If

'Compute the relative data point values
Dim relativeDataPointValues As New List(Of String)
For Each point As Decimal In literalDataPointsList
Dim relativeValue As Decimal = (point / maxValue * 100)
relativeDataPointValues.Add(relativeValue.ToString("0.00"))
Next

'Add the points to the URL
chartUrl.AppendFormat("&chd=t:{0}", String.Join(",", relativeDataPointValues.ToArray()))

...

Finally, the labels for the X and Y axis are specified. The Y axis extends from 0 to the maximum value returned by the query, whereas the X
axis data points are loaded from the xAxisLabels list. Finally, the Image control's ImageUrl property is assigned the
value of the URL that has been built up.

...

'Add two axes
chartUrl.Append("&chxt=x,y")

'Add the range for the Y axis
chartUrl.AppendFormat("&chxr=1,0,{0}", maxValue.ToString("0"))

'Add the Labels for the X axis
chartUrl.AppendFormat("&chxl=0:|{0}", String.Join("|", xAxisLabels.ToArray()))

'Load the chartUrl "image" in the imgChart Image control
imgChart.ImageUrl = chartUrl.ToString()
End Sub

The net result can be seen in the screen shots below. The first screen shot shows the gross sales for 1996 in a line chart; the second one shows the
sales for 1997 in a bar chart.

Conclusion

The Google Chart API offers a quick and easy way to generate a number of different chart types on the fly for your website. To generate a chart simply
request the Google Chart API URL passing along details about the chart - its dimensions, data points, colors, labels, and so on - via the querystring.
The Chart API returns an image which you can then display in an <img> element. With a little bit of work it's possible to generate
this charting URL from an ASP.NET web page so as to display a chart based on database data. In this article we saw how to manually construct the URL;
a future article will look at a custom control that supports declarative databinding and other nifty features.