How to create a no-code drill-down chart in SharePoint 2010

Introduction

This posting walks you through the process of creating an out-of-the-box, no-code drill-down chart in SharePoint 2010.  It uses the Northwind sample database transformed to SharePoint 2010 for data, specifically the Products and Categories lists.  The Categories list is related to the Products list through a one-to-many relationship.  The process involves several discrete steps:

1. Inserting the Chart web part onto the target page.
2. Adding a count field to the Categories list that automatically counts the number of occurrences of a Category item in the Products list.
3. Connecting the Chart web part to the Categories list.
4. Configuring Chart drill-down capability.

Step 1: Insert the Chart web part

1. Launch SharePoint Designer 2010, and then connect to the target site.
2. In Designer insert the Chart web part onto the target page.
3. Next, in Code view, search for the ShowToolbar parameter, and then set this to False.  You will not need to go to Advanced Mode to make this change.
4. Select the Chart web part inserted on the page, and then, on the Format ribbon, click Chrome Type, and then click None.
5. Click the Chrome State button, and then click Normal.
6. Save the file and then exit Designer.
7. You should see something like the following:
   

Step 2: Add count column to Categories list

1. Launch a browser, and then connect to the target site.
2. Navigate to the Categories list:
   
3. Select the List ribbon.
4. In the Manage Views group, click the Add Column button (to find it, hover the cursor over ribbon items to view a popup title of the item).  The Create Column modal dialog appears.
5. Configure as follows:
1. Column name: Count.
2. Type of information: Lookup.
3. Get information from: Products.
4. In this column: Category (Count Related).
5. Leave all other fields default:
   
6. Click OK.  The Categories list now displays the number of times that each category was used in the Products table:
   

Step 3: Connect Chart to Categories list

1. Launch a browser and then connect to the target page.
2. On the Page ribbon, click Edit Page.
3. As you move the cursor over the chart web part, a dropdown will appear across from the web part title. Click the dropdown, and then point to Connect to Data:
   
4. Click this menu item.
5. Select Connect to a List.
6. Click Next.
7. Select the site where the Products list is hosted; select the Categories list.
8. Click Next.
9. Click Next.
10. Expand the Series Properties area, and then configure as follows:
1. Series Name: CategoryCounts.
2. Series Type: Bar.
3. Leave all other fields default:
   
11. Click Finish.

Step 4: Configure chart drill-down capability

1. NOTE: after making each change below, press ENTER.  Changes can be made only one at a time, as each change triggers a page reload.
2. Launch a browser and then connect to the target page.
3. On the Page ribbon, click Edit Page.
4. As you move the cursor over the chart web part, a dropdown will appear across from the web part title. Click the dropdown, and then point to Advanced Properties.:
   
5. In the Select and Element tree at left, click Series.
6. On the Chart Properties listing, scroll down to the MapArea group.
7. Enter the following URL+keyword string:

   /Northwind2/Lists/Products/AllItems.aspx?FilterField1=Category&FilterValue1=#VALX
   

   
8. Click Finish.
9. Move the cursor over the bars to see the unique URL now configured for each:
   
   Clicking on any bar navigates the browser to the Products list, now filtered by the appropriate category value.  For example, clicking the Seafood bar in the chart, which has a value of 12, navigates the browser to the Products list, which lists 12 seafood items:
   

Optional: Enhance Chart Appearance

1. Launch a browser and then connect to the target page.
2. On the Page ribbon, click Edit Page.
3. As you move the cursor over the chart web part, a dropdown will appear across from the web part title. Click the dropdown, and then point to Advanced Properties.
4. Change Chart Dimensions
1. In the Chart Properties listing, scroll down to the Layout group, and change Width to 500px.
2. Press Enter.  The change is then implemented:
   
   Be sure to press Enter after making each change.  Otherwise, you might lose some changes.
5. Show Interlaced
1. On the Select an Element tree, select Chart Areas.
2. From the Axis Menu, select X axis.
3. In the Chart Properties listing, in the Appearance group, configure as follows (press Enter each time):
1. IsInterlaced: True.
2. InterlacedColor: WhiteSmoke.
3. The changes will be implemented immediately:
   
4. Click Finish.
6. Add Title
1. On the Select an Element tree, select Titles.
2. Click the small + sign just above the top right corner of the Chart Properties list.  This adds a new Title object to the chart, and the Chart Properties listing now displays configurable parameters for this object.
3. In the Appearance group, scrolling down a bit, look for the Text parameter and then set the text to the desired title.
4. Scroll just a bit back up, looking for the Size and Bold parameters, setting these to 12 and True respectively:
   
7. Add Axis Labels
1. On the Select an Element tree, select Chart Areas.
2. From the Axis Menu, select X axis.
3. Scroll down to the Title group.
4. Set the Bold parameter to True.
5. Enter the desired x-axis title into the Title parameter.
6. From the Axis Menu, select Y axis.
7. Scroll down to the Title group.
8. Set the Bold parameter to True.
9. Enter the desired y-axis title into the Title parameter.
10. Now the chart has X and Y axis labels:
    
11. Click Finish.
8. Display each bar in different color
1. On the Select an Element tree, select Series.
2. On the Series Menu, select the desired series (if you have only one series, this will likely display as Default).
3. On the Chart Properties listing, in the Appearance group, scroll down until you see the Palette property.  By default, this will be set to None.
4. Select any other setting available from this dropdown.  The change will be immediate:
   
5. Click Finish.
9. Hide the Data & Appearance and Advanced Properties toolbar
1. For this you will need SharePoint Designer and Design permissions to the site or page.
2. Launch SharePoint Designer 2010.
3. Connect to the site hosting the page containing the chart web part.
4. Checkout the file
5. Detach the file from the layout.
6. Open the page in Advanced edit view.
7. Search for ShowToolbar.
8. Set this parameter to False.
9. Save the file.
10. Check it back in.
10. The final interactive chart:
    

Summary

This posting as shown how to create a no-code drill-down chart in SharePoint 2010.  It has also shown a few ways in which to enhance the chart's appearance.  For additional details on the topics discussed in this posting, see the references below.

References

• Direct
• SharePoint 2010 Northwind Sample Database Site Template
• MSDN Chart Control Keywords
• General
• Built-In Keywords for Series Properties
• Keywords [rs_vsDataVis]
• Using the Chart Web Part with Web Part Data Sources
• Microsoft Chart Control How-to: Add Rich Content Using Keywords

Notes

• The URL defined in Step 4.7 does not include the view ID.  You see this ID when you choose to filter a list by some column value in the list.  You don't need this ID if you are referring to just one list, and this list is located on a different page, such as an application page.  However, if you are building page containing lists, and the charts are referring to those lists, you will need to use the view ID.  Otherwise you will experience this error: One or more field types are not installed properly. Go to the list settings page to delete these fields.
• The method presented here also works for the chart web part in SharePoint 2013.