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.
There are many ways to create charts in an ASP.NET web page. You can use the classes
in the System.Drawing namespace to programmatically generate charts; you can use
the Microsoft Office Web Components (OWC). There are also open-source
charting tools and a plethora of third-party components, as well.Microsoft has even entered the game and introduced Microsoft Chart Controls for the .NET Framework 3.5 SP1.
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. |
| chtt | The chart title. |
| chd | 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.
|
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.
Put this URL in an
<img> tag like in the markup below:
<img
src="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"
/>
|
?cht=lc&chs=300x200&chd=t:69.3,37.1,19.4,100.0,54.8,62.9&chds=0,62&chtt=Monthly%20Traffic&chxt=x,y&chxl=0:|1st|7th|14th|21st|31st&chxr=1,0,62.
And here are the results:
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
|
@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.
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()
|
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.)
...
|
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.
...
|
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.
A Google Chart API Custom Server Control
Over the past week I built such a Web control. The Web control does not provide the full suite of Google Chart API features - it only allows for the creation of line, bar, and pie charts, and it only allows a single data series - but it makes creating and displaying data-driven charts as easy as drag-and-drop and point-and-click. This article shows how to use this free custom server control and highlights some of its more interesting aspects. You can download the compiled server control, its complete source code, and a demo application at the end of this article. Read on to learn more!
An Overview of the Chart Control in the skmControls2 Library
After writing last week's article, in which I wrote 50+ lines of code that showed how to use Google Chart API to display a chart based on database data, I decided there had to be an easier way to show a simple chart. Ideally, a developer could drag a chart control from the Toolbox onto an ASP.NET page, set a few properties to indicate things like the chart type, size, and color, and then specify the chart's data in a number of different ways:
- By binding the chart to a data source control, such as a SqlDataSource or ObjectDataSource control,
- Specifying hard-coded chart data statically in the page's markup, or
- Specifying the chart data programmatically
<img> element with
its src attribute set to the appropriate URL to generate the chart with the given data.
I created a custom server control named Chart that is part of my
skmControls2 library, a collection of custom server controls for ASP.NET
version 2.0
and up. Other controls in the skmControls2 library include an enhanced GridView control,
a data bound Label control, and a TextBox
word and character counter control. You can download the latest version of the skmControls2 library, along with a demo application, at the end of
this article.
The Chart control is used in the following manner: a page developer adds the Chart control to an ASP.NET page. She can then configure appearance-related properties like
ChartType, Height, Width, LineColor, ChartTitle, and others.
Next, the page developer would specify the chart's data as a series of data points. Each data point has two attributes:
Value- the data point valueLabel- the text label associated with the data point
Items collection. The values of the Items collection can be
expressed in three different ways:
- Statically - you can specify the data points through the
declarative markup of the Chart control. For instance, to create a chart
with
data points labeled Q1, Q2, Q3, and Q4 with values 45, 21, 88, and 34,
respectively, you could use the following declarative markup:
<cc1:Chart ID="StaticChart" runat="server">
<cc1:DataPoint Value="45" Label="Q1"></cc1:DataPoint>
<cc1:DataPoint Value="21" Label="Q2"></cc1:DataPoint>
<cc1:DataPoint Value="88" Label="Q3"></cc1:DataPoint>
<cc1:DataPoint Value="34" Label="Q4"></cc1:DataPoint>
</cc1:Chart> - Programmatically - the
Itemscollection is a collection of objects of typeDataPoint. You can programmatically create these objects and add them toItems. Here's a snippet of code that adds the four data points shown above, but does so programmatically rather than statically.Protected Sub Page_Load(ByVal sender As Object, ByVal e As System.EventArgs) Handles Me.Load
If Not Page.IsPostBack Then
DynamicChart.Items.Add(New DataPoint("45", "Q1"))
DynamicChart.Items.Add(New DataPoint("21", "Q2"))
DynamicChart.Items.Add(New DataPoint("88", "Q3"))
DynamicChart.Items.Add(New DataPoint("34", "Q4"))
End If
End Sub - Via Data Binding - you can bind the Chart control to a data
source control and the Chart will treat each record returned by the data
source
control as a data point. To use this technique you must tell the Chart
control what column in the data source control constitutes the data
point value;
you can optionally specify what column constitutes the label. Use the
DataValueFieldandDataLabelFieldproperties to provide this information.
BaseDataBoundControl
class, which defines the base functionality for such controls. Another class in the .NET Framework, DataBoundControl,
fleshes out the BaseDataBoundControl class, implementing some of the essential data binding logic and adding the DataMember property.
Consequently, the Chart control extends the DataBoundControl class.
(This article does not explore the art of creating data bound server
controls. For more information on this topic check out A Crash Course on ASP.NET Control
Development: Building Data-Bound Controls.)
The
DataBoundControl class includes a method named PerformDataBinding that is executed in cases where the control is bound to
a data source control and this data is needed. The PerformDataBinding method is passed an object that implements IEnumerable, which
is the data returned by the data source control's Select() method. Custom server controls that extend DataBoundControl
typically override this method to provide the logic for rendering the
underlying data. The Chart control overrides this method and uses it to
populate
its Items collection, as the following code snippet illustrates:
protected override void PerformDataBinding(System.Collections.IEnumerable data)
|
<img> element with its src
attribute assigned the appropriate URL
to render the chart using the Google Chart API. The precise HTML element
that is rendered by a Web control depends on the value of its
TagKey property. Because we need
the Chart control to render as an <img> element (as opposed to a <span> or
<input>) the TagKey property is overridden to return the appropriate element type:
protected override HtmlTextWriterTag TagKey
|
AddAttributesToRender
method is invoked, which is responsible for adding any attributes
within the HTML element. The Chart control overrides this method so that
it can
translate the data in the Items collection (and the various appearance-related property settings) into the appropriate URL for the
src attribute.
protected override void AddAttributesToRender(HtmlTextWriter writer)
|
src attribute is set to the value returned by the CreateChartUrl
method.
This method, shown below, constructs the complete Google Chart API URL
and querystring based on the control's properties and the data in the Items
collection.
protected virtual string CreateChartUrl()
|
CreateChartUrl method starts by creating a new StringBuilder object and adds the value of the UrlBase property
(the portion of the Google Chart API URL without the querystring values, http://chart.apis.google.com/chart?). It then adds the
chart type parameter (cht), the chart dimensions (chs),
and so on. The values for these parameters are pulled from the
values of the control's properties. A number of other querystring
parameter assignment statements have been omitted from the above snippet
for the sake of
brevity. The most important step is specifying the chart data (chd). The Items collection has a method named
RenderRelativeCHDValues that returns the values in the collection in the format required by the chd querystring parameter.
As discussed in last week's Creating Charts with the Google Chart API article
the values for the chd parameter, when specified using text
formatting, should be a comma-delimited list of values between 0 and
100
where the largest data point has a value of 100 and all other data
points are expressed as a percentage of the largest. This computation is
handled within
the RenderRelativeCHDValues method.
The net result is that the Chart control renders an
<img> element with an appropriate src attribute, just like we did
in last week's article. But instead of having to create that src attribute ourselves, the Chart control does that for us. All we have to
do is set a few properties and specify the data to plot.
Using the Chart Control in an ASP.NET Page
The download available at the end of this article includes the complete source code for the Chart control, as well as a demo ASP.NET website. To use the skmControls2 controls in an ASP.NET website, copy the DLL to the website's
Bin directory and then add the following
@Register directive to the tops of the .aspx pages where you want to use the controls:
<%@ Register Assembly="skmControls2" Namespace="skmControls2.GoogleChart" TagPrefix="cc1" %>
|
@Register directive in the Web.config file so that you do not need to add it to every
ASP.NET page that uses the controls. See Tip/Trick:
How to Register User Controls and Custom Controls in Web.config.)
Next, add the following markup to the page:
<cc1:Chart ID="MyChart" runat="server" />
|
(You can also add the control to your Toolbox in Visual Studio by: right-clicking on the Toolbox; selecting Choose Items; going to the Browse tab; and browing to the
skmControls2.dll assembly. Once added to the Toolbox you can add the Chart control to a page by dragging it from
the Toolbox onto the page's Design surface or declarative markup portion.)
The demo for download at the end of this article includes three Chart control examples. Two of them (
GoogleChartDemo.aspx and
GoogleChartDemo2.aspx) use AccessDataSource controls to display category sales data from the Northwind database. The following screen
shot shows is from GoogleChartDemo2.aspx. There are two
TextBox controls in which the user can filter the dates. Upon entering
dates and
clicking the "Refresh Data" button the page shows the results in a
GridView control as well as in the Chart control. This functionality is
possible
without having to write a single line of code!
The third demo (
GoogleChartDemo3.aspx) contains a chart
whose data is constructed programmatically. In particular, this third
demo enumerates
the files in the web application's root directory and sums up the total
file size for each unique file extension. It then displays this data in a
pie chart.
As you can see by the screen shot below, there are four unique file
extensions - .aspx, .vb, .config, and
.png - with .aspx files comprising more disk space than all of the other file types combined.
Conclusion
The Google Chart API offers a free and easy way to generate charts - simply craft an appropriately formatted URL, put it in the
src attribute
of an <img> element, and, voila, you have a chart on
your web page. Unfortunately, there's a bit of work involved in
constructing
the querystring. Rather than having to memorize the assorted querystring
parameters or toil writing code to generate that query, it would be
easier to
use a custom server control that did all that for us. The Chart control
in the skmControls2 Library is such a control and, as we saw in this
article,
is can be used to generate simple charts using the Google Chart API.
Happy Programming!
Thank you
original author By Scott Mitchell
