In my last post, I introduced sparklines and their applications. Today, let’s see on an example how to build simple bar graphs in a SharePoint list.

The scenario
In my example, I am tracking customer support calls over time. For each product or service, I want to monitor:
- the number of calls (too many support calls will kill my margins).
- the  trend: call numbers should decline, as I address initial issues and improve the Quality of my product/service.

While the above table contains all the data I need, it is not easy to read. By including sparklines next to the numbers, I’ll be able to visualize both the level and the trend, for each item (cf. first screenshot).
Ideally, a visual signal (for example a change in color) would alert me when I pass a certain threshold, but we’ll leave this for another day.

For a homemade solution, the easiest type to build is a bar graph. With more advanced tools, like the jQuery sparkline plugin, I could also opt for a line graph or a discrete chart, as shown in this screenshot.

The method to render the graphs is – once again – the HTML calculated column:
1/ Use a calculated column to build a HTML string that describes the bar graph
2/ Apply the “Text to HTML” script that will change the HTML string into actual HTML.

If you haven’t used the “HTML calculated column” method yet, you’ll need to learn it first (it’s really worth it if you are interested in visualization solutions). For the latest information on the HTML calculated column, start with this post.

If you are already familiar with this method, you’ll find below the formulas needed to render the bar graphs. For a first pass, feel free to skip the tedious explanations and simply copy/paste the formulas for the HTMLstring and BarGraph columns.

Single bar

Let’s start with a single bar for January. Once we get that, we’ll just replicate it for each month.

To adjust the graph size, I need to know the maximum value for my table data. For this, I am adding to my list a column, called Max (see first screenshot). In my example, I have chosen a fixed value of 2000 for Max, but I could also have entered a formula based on the content of other columns.

I can now:
- calculate the height of my bar: 20*Jan/Max pixels (the maximum height will be 20 px).
- create my HTML element, a green bar with the appropriate height:

="<b style='display:inline-block;background-color:chartreuse;margin-right:1px;width:4px;font-size:0px;height:"&(20*Jan/Max)&"px;' title='"&Jan&"'></b>"

Note: the tag name (here a “b” tag) doesn’t really matter as long as I can assign a background color.

Multiple bars

In theory, we would just need to repeat the above formula 12 times to get the chart for the whole year. Except that we hit a road block here: the complete formula will have a length of 12×150 characters, plus a wrapping tag. That’s far too much for a calculated column, which will “only” accept 1000 characters.

As all bars follow the same pattern, I’ll use the following trick: store the recurring string in a separate calculated column, that I’ll call HTMLstring:

="</b><b style='display:inline-block;background-color:chartreuse;margin-right:1px;width:4px;font-size:1px;height:"

 I can now use HTMLstring to write my complete formula for the BarGraph column:

="<span style='white-space:nowrap;'><b style='display:inline-block;height:20px;'>"&HTMLstring&(Jan*20/Max)&"px;' title='"&Jan&"'>"&HTMLstring&(Feb*20/Max)&"px;' title='"&Feb&"'>"&HTMLstring&(Mar*20/Max)&"px;' title='"&Mar&"'>"&HTMLstring&(Apr*20/Max)&"px;' title='"&Apr&"'>"&HTMLstring&(May*20/Max)&"px;' title='"&May&"'>"&HTMLstring&(Jun*20/Max)&"px;' title='"&Jun&"'>"&HTMLstring&(Jul*20/Max)&"px;' title='"&Jul&"'>"&HTMLstring&(Aug*20/Max)&"px;' title='"&Aug&"'>"&HTMLstring&(Sep*20/Max)&"px;' title='"&Sep&"'>"&HTMLstring&(Oct*20/Max)&"px;' title='"&Oct&"'>"&HTMLstring&(Nov*20/Max)&"px;' title='"&Nov&"'>"&HTMLstring&(Dec*20/Max)&"px;' title='"&Dec&"'></b></span>"

The solution described in this post gives a nice result, you’ll notice that I have even included a hover effect that allows to read the values directly on the sparklines. However, there are a couple limitations. First, we need to build each graph by hand. Then, with those monster formulas, we quickly reach SharePoint’s limits - 1000 characters for the formula, 2000 characters for the calculated field. For the record, my above example consumes 665 characters for the formula, and ~1700 characters for the calculated field. Note that I chose on purpose very short column names for the months, to shorten my formula. This is also the reason why I chose “b” tags.

In Wednesday’s live online workshop, you’ll have the opportunity to practice this method. We’ll also review other business scenarii, and learn other techniques allowing to build more advanced inline charts, for example using the jQuery sparklines plugin or the Google charts. To give you an idea of the result, check out these screenshots from Mark Miller.

Possibly related posts: (automatically generated)