Tutorial: add color coding to your SharePoint 2007 calendar in 15 minutes

May 2010

Environment: SharePoint 2007 – wss or MOSS (no SharePoint Designer or third party application required).
Audience: confirmed end user, power user.
Permission level: design or full control.
Estimated time: 15 minutes.

0- Before you start
1- Select your colors (5 minutes)
2- Create your calculated columns (3 minutes)
3- Set up your calendar view (2 minutes)
4- Render the colors (5 minutes)
5- If you want to undo your changes
6- A note for SharePoint 2010 users

0- Before you start

To follow this tutorial, you need a calendar created in SharePoint 2007 (wss or MOSS).

 In my example, taken from Mark Miller’s community calendar, items are organized in categories:
– Online Event
– Online Workshop
– Training
– Conference
– User Group
– SharePoint Saturday
– Other 

I have created a choice column, called “Category”, to store these choices. Note that for this tutorial seven is the maximum number of choices. If your choice column has more options, only the first seven will be color coded.

A choice column is the natural option to organize items in categories. You can also use any column type that contains text or numbers, like content type, text column, calculated column. Note that columns of type Lookup will not work for this tutorial. 

Usability tip: People can distinguish up to ten different colors that are assigned to different categories, but it may be safer to use no more than five different colors for category coding. (source: rightpoint))

If your choice list includes more than seven choices, this follow up article will show you how to proceed.

Ready? Let’s see if we can get our color coded calendar in less than 15 minutes…

1- Select your colors (5 minutes)

To choose your colors, simply fill out this online form. In my example:
– choice column: Category
– choices: paste here the choices from the choice column
– display: Title (the event title will be displayed on the calendar)
– keep the default value for the other options (except if you are on a non-English SharePoint farm)
– Use the color pickers to select your colors for each choice

Note: the semi-transparent background will not be rendered in older browsers (like IE 6). 

After selecting your options, scroll down to the bottom of the form. You’ll see two formulas (pale-green text areas). Leave this page open or save your two formulas, we’ll use them in the next step. 

Note: you can choose to group the two formulas into one by unchecking the “Separate Color Column” checkbox.

While we are on this page, let’s grab the script that we’ll use later for the rendering:
– click on the Download tab.
– right-click on the last file name TextToHTMLlite-v2.1.1.txt, and select “save target as…” to save it to your computer.
– upload the file to a SharePoint library in your site or site collection. The location doesn’t matter, as long as your users have read access to the file.

Note! the download section displays 4 files; pick the last one for this tutorial.

2- Create your calculated columns

On your calendar page, select:
List > List Settings

On the List Settings page, create two calculated columns (use the “Create column” option for this):
– First column, named “Color”: paste the first formula.
– Second column: named “Display”: paste the second formula.

3- Set up your calendar view

Staying on the settings page, under Views, click on your calendar view. In the settings page, choose to display the “Display” column. Save your changes.

  

Now, go back to your calendar view, and you should get an ugly result like this:

Don’t worry, we are going to fix this in step 4.

4- Render the colors

We can now use the script we grabbed in step 1:
– go to your calendar view, and switch the Web page to edit mode:
Site Actions > Edit Page
– Click on “Add a Web Part”, and add a Content Editor Web Part to the page
– drag and drop the calendar view above the Content Editor Web Part (the order is important)
– in the Content Editor Web Part, click on “open the tool pane”
– under content link, paste the URL of your TextToHTMLlite-v2.1.1.txt file (remember, you stored it in a document library in step 1). 

If you now exit the edit mode, you should see your calendar in color.

5- If you want to undo your changes 

A key advantage of this technique is that all our customizations were made through the SharePoint UI and can easily be undone. If later you want to revert to the initial view:
– remove the Content Editor Web Part from the page
– go to the view settings page and replace Display with Title as the displayed column.
– delete the two calculated columns.

6- A note for SharePoint 2010 users

In SharePoint 2010, the script from step 4 will not work because calendars are rendered asynchronously. Alternate options can be found in this post.

Updates [09/08/2010]:
– Links now point to the new location for the SharePoint User’s Toolkit
– Link to follow up article for more than seven choices
– Note for SP 2010 users

Update [12/06/2010]:
Brendan Newell published an article based on this tutorial, with a couple additional tips. For example, here is the style in SharePoint 2010 to remove the default background from the event:

<style type="text/css">
.ms-acal-selected, .ms-acal-item {
  background:none;border:0px;
}
</style>

The article also mentions a resizing issue, which AFAIK is linked to the default calendar, not to color coding.
Check out Brendan’s article for more information!

Live demo: SharePoint May 2010 events, color coded

May2010

Today I decided to test my color coding tool on Mark Miller’s excellent SharePoint Community Calendar. I borrowed a slice (the month of May) and applied my formulas: you can compare the original calendar with my color coded version.

In the beginning of next week, I’ll publish an updated version of the tool, and a step by step tutorial.

Note that the semi-transparent background effect will not work in older browsers, like Internet Explorer 6.

Quick tip: in SharePoint, each list comes with a RSS feed. If you want to be notified of new events, you can subscribe to the Community Calendar’s RSS feed. Big thanks to Mark Miller and Natasha Felshman for maintaining such a useful resource!

Color coding: help me translate the formula!

I recently published a new version of my formula generator for color coding calendars and lists:
http://www.pathtosharepoint.com/sharepoint-user-toolkit/Pages/Color-Coding-Calendar-List.aspx

I wrote the formula for the English version of SharePoint. I’d like to offer other languages, but for this I need to know how to translate the following functions:
IF function: IF([Column name]=”string”,Yes,No)
Concatenate symbol: &

Update [April 5]: I’d also be interested in the translation of the CHOOSE, LEFT and RIGHT functions for another formula generator.

If you have a non-English version of SharePoint and know the answer, I’d appreciate if you could leave a comment telling me the language you’re using and the localized equivalents of the English functions.

Thanks for your help!

Note: the color names remain in English, as these are actually color codes recognized by the browsers.

Color coding: new formula generator, testers welcome!

I am working on a new formula generator for color coding calendars and lists, to replace the current one. If you are already using the HTML Calculated Column, I’d appreciate if you could give it a try and tell me how it goes.

You’ll find the new formula generator here:
http://www.pathtosharepoint.com/sharepoint-user-toolkit/Pages/Color-Coding-Calendar-List.aspx

The new form includes a color picker, which will allow you to choose among more than a hundred different color names supported by all major browsers.

I have updated the formula, and the rendering should be improved in calendar views. I am also including a fancy semi-transparent background. This is a risky exercise because of browser compatibility issues, so let me know if it doesn’t render as expected.

Known issue: if you choose to color the text, border and background, you’ll notice that the formula becomes quite heavy because the color calculation is repeated three times. The formula may even become too long for SharePoint, and you’ll get the following message:
The formula contains a syntax error or is not supported.
To address this issue, I am working on an option to store the color calculation in a separate field.

This week’s workshops: visualization, Gantt and Sparklines

I am presenting three live online workshops this week, with Mark Miller from EndUserSharePoint.com. As usual, each participant receives a sandbox to try out the solutions we provide.

March 23, 2010 – Inline Visualizations in SharePoint
An entry level workshop where you’ll learn how to add color and other effects to your SharePoint lists. Here is a live demo:
http://www.pathtosharepoint.com/Lists/TasksVisualization/AllItems.aspx

March 24, 2010 – SharePoint Gantt Chart Enhancements
The participants will receive 6 snippets of code. The centerpiece is the dynamic timescale (month/week/day), as demonstrated on this mockup:
http://www.pathtosharepoint.com/Pages/GanttTimeScale.aspx

March 25, 2010 – Dynamic Feedback through SharePoint and Sparkline Charts
Sparklines are a fascinating, relatively new way to communicate. More compact than traditional charts, richer than traditional indicators, they are very popular in dashboards that require to synthesize large amounts of data.
jQuery and Google will come to the rescue to help us integrate these mini-charts in SharePoint. But of course we’ll also share some homemade recipes!

For more information and to register, follow this link:
http://eusp-chartsandgraphs.eventbrite.com

My slides from SharePoint Saturday EMEA

My slide deck from the SharePoint Saturday EMEA conference  is now online. You can find it on Slideshare, or get it from the download section of my Website (under KPI roll-up). The recording of the session should be available soon, watch out for updates on EndUserSharePoint.com.

SharePoint Saturday EMEA was a fantastic event, kudos to Mark Miller and the organization team! As Mark explained in his post mortem, “there were a couple of roadblocks to making this thing actually work. First was that Europeans treasure their weekends and we had to assume that many people would not attend because it was scheduled to be a Saturday event.” Well, the Europeans proved that they had the motivation to dedicate their Saturday to SharePoint. And the Americans proved that they are always ready for action, even at 3 am!

Smart TextToHTML

First things first: if you don’t know what I mean by “TextToHTML “, you won’t get much from this post. In this case, I recommend that you start with this introduction.
In short, the TextToHTML script has two roles:
1/ find HTML strings in a SharePoint page
2/ Convert these strings into actual HTML

Yesterday, I came across a discussion between @EUSP, @webdes03 and @ebrackley on Twitter:
“TextToHTML can be a bad performer when tasked with lots of HTML; also research DVWPs”

This is a timely comment, as I am about to present at SharePoint Saturday EMEA. Let me expand on this, and provide a few hints on how to better use the HTML Calculated Column.

Remember: TextToHTML is not the only way

Right, most of the examples you’ll find on my blog rely on the TextToHTML script, embedded in a Content Editor Web Part. But there are other ways to render the HTML. In particular, the Data View Web Part, used in crosslist mode, or the Content Query Web Part can directly do the rendering. For more information, see these series:
– for MOSS
KPI roll-up in MOSS
– for SharePoint 2007 (applies to both wss and MOSS):
KPI roll-up in SharePoint (Part I)
KPI roll-up in SharePoint (Part II)

The latter will be the theme of my presentation at SharePoint Saturday.

Get a faster browser

Not always a choice, but if you can, upgrade your browser to the latest version. These days, browsers are improving by leaps and bounds, with Google leading the charge.
My tests show that the TextToHTML script is four times faster in IE 8 than in IE 7. That’s huge! Some other browsers offer even better response time.

Faster TextToHTML

If you are using the TextToHTML script, you’ve certainly got the current version from the download section. What I am making available over there is a generic script, for both SharePoint 2003 and 2007. If you are on SharePoint 2007, you can make it more specific and thus faster.

Focus on the main content

As is, the script will scan the whole page to find table cells (TD elements):

TextToHTML(document.getElementsByTagName("TD"),regexpTD);

This is a waste of time, as you just need the main content, excluding header and Quicklaunch. You can easily do this by replacing the above line with the following code:

var theMainContent = document.getElementById("MSO_MainContent");
TextToHTML(theMainContent.getElementsByTagName("TD"),regexpTD);


Use selectors

Instead of grabbing all the cells in the document, try to restrict your scope to the cells that may contain HTML Calculated Columns.

For example, Paul Grenier proposed a jQuery version of my script, which allows you to grab only certain cells. If HTML Calculated Columns are only in List View Web Parts on your page, the simple $(“td.ms-vb2”) selector should be enough. For better performance, you may want to combine it with my first advice, and focus on the main content: $(“#MSO_MainContent td.ms-vb2”).

Here again, a modern browser will give you better performance (for example if it has native tools to find elements by class name).

Place your script close to the point of consumption

Imagine that you are displaying 10 lists on your page, but only the third one is using HTML calculated columns. If you place the CEWP that contains TextToHTML at the bottom of the page, the script will go through the 10 lists. If you place the script right below the third list: when the script runs, it will be faster as it only sees 3 lists, the 7 others being displayed later.
btw placing the scripts close to the point of consumption is a good practice that also applies to other scripts, not just here. In traditional Web design, you would find all the scripts under the head section of the page, but this practice has evolved now that pages are more dynamic.

If you have other ideas, feel free to share them with me and the other readers! I’ll push some of these performance improvements in the next release of the TextToHTML script. I am also looking for volunteers to test the beta versions…

KPI roll-up in SharePoint (Part II)

After reading about the scenario and watching my live demo in part I, it is now time for you to try out KPI roll-ups for yourself.

Note: You’ll need site owner permissions on your site collection to set up the demo.

 

I am providing all the templates needed to replicate my demo in the download section. This includes 4 files:

Projects_List.stp (List template)
Each program site has a projects list. This is where the project managers will update their project status: progress and 4 health indicators.

Projects.webpart
This is a Data View Web Part that displays the visual indicators, progress bar and traffic lights.
Placed on the home page,  it will find all the project lists in the sub-tree. For example, if added to the home page of BU2, it will find all the project lists in BU2, Program 2.1, Program 2.2 and Program 2.3.


  
Projects_with_Overall_Status_=_Red.webpart 
Similar to the previous Web Part, but with an additional filter that only selects the projects in poor health (overall status = red).

Program_Site.stp 
A site template to create program sites. The template includes both the Projects list and the Project DVWP.

So you have the choice: either use the site template, or use the list template with the DVWP template. See below detailed instructions on how to use the files.

A couple comments:
– I used a site content type to manage centrally the project items. It is excluded from the templates, as the SharePoint UI doesn’t provide a way to export/import content types.
– the DVWP includes two grouping levels that are based on the site collection hierarchy.

What’s next?

I am really looking forward to your feedback. I think it is a very convenient implementation of the  “HTML Calculated Column”, as it doesn’t rely on a Content Editor Web Part. Also, the method works on both wss and MOSS, gives you access to a large choice of visual indicators, and doesn’t require images.

If everything works as advertised, please leave a comment here! If not, you may also leave a comment, but more importantly contact me so that I can help you out.

Also, let me know if you’re not clear about the scenario and the benefits of such an implementation.

In part III I’ll explain how I built the projects list.

Detailed instructions

Start by downloading the templates from the download section, under the topic “KPI roll-up”. To download a file, right click on it and select “save target as”.

To use the Projects list template (you need site owner permissions):
– On your top level site, go to the List template gallery:
Site Actions | Site Settings | Galleries | List templates
– Select Upload
– Upload the Projects_List.stp template

You can now create lists based on the ProjectsList template, on any site of your site collection:
Site Actions | Create | Custom Lists | Projects List

To use the Data View Web Parts:
– On your top level site, go to the Web Part gallery:
Site Actions | Site Settings | Galleries | List templates
– Select Upload
– Upload the two templates Projects.webpart and Projects_with_Overall_Status_=_Red.webpart

You can now add them to your Web Part pages like any other Web Part.

Alternately, you can use the Program_Site.stp site template that contains both the projects list and the Data View Web Part (you need site owner permissions):
– On your top level site, go to the site template gallery:
Site Actions | Site Settings | Galleries | Site templates
– Select Upload
– Upload the ProjectsList template

To create a site based on the template:
Site Actions | Create | Web Pages | Sites and Workspaces

Good luck!