Trick or treat? Group items by month

OrderedMonthsIt’s that time of the year again when the unnatural becomes the norm, so let’s continue the tradition started last year with the SPELL program. Our goal today will be to display list or library items grouped by month, as shown in the screenshot.

The deal is that we don’t want any custom code or workflow here, just the regular out of the box UI features. We’ll be creating two calculated columns, called Year and Month, where we’ll insert the appropriate formulas. I chose the “Modified” column for my sample formulas, but of course any other date column would work too.

The YEAR and MONTH functions


A quick review of the SharePoint date functions gives you YEAR and MONTH that should fit the bill:

Year:  =YEAR(Modified)
Month: =MONTH(Modified)

The result is less than satisfactory though (see screenshot), as the year is displayed with a thousands separator. and months are displayed as numbers.

The magical TEXT function

MixedMonthsFurther exploration will take you to the TEXT function. It is not very well documented in SharePoint, fortunately you can rely on the Excel documentation and come out with the following formulas:
Year:  =TEXT(Modified,"yyyy")
Month: =TEXT(Modified,"mmmm")

Still not happy with the result? Right, the months are displayed in alphabetical order, not sequential order, not yet an ideal experience for our end user.

So let’s pull our last trick, and use the following formula for the calculated month:
Month: =REPT(" ",13-MONTH(Modified))&TEXT(Modified,"mmmm")

You can see the final result live on this page.

What’s the trick? We are still relying on the out of the box alphabetical sorting, but to force the order, we are adding a bunch of white spaces before the month name. The calculated Month actually contains the following values (each _ represents a white space):


Now, why don’t we see these spaces on the Web page? What makes the magic work is that when you insert multiple spaces in a Web page, the html specification says that

user agents should collapse input white space sequences

That’s it!

If you want to get really fancy, you could even use the zero-width space character. The best part is that people who edit your formula won’t even understand the trick, as the zero-width space won’t be visible (there’s however a good chance that they break your cool formula).

To take this further

You can apply this trick to other situations. A typical example is a color code. The alphabetical order will give you Green-Red-Yellow or Amber-Green-Red, you can address that by adding the appropriate leading spaces.

Be careful with this technique though: even if the rendering looks fine, the spaces are indeed stored in the field, and this might break other customizations. So this trick is better kept in a calculated column that will be exclusively used for rendering purposes.

HTML Calculated Column: solutions for SP 2010 (Part I)

This series assumes that you are familiar with the “HTML Calculated Column“.

The “HTML Calculated Column” is a powerful method that I created for SharePoint 2003 and 2007. It allows you to display in your SharePoint lists any kind of HTML: color coded text, inline charts, tooltips, embedded videos, etc.
It works in two steps:
1/ Build a HTML string, using a calculated column
2/ Render this HTML string as HTML in SharePoint pages

I am receiving more and more requests from readers who want to use it in SharePoint 2010. As I explained in a previous post, step 2 is tricky in SP 2010, due to asynchronous updates.

At this point, I don’t have a perfect answer, but I figured I’d just start sharing what I have so far. Expect this to be a long series, where I’ll publish updates as and when I validate more options. Your feedback and suggestions will be essential to guide me in my investigations.

For this first post, let’s start with an easy one. In SP 2010 you can disable asynchronous updates for lists:
– Edit Web Part
– uncheck all the AJAX options:

With the AJAX options disabled, the view will behave like it did in SharePoint 2007, and the Text to HTML script will work (well, you tell me…).

Maximum number of IF statements in a Calculated Column?

If you said seven, or eight, you spoke too fast…

I just answered a question on the Stump the Panel forum, and thought others could be interested.

The context

I recently published a new version of my tool for color coding calendars and lists. This tool generates a formula that associates each item of a choice list with a color:


For example, if the [Status] column contains Choice5, the output of the formula will be DarkCyan.

The issue

In practice, users often have more than 7 items in their choice list. For 8 choices, the formula becomes:


But if you try it, here is the message SharePoint throws back at you:
The formula contains a syntax error or is not supported.

So here is the sad truth: you cannot have more than seven nested IF statements in a calculated column.

When users discover this – by trial and error, via a forum, or from their experience with Excel – the next step for them is usually to try and break their formula across multiple columns, each containing a maximum of seven nested IFs. However, in most cases there is a simpler way.

The workaround

We have seen that a calculated column doesn’t allow more than seven nested IFs. But what if they are not nested? Let’s give it a try with my formula generator:
First pass: choice1 to choice7
Second pass: choice 8 to choice 14

Now, I just have to concatenate these two using the & symbol:


There is still something wrong as the “” (no selection) option appears twice. So I need to remove the redundant one (in orange above, don’t forget the last parenthesis) to get my final result:


And voila, a formula that fits in one calculated column, with 15 IF functions!

“That’s cool, Christophe! So… when can we see this in your formula generator?”
Ok, I’ll update it… but seriously, is it reasonable to use more than 7 colors for color coding?

Other options

For the record, IF is not the only SharePoint function allowing to build conditional formulas. See for example in this post how I used the CHOOSE function. And yes, there will be another formula generator built on CHOOSE!

HTML Calculated Column and Unicode Graphics

The number one application of the “HTML Calculated Column” method is the display of visual indicators in SharePoint lists. You’ll find many examples on my blog:
Progress bars
Color gradients
– etc.

If you haven’t used this method yet, you’ll need to learn it to take advantage of these tutorials. For the latest information on the HTML Calculated Column, start with this post… or attend one of our live online workshops.

Most examples are about color coding backgrounds or text. But what if you want to take it a little further? For example display:
– up/down arrows
– check marks
– star ratings ✭✭✭✭✭
– traffic lights
– etc.

What immediately comes to mind is to use a set of icons. But the above examples offer a lighter solution: welcome to the world of unicode graphics!

Unicode is an international standard that references character sets. This includes some graphics, see for example the page below:
For the graphics, scroll down to the “Symbols” category.

Some benefits of unicode, compared to icons:
– unlimited choice of colors, for both the graphic and the background.
– the rendering is not bound to an external image. This means better performance. Also, it makes it easier to save the SharePoint list as template for reuse in other environments (I’ll provide such a template in an upcoming post).

An example: traffic lights

As an example, here is the formula I used in a calculated column to generate the above traffic lights:

="<span style='background-color:black;font-size:24px;'><span style='padding:-10px;color:"&IF([Color]="Green","green;'>✹","gray;'>✹")&"</span><span style='padding:-10px;color:"&IF([Color]="Amber","RGB(255, 191, 0);'>✹","gray;'>✹")&"</span><span style='padding:-10px;color:"&IF([Color]="Red","red;'>✹","gray;'>✹")&"</span></span>"

Where the [Color] column can take the values Green, Amber or Red.

How about Wingdings?

Why use unicode characters, and not simply fonts like Webdings, Wingdings, or Zapf Dingbats? Those too offer graphics, but there is a downside: these fonts are not standard, and they don’t work cross-browser (and never will, from what I read). Such graphic fonts could still work for you if you are in a corporate environment where your internal policy enforces the use of Internet Explorer.

Unicode seems to work in all modern browsers. I tested it in IE7, IE8, Firefox, Chrome and Safari.

Explore your SharePoint network on Twitter with Mentionmap

Well, I think the easiest way to understand this is to see it in action!

1/Go to this page:

2/ Watch the graph build itself and start interacting with it
3/ Enter your Twitter id in the green text box, on the top left

How it works (source Asterisq): Mentionmap loads each user’s Twitter status updates (tweets) and finds the people and hashtags they talked about the most.[…] In this data visualization, mentions become connections.

Now, here is the reason why Mentionmap immediately caught my attention: the idea is not new to me, this network graph is something I already thought about in the past. I never blogged about it, but back in January I was in contact with Daniel McLaren, the author of the application, and built a proof of concept, based not on Twitter but on Technorati:
Click for example on to see which blogs linked to it (remember, the data is from January).

I dropped the idea because it didn’t work out so well with Technorati – the data was too scarce to be meaningful.

So, let me know if Mentionmap works for you! Does the graph help you discover useful connections?

And for Lord of the Rings fans: check out this experiment on timeline visualization, also by Daniel McLaren.

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.

HTML Calculated Column: minor update… and some advice

I am publishing today a minor update (v 2.1.1) of the TextToHTML script:
You’ll find the updated files under the “download” tab.

Why this update?

First, I am responding to comments from readers on the blog. Several months ago, Jim Bob Howard posted an example using my method. His post offers detailed step by step explanations, and is very popular. However, a few readers reported issues with the calendar week/day views, which didn’t render the colors in some configurations. The code I posted today aims at fixing this issue.

I am also taking the opportunity of this update to propose two separate scripts:
– a light version, for both SharePoint 2003 and 2007, that applies the rendering to displayed items only.
– a full version, SP 2007 only, that applies the rendering to displayed and hidden items (grouped views, preview pane, etc.). Be aware that this version encapsulates some of the SharePoint OOTB code.

Tell me again, what is this “HTML Calculated Column”?

If you are an end user, the HTML Calculated Column is a simple yet powerful method that allows you to enhance your SharePoint sites. Even if you don’t plan to use it, you should at least be aware of its capabilities. It many situations, it can replace or even do better than other SharePoint options, like the Data View Web Part, Excel Services or the KPIs available in MOSS. To give you an idea of what you can accomplish, take a look at these examples:
indicators for lists
color coded calendars
KPI roll-up
some other applications

For more explanations, read the original article.

To be fair, the method also has its flaws. It leverages the OOTB calculated column, but also relies on a client side script to render the HTML on the page. If you don’t feel comfortable with the “hack” part (TextToHTML script), note that you can avoid it if you use the method with a Data View Web Part, a Content Query Web Part (see above link: KPI roll-up), or an XML source (RSS, URL protocol, Web Service).

Why two different file formats, .dwp and .txt?

I am releasing the code in two formats: Web Part (.dwp) and text (.txt).

The .dwp file is for your convenience, if you only use the method sporadically, or for the first time. Simply import the Web Part to your page and you’re good to go. It is just a Content Editor Web Part containing the script.

If you plan to use the method on a regular basis, you should grab the text file instead: store the code in a central location and point to it. This will greatly facilitate future updates. For more details on this “best practice”, read this article.

A couple reminders

– Remember that the Text to HTML script must be placed below the lists you need to modify.

– The HTML you create via the calculated column must follow the HTML standards. For example for calendar views you’ll need to use inline elements (“span” for example), not block elements (like “div”).

What’s next?

I am currently working on version 3 of the script, which will make the method work on column filters, and headers of grouped views. Some people have already received intermediary versions, and I am waiting for their feedback.
I am also listening to feedback on the current version (big thanks to the readers who shared their findings!). For example, the use of the method in discussion lists, blogs or wikis will require more investigations.

How can I be informed of upcoming releases?

The preferred way is to subscribe to the RSS feeds, either for this blog or for my SharePoint downloads library. If you don’t use RSS, feel free to contact me, and I’ll send you an e-mail when v3 is out.

Visualization – calculated color gradients


If you need dynamically generated visualizations for your SharePoint data, have you considered leveraging the power of the Calculated Column? This Tuesday at 1:00pm EST, Mark Miller and I will give you all the keys to master this simple yet powerful technique. At the end of the two hour entry level workshop, you’ll be able to add color coding, KPIs and other effects – like the one described in this post – to your SharePoint lists.

Green/Yellow/Red is a standard color palette for dashboards. You can just use 3 colors to visualize discrete states, for example the status of a project (on track – drifting – late). But if your purpose is to communicate progress, or a measure on a scale, you need a larger color palette. This is for example the case in my screenshot, where the color reflects the level of completion (in %), in a tasks list.

So, how can I do this in SharePoint? Of course, my plan is to use a calculated column that will determine the color, based on the value in the [% Completed] column.

Method 1: nested IFs

This is the most basic approach:
if [% Completed]>90, select green
else if [% Completed]>80, etc.
I am not going to detail it, as we can do much better.

Method 2: CHOOSE function

The CHOOSE function is more elegant than nested IFs, and is a natural choice when dealing with multiple options. You’ll find all the explanations to achieve a color gradient in this post.

Method 3: pure calculation

So, how can we go even further? Well, colors can be identified by their name, but also by their rgb code, as each color can be generated from a combination of red, green and blue. For example:
red: rgb(255,0,0)
green: rgb(0,255,0)
blue: rgb(0,0,255)
yellow: rgb(255,255,0)
white: rgb(255,255,255)

Using these values, we can “easily” create our red/yellow/green gradient:
rgb(255,0,0) –> rgb(255,255,0) –> rgb(0,255,0)

The following formula, entered in a calculated column, will give you the rgb value for each value of the [% Completed] column:
=”rgb(“&INT(MIN(510-[% Complete]*255*2,255))&”,”&INT(MIN([% Complete]*255*2,255))&”,0)”

To obtain the visual effect as in the screenshot, use the HTML Calculated Column method, with the following formula:

="<span style='display:inline-block;position:relative; width:60px; height:14px;border:1px solid;'><span style='display:inline-block;position:relative;background-color:rgb("&INT(MIN(510-[% Complete]*255*2,255))&","&INT(MIN([% Complete]*255*2,255))&",0); width:"&([% Complete]*100)&"%;height:14px;'><span style='position:absolute; top:0px;'> "&TEXT([% Complete],"0%")&"</span></span></span>"

Note that the method works in both SharePoint 2003 and 2007.