So you want to use an out of the box (OOB) SharePoint pie chart to tell you how many list items have a certain status (or other column choice)? Trying to add that web part to a page and setting its target list as the list you want will not work. Many 3rd party tools exist to deal with this exact limitation of the OOB charting web part. The good news is that in SharePoint 2010 there is an easy way to get this data graphed with the OOB pie chart using a little known trick. For this example I will show you how to set up a Task list where you want to show the Status field count values in a pie chart.
Step 1, create a custom list called StatusLookUp and add an item for each status you want to have. The items in the list will be used as statuses in the “Status” column via a lookup column.
Step 2, create the list you want to use to track your tasks, for this example I am calling it ProjectStatus. If you already have a list this will work as well, you will need to migrate your status column data though. After you have your list setup, create a column called StatusLookup. It will be a Lookup column type and you will use the StatusLookUp list as your input. After creating this column you will need to migrate your current Status column data to this column if you already have a list, if not create a few test items and set their status using this column. This will be a small burden to migrate the statuses of each item but well worth it. I recommend doing it programatically via .Net as a console app or ASPX page. The code for that will simply loop through your list and set the new column data as the current column data with the exception that you will need to format that data to fit into a lookup field type.
This is my basic list with 4 projects that I am tracking and what the status is of each. Note that the StatusLookup field is a lookup of the StatusLookUp list and not the OOB Status field.
Step 3, create a new Lookup column in your StatusLookup list. Call the column Count and reference the column that you just created in your ProjectStatus list. Be sure the column contains “(Count Related)” in the name. Now that you have created your new Status field in your list and migrated the data over, go back to your original StatusLookup list and create a new column that is a lookup of the column that you just created as a lookup to this list. It sounds like a crazy loop but when you do this it returns a count of the number of occurrences instead of the same data.
Step 4, add the chart web part to the page and connect it to your Status Lookup list. Set the Count as your Y axis and the Title as your X axis. Finally, customize your chart to be a pie graph and set your data to display as you wish. Viola! Since this post is about the data and not really about the pie chart I skimmed this part but trust me, you can totally just add the web part and walk through the setup wizard and get the data you need.
Hope this helps, enjoy.
– Tim Ferro