When you think ASP, think...
Recent Articles
All Articles
ASP.NET Articles
ASPFAQs.com
Message Board
Related Web Technologies
User Tips!
Coding Tips
Search

Sections:
Book Reviews
Sample Chapters
Commonly Asked Message Board Questions
JavaScript Tutorials
MSDN Communities Hub
Official Docs
Security
Stump the SQL Guru!
Web Hosts
XML
Information:
Advertise
Feedback
Author an Article

ASP ASP.NET ASP FAQs Message Board Feedback
 
Print this Page!
Published: Wednesday, October 28, 2009

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.

  • Getting Started - walks through getting started using the Chart Controls, from version requirements to downloading and installing the Chart Controls, to displaying a simple chart in an ASP.NET page.
  • Plotting Chart Data - examines the multitude of ways by which data can be plotted on a chart, from databinding to manually adding the points one at a time.
  • Rendering the Chart - the Chart Controls offer a variety of ways to render the chart data into an image. This article explores these options.
  • Sorting and Filtering Chart Data - this article shows how to programmatically sort and filter the chart's data prior to display.
  • Programmatically Generating Chart Images - learn how to programmatically create and alter the chart image file.
  • Creating Drill Down Reports - see how to build drill down reports using the Chart control.
  • Adding Statistical Formulas - learn how to add statistical formulas, such as mean, median, variance, and forecasts, to your charts.
  • Enhancing Charts With Ajax - improve the user experience for dynamic and interactive charts using Ajax.
  • Serializing Chart Data - see how to persist a chart's data and appearance to a persistent store.
  • Using the Chart Controls with ASP.NET MVC - learn how to display charts in an ASP.NET MVC application.
  • Exporting Charts - allow visitors to export charts as images and PDF files.
  • (Subscribe to this Article Series! )

    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!

    - continued -

    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
    Dim annualSalesNumbers As SalesFigureList = GetSalesNumbersForYear(2009)

    ' For each record, add a data point to the series
    For Each salesFigure As SalesFigure In annualSalesNumbers
       ChartID.Series("SeriesName").Points.AddXY(salesFigure.Department, salesFigure.GrossSales)
    Next

    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">
       <Series>
          <asp:Series Name="Categories" ChartType="Column" Palette="Chocolate" ChartArea="MainChartArea"></asp:Series>
       </Series>
       
       <ChartAreas>
          <asp:ChartArea Name="MainChartArea" Area3DStyle-Enable3D="true">
             <Area3DStyle Enable3D="True"></Area3DStyle>
          </asp:ChartArea>
       </ChartAreas>
    </asp:Chart>

    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...
    Dim categoriesSeries As Series = chtCategoriesProductCount.Series("Categories")

    'Get the data from the SqlDataSource control
    Dim results As DataView = dsProductsPerCategory.Select(DataSourceSelectArguments.Empty)

    For Each row As DataRowView In results
       'Add a DataPoint
       categoriesSeries.Points.AddXY(row("CategoryName").ToString(), Convert.ToDouble(row("ProductCount")))

       'Get the just-added DataPoint
       Dim justAddedPoint As DataPoint = categoriesSeries.Points(categoriesSeries.Points.Count - 1)

       'Set the just-added DataPoint's Url
       justAddedPoint.Url = String.Format("~/Demos/DrillDown/ProductsInCategory.aspx?CategoryID={0}", row("CategoryID").ToString())

    Next

    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.

    The column chart shows how many products exist in each category.

    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" ... />

    <map name="imageMapId" id="imageMapId">
       <area shape="rect" coords="450,82,455,87" href="ProductsInCategory.aspx?CategoryID=8" alt="" />
       <area shape="rect" coords="405,194,410,199" href="ProductsInCategory.aspx?CategoryID=7" alt="" />
       ...
    </map>

    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.

    The products in the Beverages category are displayed in a grid.

    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.

    Hovering over a category's column in the chart displays its products in a grid beneath the chart.

    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
       For Each point As DataPoint In ChartID.Series("SeriesName").Points
          point.Url = "..."
       Next
    End Sub

    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 the YValuesPerPoint property, 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 YValueMembers property 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 YValuesPerPoint property to the number of fields you specify in YValueMembers.

    During the DataBound event, simply retrieve this second value for each point, and use it to set additional attributes. For instance, to set the Url value based on the data point's CategoryId value, use the following code, which loops through each point in the series, determines the CategoryId value by getting the point's second YValues collection value, and then sets the point's Url property 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

    Thank you for your solution, Olek! I've updated the code available at the end of this article to include an additional demo, ~/Demos/DrillDown/Declaratively.aspx, which shows Olek's approach in action.

    Happy Programming!

  • By Scott Mitchell


    Attachments:

  • Download the code for this article
  • Further Reading


  • Accessing and Updating Data in ASP.NET 2.0: Programmatically Accessing Data using the Data Source Controls
  • Accessing and Updating Data in ASP.NET 2.0: Filtering Database Data with Parameters
  • Charting With ASP.NET And LINQ | Download Scott's 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.

  • Getting Started - walks through getting started using the Chart Controls, from version requirements to downloading and installing the Chart Controls, to displaying a simple chart in an ASP.NET page.
  • Plotting Chart Data - examines the multitude of ways by which data can be plotted on a chart, from databinding to manually adding the points one at a time.
  • Rendering the Chart - the Chart Controls offer a variety of ways to render the chart data into an image. This article explores these options.
  • Sorting and Filtering Chart Data - this article shows how to programmatically sort and filter the chart's data prior to display.
  • Programmatically Generating Chart Images - learn how to programmatically create and alter the chart image file.
  • Creating Drill Down Reports - see how to build drill down reports using the Chart control.
  • Adding Statistical Formulas - learn how to add statistical formulas, such as mean, median, variance, and forecasts, to your charts.
  • Enhancing Charts With Ajax - improve the user experience for dynamic and interactive charts using Ajax.
  • Serializing Chart Data - see how to persist a chart's data and appearance to a persistent store.
  • Using the Chart Controls with ASP.NET MVC - learn how to display charts in an ASP.NET MVC application.
  • Exporting Charts - allow visitors to export charts as images and PDF files.
  • (Subscribe to this Article Series! )



    ASP.NET [1.x] [2.0] | ASPMessageboard.com | ASPFAQs.com | Advertise | Feedback | Author an Article