Web Chart Axises or Reporting Solution

Question

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.

Answer 1

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:

• free: http://no-code-moss2007.blogspot.com/2009/04/situacion.html
• for a fee: http://blog.pathtosharepoint.com/2012/11/06/interactive-demo-charting-in-sharepoint/

[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.