Using Microsoft's Chart Controls In An ASP.NET Application: Creating Drill Down Reports
By Scott Mitchell
| A Multipart Series on Microsoft's Chart Controls |
|---|
|
A picture is worth a 1,000 words... This adage rings especially true when it comes to reporting. Charts summarize and illuminate patterns in data in a way that long tables of numbers simply cannot.
The Microsoft Chart Controls are a free and encompassing set of charts for WinForms and ASP.NET applications. This article series explores
how to use these Chart Controls in an ASP.NET application.
|
Introduction
Each series in a chart is composed of a set of data points, which are modeled via the
DataPoint class. For most chart types, the two key attributes of a data
point are its X and Y value. For example, in a line chart the X value indicates the position of the data point along the X axis, while the Y value represents
the position of the data point along the Y axis. Ditto for a column chart, although it may help to think of the Y value as the height of the column.
In addition to X and Y values, data points can include additional bits of information, including an associated URL. As you would expect, when a data point has an associated URL it becomes click-able in the rendered chart image, and clicking the data point whisks the user to the specified URL. By specifying URLs, it's possible to create drill down reports, which are reports that let the user click a particular data point in a chart to explore its details.
This article examines how to build drill down reports. Specifically, we'll walk through two demos that display the same data using a column chart. The charts' X axes list
the categories in the Northwind database's Categories table, with the height of each category's column indicating the number of products associated with said
category. Clicking on one of these columns in the chart takes the user to a second web page, which displays a grid showing the products that belong to the selected
category. The second demo enhances the chart to display the corresponding category's products when hovering the mouse over the category column.
Read on to learn more!
A Quick Overview On Plotting Chart Data
As discussed in the Getting Started article, a chart can contain one or more series, where each series is a collection of data points. In Plotting Chart Data we looked at a variety of ways to specify these X and Y values for each data point in a series. One way to specify the data points is to do so programmatically, one point at a time. The following snippet of pseudocode illustrates this concept:
' Get the data to plot
|
The above code assumes that there is some object of type SalesFigureList that represents a collection of SalesFigure objects, and that there exists
a method named GetSalesNumbersForYear that returns a SalesFigureList object populated with the sales for a specified year. Instead of calling the
GetSalesNumbersForYear method, you could run a query against a database, call a Web Service, read in the contents of an XML file, or whatever it is you need
to do to get the data to display on the chart. Regardless of how you retrieved the data, the next step is to enumerate the data and, for each record, add a data point to the
series. The above code adds the a new data point the the chart's SeriesName series, using the department name as the X value and the gross sales as the Y value.
The AddXY method of the series's Points collection creates a new DataPoint object, sets its XValue and YValues
properties based on the supplied input parameters, and then adds the new DataPoint object to the Points collection. In addition to its
XValue and YValues properties, the DataPoint class offers a number of other properties, one of them being the Url property.
If the Url property is specified then the chart is rendered in such a manner that each data point becomes click-able, and clicking a data point takes the user to
the specified Url.
Creating a Simple Drill Down Report
The
DataPoint object's Url property can be used to create drill down reports. A drill down report is a report in which the viewer can learn more about
a particular data point by clicking on it. Let's use this property to build a drill down report that works in the following way: we'll use the Microsoft Chart controls to
display a column chart that shows information about the categories in the Northwind database. Specifically, the chart will have a column for each category, with the height
of the column indicating the number of products assigned to the category (see the screen shot below). Clicking a column will take the user to a second page where they will see a grid that displays
information about those products in the selected category.
Let's start by building the page that contains the chart, which I've named Simple.aspx. (This demo and the one we will tackle later in this article are both available
for download at the end of this article.) The page contains a Chart control named chtCategoriesProductCount that has a single series defined
(Categories) of type Column and a single, 3D Chart Area.
<asp:Chart ID="chtCategoriesProductCount" runat="server" Width="550px" Height="400px">
|
The page also includes a SqlDataSource control named dsProductsPerCategory, which defines an ad-hoc SQL query that returns the CategoryID and
CategoryName fields of each category in the Categories table, along with the number of products associated with each category. Note that this
SqlDataSource control is not bound to the Chart control. Rather, we are going to programmatically get the data from the SqlDataSource control and then manually add
a DataPoint object for each record returned from the SqlDataSource control. We'll discuss the rationale behind this decision later on in this article.
The code in the Page_Load event handler follows. As you can see, we start by getting the data from the SqlDataSource control by calling its Select method.
Next, the results are enumerated and for each record we first add a DataPoint to the series and then set its Url property to
"~/Demos/DrillDown/ProductsInCategory.aspx?CategoryID=CategoryID", where CategoryID is the CategoryID value of the record used to create the
DataPoint. (For more information on how to programmatically work with the SqlDataSource control, see: Accessing
and Updating Data in ASP.NET 2.0: Programmatically Accessing Data using the Data Source Controls.)
'Reference the Categories series...
|
Pay particular attention to the code in the For Each loop. Note that we start by calling the Points collection's AddXY method to add
a DataPoint to the chart for the current record from the database. The AddXY method does not allow us to specify a value for the Url
property, so we need to get a reference to the just-added DataPoint and set this property ourselves. Because the AddXY method adds the new
DataPoint to the end of the Points collecting, we can get the just-added DataPoint via
series.Points(series.Count - 1).
With this code in place, the Simple.aspx web page shows a column chart of the categories' number of products. What's more, each column, when clicked,
will send the viewer to ProductsInCategory.aspx?CategoryID=CategoryID, which we'll create momentarily. The screen shot below shows the column chart.
If you do a View/Source on the web page in your browser you'll see that setting the Url property causes the Chart control to render an
image map alongside the <img> element that displays the chart. An image map is defined via the
<map> HTML element, and specifies regions of an image. The following is a simplified snippet of the <img> and <map>
markup generated by the Chart control.
<img id="id" src="chartImage.png" alt="" usemap="#imageMapId" ... />
|
The <img> element that precedes the <map> element is image of the actual chart. (Refer to the
Rendering the Chart installment in this article series for a refresher on how the Chart control generates
and displays the chart in the user's browser.) The <img> element's usemap attribute associated the image with the image map.
The <map> element defines an image map, which is a series of coordinates that define regions in the associated image. When one of these regions is clicked,
the user is whisked to the URL specified in the <area> tag's href attribute.
Finishing Touches - Creating the Details Page, ProductsInCategory.aspx
At this point we have created the drill down chart but we still need to implement the details page,
ProductsInCategory.aspx. This page is passed a CategoryID
value through the querystring and needs to display information about that category's products. To accomplish this, I created this page with a GridView and SqlDataSource. The
SqlDataSource control queries the Products table, bringing back information about those products whose CategoryID value matches the CategoryID
value specified through the querystring. These results are then displayed in a GridView.
The screen shot below shows the ProductsInCategory.aspx page when a CategoryID value of 1 is passed through the querystring, which is what would happen
when clicking the Beverages column in the chart.
For more information on getting database data based on a querystring parameter, check out Accessing and Updating Data in ASP.NET 2.0: Filtering Database Data with Parameters.
Showing Drill Down Data In The Same Page
The example we just looked at used two pages to show drill down data. While this is certainly nice, it can be inconvenient if the user wants to quickly scan the detail data. Perhaps the user is interested in discovering what category contains the most expensive product. Having the product list in a separate page requires a lot of extra clicks to unearth this information.
An alternative approach is to show the drill down data in the same page as the chart. In addition to the Url property, the DataPoint class also offers
a MapAreaAttributes property. This property, if specified, defines what client-side attributes should be applied to the image map for each DataPoint.
This property can be used to inject JavaScript into the image map so that some action can occur in response to a client-side event. For instance, you can use this property
to run client-side code when the user mouses over or mouses out of a particular data point.
The download available at the end of this article includes a demo named Fancy.aspx that extends the Simple.aspx demo. Clicking a column in the
Fancy.aspx still sends the user to ProductsInCategory.aspx, but in addition to that the user can merely hover their mouse over a column to see
the same product grid in ProductsInCategory.aspx displayed beneath the chart.
To accomplish this I added an <iframe> beneath the Chart control on Fancy.aspx. Next, I create a JavaScript function named
showProductsInCategoryIFrame(categoryId). This function uses JavaScript's document.getElementById(id) function to get a reference
to the <iframe> and sets its src attribute to JustProductsInCategoryGrid.aspx?CategoryID=categoryId.
JustProductsInCategoryGrid.aspx is an ASP.NET page that contains the same GridView and SqlDataSource as ProductsInCategory.aspx. The only difference
is that it does not include all of the extra markup - the header, the menu on the left, and so forth.
When plotting the chart's data points I set both the Url and MapAreaAttributes properties. As in Simple.aspx, the Url
property is set to ProductsInCategory.aspx?CategoryID=categoryId. The MapAreaAttributes property specifies the JavaScript to execute in
response to the onmouseover and onmouseout client-side events.
justAddedPoint.MapAreaAttributes = String.Format("onmouseover=""showProductsInCategoryIFrame({0});"" onmouseout=""showProductsInCategoryIFrame(-1);""", myReader("CategoryID").ToString())
|
When the user mouses over a column in the chart, the
showProductsInCategoryIFrame(categoryId) function is called, where categoryId is the CategoryID value of the category whose column
was moused over. When the user mouses out of the column, showProductsInCategoryIFrame(-1) is called. Because there is no category with a CategoryID
value of -1, the GridView is not displayed. The net result is that the area beneath the chart appears empty.
The screen shot below shows Fancy.aspx when the user is hovered over the Beverages column.
Working With Declaratively Bound Data
Both the
Simple.aspx and Fancy.aspx demos work by programmatically adding points to the chart one at a time. The points are added to the chart in
Simple.aspx from the Page_Load event handler. Fancy.aspx uses Scott Allen's
ChartBuilder class. In both cases, the points to plot are
added to the chart using the Points collection's AddXY method, after which the other properties are set (Url, MapAreaAttributes,
and so on).
Data can also be bound to the chart declaratively. However, there is no way to specify the value for the Url or MapAreaAttributes properties using
the declarative syntax, at least not a way that I could discover. You can, however, create an event handler for the Chat's DataBound event and then enumerate
the DataPoints. To do so, you'd write code similar to the following:
Protected Sub ChartID_DataBound(ByVal sender As Object, ByVal e As System.EventArgs) Handles ChartID.DataBound
|
The problem I ran into when trying to use the above approach is that I couldn't determine the CategoryID. Each DataPoint has XValue and YValues
properties, but these were set to the CategoryName and ProductCount values returned by my SQL query, respectively. If anyone has a solution to this
problem - namely, how to have the chart display the CategoryName and ProductCount values and have the DataPoints' Url properties
reference a different field returned from the data source, please do let me know and I'll add an example to the demo and update this article accordingly.
[UPDATE - September 1st, 2009]
Alert reader Olek Naleczynski found a way to create drill-down reports using databinding. Here's what Olek had to say (with slight editing
job by yours truly):
The Series object has theThank you for your solution, Olek! I've updated the code available at the end of this article to include an additional demo,YValuesPerPointproperty, which is used in bubble graphs to specify the bubble size. I found that you can set this property declaratively. Thus, you can create a series of type Bar (or Column) with two Y values for each point - actually you could even have three or four! Those additional Y values are then ignored during rendering.To specify a second Y value (or a third, or fourth) add the additional members to
YValueMembersproperty and separate them with commas like so:
<asp:Series Name="Categories" XValueMember="CategoryName" YValueMembers="ProductCount, CategoryId" ChartType="Bar" YValuesPerPoint="2" ...></asp:Series>Also note that you need to set the
YValuesPerPointproperty to the number of fields you specify inYValueMembers.During the
DataBoundevent, simply retrieve this second value for each point, and use it to set additional attributes. For instance, to set theUrlvalue based on the data point'sCategoryIdvalue, use the following code, which loops through each point in the series, determines theCategoryIdvalue by getting the point's secondYValuescollection value, and then sets the point'sUrlproperty accordingly:
Protected Sub chtCategoriesProductCount_DataBound(ByVal sender As Object, ByVal e As System.EventArgs) Handles chtCategoriesProductCount.DataBound
For Each point As DataPoint In chtCategoriesProductCount.Series("Categories").Points
Dim categoryId As Integer = point.YValues(1)
point.Url = String.Format("~/Demos/DrillDown/ProductsInCategory.aspx?CategoryID={0}", categoryId.ToString())
...
Next
End Sub
~/Demos/DrillDown/Declaratively.aspx, which shows Olek's approach in action.
Happy Programming!
Attachments:
Further Reading
ChartBuilder class
| A Multipart Series on Microsoft's Chart Controls |
|---|
|
A picture is worth a 1,000 words... This adage rings especially true when it comes to reporting. Charts summarize and illuminate patterns in data in a way that long tables of numbers simply cannot.
The Microsoft Chart Controls are a free and encompassing set of charts for WinForms and ASP.NET applications. This article series explores
how to use these Chart Controls in an ASP.NET application.
|
