KPI lists are often used to count how many items meet certain criteria. For example, a task list may contain a Status field with the values Not Started, Started and Completed. A KPI list would count how many items have each status and compare to some metric of performance.

It is then very frequent for users to want to display the results as a chart, a step which the SharePoint 2010 Chart web part simplifies considerably. Except that the count totals in the KPI list are not available to the chart web part and neither are totals in a view. So how can a chart be used when reflecting counts of items.

There is a workaround, which entails making the column we are interested in counting a lookup from another list which contains the different values of the column. In the case of our example, Not Started, Started and Completed.

In your task list, make sure the Status column is a look-up from this other list, as follows:

When you have set the Status column on a number of items, as below:

you can then add an additional column to your Status values list called Count, This should also be a look-up column, which should be linked to the Status (Count Related) column in your task list.

The final result is a list which contains a count of the number of items in your tasks list grouped by Status.

It is then a simple process to add a Chart web part and to connect to the count data in the Status list.

Advertisements