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.
