Sparklines in SharePoint (part II) – A homemade bar graph

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.

Advertisements

14 thoughts on “Sparklines in SharePoint (part II) – A homemade bar graph

  1. Pingback: Links (12/3/2009) « Steve Pietrek-Everything SharePoint/Silverlight

  2. The Charts and Graph: Part 02 (Sparklines) was very informative and helpful workshop. We are now using for our ‘Tasks and Projects’ within the hospital. Keep up the great work! Here is my question, “How do I get the colors to change when the task or project is pass the due date?” I still having problems with ‘Color Calendar’, for some reason I can’t get it to work. Can send you screen shots? I learn better if I see the process.
    Thanks,
    Frank

    • Thanks Frank, send me some screenshots of your sparklines!

      Comparing a due date with today is not an easy task in SharePoint. Search for “countdown” on my blog to find some ideas.

      Ans sure, feel free to send me more details on your calendar issue.

  3. Our hospital Command Group would like it better if the colors changed on the ‘past due date. I was asked this question “Is it possible to use this calendar like we use Outlook? The Command would like to be able to ‘color’ code the ‘All day event’ bar. Where can I send the screenshots?

  4. Hi, I followed the exact same steps here but I dont see any graph… it shows all the code and tags… under the HTMLstring and BarGraph… I have all the columns like your screenshots too… Any advice?

  5. I followed those 2 steps.
    “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.”

    And I got this HTMLstring (calculated)column and BarGraph (calculated)column with your script pasted into it and I also got the CEWP added to the list… but all I see now is that script i pasted onto the 2 columns, no graph show up. I have tried the other stuff like progress bar with colors and indicator, traffic light kind of stuff and they all works fine…

    Please advise, Thanks!

    • Sorry, I don’t know what to say…we had this same setup in the workshop two weeks ago, and it worked fine for everybody.
      Maybe check the length of your calculated result (see size limits in the post).

  6. My manager got it to work. He said all he did was copy and paste the updated code…

    I have another question though, the gradient color progress bar, I tried to put a black border around the bar but as soon as I put in the The border show up, HOWEVER, the percentage text is not showing…

    Anyway advice?

    Thanks!

  7. I can’t get this to work. I created the excet same list (title/12 months) with but when I past the formula in the claculated colum I get “The formula refers to a column that does not exist”
    (I tried it with just one month – same thing)
    Guess I’m missing something… do I need to make changes to the formula for this to work ?

Comments are closed.