In this example we will extend the previous pie chart to be a drill down chart, so all of the code and data will be utilizing what had been previously created. Please feel free to start with code from previous blog (Create ASP.NET 4.0 Pie Chart with Tooltips).
What are we creating?
A drill down chart is an interactive chart that has clickable or interactive hot spots that will allow us to view more specific information for user selected data. Continuing with our retail theme from the first blog: A department level manager, having been impressed with your pie chart, would like to have a line graph be displayed with more specific sales information for the selected product category.
A moment on User Interface Design
Before we start coding it's important to think about how our application is being used; yes what is the workflow of our targeted end users, will they typically use the mouse or keyboard to navigate within our application. In this case we are being requested to provide a line graph based on a selected value. We do have options to accomplish this task, and depending on the typical workflow of our end users we can quickly determine which option is right for your application.
Two simple options to accomplish this task would be:
1. Use a drop down list control that would allow the user to select the department from the list; great for those who primarily use the keyboard for application navigation (think data entry).
2. Pie graph interactivity, creating clickable graph hot spots allowing the user to click the desired product category and display the corresponding data.
Given the title of this blog you know that I will be using the second option but it is important to know there are several reasons why I would choose the section option.
1. Today's users are expecting more interactivity from their business applications, this includes graphs.
2. In this example, the manager assures me that users will primarily be using a mouse for navigation as these charts will be hosted inside an internal Share Point site.
3. So for the 'mouse-navigators' of the world this means that by using drill-down functionality (as compared to the drop down) will require one less mouse-click for the user to accomplish their desired response (seeing the detailed data). (Alternatively, if my users were primarily using the keyboard for navigation then I most likely wouldn't use a graph drill down for navigation.)
Lets start coding (Extend the existing pie chart)
First we need to extend the existing pie chart to provide data and the event that will allow us to respond to the user's request. An item to note is that utilizing multiple series for a single chart is very helpful in supporting your business rules and typically the easiest option to reference corresponding data values after your graph has been data bound.
- Set the PostBackValue for Series1
- Open the Series Collection Editor by selecting the Pie Chart and clicking the ellipsis for the Series property
- Select Series1 in the Members panel
- In the properties panel scroll down and expand the 'MapArea' section
- Set the PostBackValue to '#VALX' this will set the post back value to the same value as our XValueMember. (most of the chart keywords will work here reference links below)
- Click ok and save
- Add a new series to the chart
- Our existing pie-chart series holds the department name and total sales. We need a series to retain our department ID so we have a concrete value to request additional information from the database.
- In the design view select the pie chart
- In the properties window select the ellipsis for the 'Series' property
- In the 'Series Collection Editor' dialog click the 'Add' button
- For the new series set the following properties
- ChartType = "Pie"
- Name = "ProductCategoryReference"
- XValueMember = "ProductCategoryName"
- YValueMember = "ProductCategoryID"
- Yours series should look like this:
- Click the 'Ok' button to close
- Map Product Category ID to PostBackValue
- To reference the concrete ID in the series that we created in the previous step we must use some code.
- Select the chart and add an event handler for the "DataBound" event.
- Go to the code behind and add the following code
- The code appends the ProductCatogoryID from the second series to the post back values of the original series
1: protected void Chart1_DataBound(object sender, EventArgs e)
2: {
3: Chart chart1 = (Chart)sender;
4: int dpCount = chart1.Series[0].Points.Count;
5: for (int x = 0; x < dpCount; x++)
6: {
7: DataPoint ptRef = chart1.Series["ProductCategoryReference"].Points[x];
8: DataPoint ptTarget = chart1.Series["Series1"].Points[x];
9: ptTarget.PostBackValue += string.Format(",{0}", Convert.ToString(ptRef.YValues[0])); ;
10: }
11: }
-
Add Click event handler to the Chart control
- Add code to capture our post back values
- Add a session variable to retain the selected product category id
1: protected void Chart1_Click(object sender, ImageMapEventArgs e)
2: {
3: string[] postValues = e.PostBackValue.Split(',');
4: int productCategoryID = int.Parse(postValues[1]);
5: Session["ProductCategoryID"] = productCategoryID;
6: }
- Drag a new SQLDataSource on to the design surface
- Configure the data source (see my configuration below)
- Be sure to add the session parameter to the query
1: <asp:SqlDataSource ID="sqlSalesByProductCategory" runat="server"
2: ConnectionString="<%$ ConnectionStrings:SalesDatabaseConnectionString %>"
3: ProviderName="<%$ ConnectionStrings:SalesDatabaseConnectionString.ProviderName %>"
4: SelectCommand="SELECT ProductName, COUNT(P.ProductID) CountOfSales FROM ProductCategory PC INNER JOIN Product P on PC.ProductCategoryID = P.ProductCategoryID INNER JOIN Sale S ON P.ProductID = S.ProductID WHERE PC.ProductCategoryID = @SessionProductCategoryID Group By ProductName">
5: <SelectParameters>
6: <asp:SessionParameter Name="SessionProductCategoryID" SessionField="ProductCategoryID" DbType="Int32" />
7: </SelectParameters>
8: </asp:SqlDataSource>
- Add our detail graph (Line Graph)
- From the toolbox drag/drop a new Chart onto the design surface
- Set the following properties by selecting the smart tag carrot '>' when viewing the chart control on the design surface:
- Choose Data Source = "sqlSalesByProductCategory"
- Chart Type= "Column"
- X Value Member = "ProductName"
- Y Value Member = "CountOfSales"
- That's it!!
- Save your work and press F5 to run
- Clicking an area on the pie chart will cause the bar chart to be populated.
Now you may have noticed the ugly post back process that repaints the screen for each click of the pie chart (this is easily solved by using an Ajax Update Panel control) who knows maybe a future blog.
~Happy Charting~
Jim
Chart Control Keywords (MSDN)
Visual Studio 2010 C# Project Source (Chart Drill Down)