SharePoint 2010 Pie Chart with Counts

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

Trackbacks Comments
  • Karan Bhatia says:

    Hi, thanks for the tutorial ! I am a Sharepoint beginner, and this may sound like a dumb question but I still need to ask this because I am not able to figure it out on my own –> How do I create the column “Count Related”. Can you please brief that here !

    Thanks

    • Tim says:

      Thanks for visiting. To answer your question, you don’t create “Count Related”. You create “StatusLookup” earlier in the tutorial. Then when you create a lookup column that looks up data from a column that is itself a lookup column, that’s when you get the column name “StatusLookup (Count Related)”. I hope this helps clear it up.

      Tim

      • Karan Bhatia says:

        Thanks a ton Tim. That really helped ! So, I am able to make a pie-chart with counts. I had one more doubt. Now, I am having a list that has a column called due date. I have another column that is a calculated column that sets the value On-time if today due date. I am trying to create a pie-chart showing projects on-time and projects that are late. Is there any way of doing this since this column is a calculated column.

        Thanks again Tim
        Karan

      • SSS says:

        I have created as you suggested in the above blog. Everything works fine. But the issue is, I have selected the “count” in Y axis of the Pie Chart, but the “Title” only displays in the Pie Chart. Could you Please help me to display the “Count” value.

  • Eric says:

    This worked perfect for lists created in SP – however I created a custom InfoPath form for a client and I am unable to get the count from a lookup field using a data connection – even thouigh the lookup list is in the same site collection. Any advice?

  • Jamie says:

    This page has helped no end! A task that’s been bugging me for a few weeks with no apparent solution and I find your site.

    I got the same process to work with a document library and custom (infopath) form, the difficult part was getting the form to submit the values to the correct columns (as I’d already created the form before finding your solution).

    Do you know if its possible to have the StatusLookUp list grouped? Say if you wanted a group of counts for a particular date, you’d have another column for the date (which would be grouped for the current view) and then the chart web part could group the counts by date then by ‘status’ ?

  • Lou says:

    So beautifully simple! Thanks to you for writing this and google for sending me your way. I shall be checking out some more of your posts!

  • Swetal Patel says:

    You are amazing! What an elegant solution… Love it and using it for my corporate intranet sharepoint site. Thanks

  • Uma says:

    Simply Beautiful .

    Perfect for my solution :)

  • Optimistic Al says:

    Elegant solution…well done!

  • Rich says:

    Thanks for this – does seems to be quite a complex subject which surprises me. I would think SP would offer a range of reporting options OTB! Anyway, quick question – is this web part only available in the Enterprise edition of SharePoint 2010? I take it this is the chart from the business data we parts? Thanks!

  • Jackson says:

    Question: Say the column you want to count has a choice (radio bubble) feature, that we use that includes the names of which the project is assigned to (Jackie, Lana, TBD). I made a new list to lookup and then count the number of each individual. However, once I check Look-Up on the column feature, the dropdown menu does not include any of the choice or date featured columns. Is there a way to work with these columns or do they have to be text related columns to Look-Up and count. Thanks for the help

  • LeQuisha says:

    Hello, thanks for the instructions it was very helpful. Now I need to know how to run the same count for differnt projects. I want to show the count for project 1 in one view and count for project 2 in a different view or possibly show both in one view with a legend that shows the different projects. is there a way to add multiple projects to the view?

  • Oli says:

    Hi,

    This may sound stupid, however once i have created the chart web part, and would like to display both the titles and the percentages on my graph, can someone please help with this?

    When I click to show a legend if i choose to have the percentages shown on the table, the legend changes to this automatically? in my eyes that is pointless as you dont need to display the same information twice? so can someone please tell me how I can show the percentages on the graph, and the titles in the legend?

    Oli

  • Dave says:

    This solved one of my major issues. Thank you. The next piece I need to be able to do is dynamically filter the results. Currently I have a web part that displays the source data and a web part filter. I can add a web part to the page and display what you showed us above, but cannot filter the results to reflect what the filtered source data is displaying.

  • Twain says:

    That is a nifty trick. However, isn’t this a very manual way to solve the problem? If the number of dashboards is many, then won’t it be hard to configure and maintain? is the 3rd party way still the easiest? for example Collabion (http://bit.ly/YVMpwM), and Nevron (http://bit.ly/YVMwZk) have simple data grouping options.

  • Tasha says:

    How do you migrate column data?

  • Jesse says:

    OMG This is perfect! Thanks so Much
    M&J

Leave a Comment