I have a library of items I would like to report on dynamically. For the sake of this exercise, I would like to show 2 items from the list:

The region in which the list item exists
The status of the list item

I am using a calculated column to determine the status.

When I attempt to graph using the web part, I am not able to select the status column as a Y value. This, I assume, is because it is not a numerical value.

Something tells me my best solution is to create a second list that will connect to the library, to provide a summary table with actual counts for each region/status, but I am not clear on the methodology to do so. When I attempted to use lookup columns, I was simply given a blank list and the ability to add one item at a time from the library.


This is a tricky request, with no easy out of the box answer.

By "the web part", I assume you mean the Chart Web Part in SP 2010. This tool doesn't do aggregations (and has been removed in SP 2013 btw).

If you were just using a lookup to the status (with the status values stored in a separate list), you could do a "reverse lookup" to get the count. Unfortunately if the status is calculated (not set manually), this makes it harder to combine it with a lookup.

This post shows you how a "reverse lookup" could work: http://www.timferro.com/wordpress/archives/261

Now, the standard way to get counts is to create a grouped view (group by region and status). Unfortunately there is no out of the box way to chart from a grouped view. If you go this route, I would suggest to take a look at these two options:

[Disclaimer: I am the author of the above solution]

For the record, there is also a nice free charting solution that relies on Google: http://spjsblog.com/2013/03/11/spjs-charts-for-sharepoint-export-to-excel-and-sp2013-support/

If you plan to use it, just make sure you understand the performance and licensing implications.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.