Case study: course compliance report (Part I)

A couple weeks ago, Larry Pfaff shared a screenshot that impressed me. First, it made full use of my “HTML Calculated Column” method, including in a same report color coding, a pie chart and a progress bar. But also it offered an elegant, condensed layout, different from the traditional “one row per item” tables.
I asked Larry if he could tell us more about his design. After introducing the business context and providing an overview in part I,  in part II and III we’ll dive into the formulas behind this customization.

 This past year I was given a set of requirements to develop a reporting mechanism that needed to track course compliance for all company employees. Although our Learning Management System (LMS) has some reporting functionality it requires a higher permission level to run some of the basic vanilla reports and the vanilla report data is for specific groups or teams, not the entire organization. With over 70k employees I needed to find a simple useable way to report out on the course compliance (Complete, Exempt and Not Complete).

Some of the key features I kept in mind were usability, simplicity and functionality. From inception I knew I wanted a condensed view that would display the data across multiple rows that included color coding and inline charts. From a usability standpoint a table output would be easier to interpret then the standard SharePoint views and would be very helpful in meeting the simplicity and usability requirements.

For the last six plus years I have been actively using SharePoint (SP). I utilize SP whenever I can with the understanding that SP is not the solution for every request. Considering the end user population and usage of this data, SP was the solution for this project. I know SP offers multiple views and the views could include numerous sorts and filters that can be applied, but even the Table view SP offered could not organize the data on multiple rows. Once I was able to get my LMS data into SP I could manipulate the output into a table format grouping the data across multiple rows.

First I needed to automate data migration into SP. Then I needed to organize my output to obtain a table result. For the past six years I struggled on my own learning SP trying to stay ahead of the curve. One of the toughest obstacles I’ve come across is the lack of shared knowledge from within my company. When I first came across the Path to SharePoint blog this opened doors to a whole new world, starting with how to convert text to HTML by adding script to a Content Editor Web Parts (CEWP). This provided a way to change the standard SP pages without damaging or unghosting them.

Once my data automation was implemented I began trying different outputs (views). In one of my initial views (Default) I was able to display a simple colored progress bar and inline charts through the use of the HTMLcc script. This put me on the way to achieving the visual effects I was looking for. 
 

Figure 1.1

 This view was a simplistic, but not the table output I needed. It returned the data with colors and charts, but the results are still on one row and not easy to read. I began investigating other view types.

Implementing the Boxed view condensed the data some, but it was confusing because the data had no delimitation.
 

Figure 1.2

The “Boxed view, no labels” returned a cleaner results, but still missing organization.
 

Figure 1.3

 So I began thinking how this data could be displayed in a way that would provide simple results across multiple rows of a table. I began with a simple table design for the data. With the HTMLcc script already in use I knew I could convert any HTML I put into a calculated field. My first attempt to create a table with some test data was successful.

As I began testing my table across the different view types (Basic Table view) my results were less satisfying even with the addition of CSS and “HTMLcc” scripts it resulted in missing formatting. 
 

Figure 1.4

In figure 1.4, every other table was missing borders. Looking through the page source I realized the TR class “.ms-alternating” was causing a conflict. Initially I did not pursue a fix. When I presented this to Christophe he agreed the issue was the TR class and modified the table styles for a successful solution.

 Even without all the borders the results were much more manageable and easier to read. I continued testing the table layout across multiple views. Initially, during development, I was only using Internet Explorer 7, but once loaded into Firefox the results were identical.
 

Figure 1.5

Wrap up
Today we were able to achieve extraordinary results that displayed data across multiple rows in a table format. The tables contained color and inline charts something the OOB SharePoint could not achieve. The output was tested across multiple view types and across multiple browsers with identical results.  In Part 2, I will discuss the steps to creating a table output incorporating color and Google charts through the use of scripts and calculated columns, with an identical look and feel across multiple browsers.

Credits
I am a long time reader of Christophe’s blog and have always been amazed at the work he has put out. This article could not be possible if not for the work and scripts of Christophe Humbert at Path to SharePoint.

About Larry
Larry Pfaff, Sr, Systems Analyst working for Convergys Corp, a global company, based out of Jacksonville Florida delivering training and outsourcing for major corporations. I have been working with SharePoint for almost 7 years and manage many internal projects using SharePoint as the collaboration tool. I enjoy new challenges and automating existing process. I seek out cookie cutter solution and mold them to fit for “Non-Developers” working with SharePoint by developing and providing training for end user and sharing my knowledge with as many as I can.”

Update [06/07/2010]: Screenshots updated.

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!