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.