A new location for the SharePoint User’s Toolkit

The SharePoint User’s Toolkit has a new official page:
http://sp2010.pathtosharepoint.com/SharePoint-User-Toolkit/

The old location will remain active but won’t be updated anymore.

The new site is based on SharePoint 2010 and hosted by fpweb.net. Thanks to the support of fpweb.net, I expect the new site to be more reliable and offer a better user experience.

The SharePoint User’s Toolkit is a collection of tools designed to help end users build advanced customizations. It includes for example the Easy Tabs and an Image Rotator. It will continue to grow, with new tools added every month.

Regular users of the Toolkit will notice that several solutions are not in beta anymore. I haven’t actually made any changes to the code, the beta versions are becoming official simply because no issue was reported in the past few months.

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

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

Part I: fallback to SP 2007
Part II: edit in SharePoint Designer
Part III: deal with individual Web Parts
Part IV: brute force 

At the end of part III, we were left with this question: how do I know when I need to run the Text to HTML script? 

In this post I’ll provide an easy answer: I don’t know. Updates to the page can come from the asynchronous load of a calendar view,  the asynchronous update of a list view, expanding grouped items, a response to a Web service call, etc.

Not knowing when or where updates happen, my only solution is to scan the content of the page at regular intervals. Not the most elegant or performant choice, but a bulletproof approach that will cover all cases.

My sample code:

 
<script type="text/javascript">

// Copyright (c) 2010 Christophe Humbert - Path to SharePoint

// Find all Web Parts in the page
var listWP=[],calWP=[],divs=document.getElementById("MSO_ContentTable").getElementsByTagName("div");
var count=divs.length;
for (i=0;i<count;i++) {
try {
if (divs[i].id.indexOf("WebPartWPQ")==0){
if (divs[i].innerHTML.indexOf("ViewDefault_CalendarView")>=0) {
// Calendars
calWP.push(divs[i].id);
}
else {
// Other Web Parts
listWP.push(divs[i].id);
}
}
}
catch(e){}
}

function TextToHTML(NodeSet, HTMLregexp) {
var CellContent = "";
var i=0;
while (i < NodeSet.length){
try {
CellContent = NodeSet[i].innerText || NodeSet[i].textContent;
if (HTMLregexp.test(CellContent)) {NodeSet[i].innerHTML = CellContent;}
}
catch(err){}
i=i+1;
}
}

var regexpA = new RegExp("\\s*<([a-zA-Z]*)(.|\\s)*/\\1?>\\s*$");
var regexpTD = new RegExp("^\\s*<([a-zA-Z]*)(.|\\s)*/\\1?>\\s*$");

var WP = new Object;

function UpdateWP() {
if (calWP.length>0){
for (i=0;i<calWP.length;i++) {
WP=document.getElementById(calWP[i]);
if (WP.innerHTML.indexOf("&lt\;")>=0) {TextToHTML(WP.getElementsByTagName("a"),regexpA);}
}
}
if (listWP.length>0){
for (i=0;i<listWP.length;i++) {
WP=document.getElementById(listWP[i]);
if (WP.innerHTML.indexOf("&lt\;")>=0) {TextToHTML(WP.getElementsByTagName("td"),regexpTD);}
}
}
// Check every 200 ms, forever
setTimeout("UpdateWP()",200);
}
UpdateWP();

</script>

The beginning of the script is taken from part III.

Note that I have included a quick check – if the Web Part doesn’t contain any “<” character then there’s no need to run the full Text to HTML function.

The 200 ms interval is a trade off between refresh needs (we hope that the naked eye won’t even notice the HTML strings) and browser performance. For IE 6, you may have to increase this number.

Like the other articles in this series, consider this sample script as experimental and handle with care.

In the next episodes, we’ll try to be more subtle, and provide optimized routines to address specific situations.

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

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

Part I: fallback to SP 2007
Part II: edit in SharePoint Designer
Part III: deal with individual Web Parts

My current Text to HTML script, written for SharePoint 2007, works as follows:

  1. find all hyperlinks (A tags) on the page
  2. if a tag contains an HTML string, convert it to actual HTML
  3. find all cells (TD tags) on the page
  4. if a tag contains an HTML string, convert it to actual HTML

I played with some more efficient scripts, but my tests showed that there was so little to gain that it was not worth the trouble.

In SharePoint 2010, things are different: because of asynchronous updates on the page (details here), the script needs to run more often, and performance optimizations will have a significant impact. So I chose a different approach to reduce the size of the loops:

  1. identify all the Web Parts on the page
  2. sort them out, to separate calendar Web Parts from the others
  3. for the calendar Web Parts:
    1. find all hyperlinks (A tags) in the WP
    2. if a tag contains an HTML string, convert it to actual HTML
  4. for the other Web Parts:
    1. find all cells (TD tags) in the WP
    2. if a tag contains an HTML string, convert it to actual HTML
  5. rinse and repeat steps 3 and 4 as needed

The script:


<script type="text/javascript">

// Christophe Humbert - Path to SharePoint

// Find all Web Parts in the page
var listWP=[],calWP=[],divs=document.getElementById("MSO_ContentTable").getElementsByTagName("div");
var count=divs.length;
for (i=0;i<count;i++) {
try {
if (divs[i].id.indexOf("WebPartWPQ")==0){
if (divs[i].innerHTML.indexOf("ViewDefault_CalendarView")>=0) {
// Calendars
calWP.push(divs[i].id);
}
else {
// Other Web Parts
listWP.push(divs[i].id);
}
}
}
catch(e){}
}

// Apply Text to HTML as needed

</script>

The question now is how to “apply the Text to HTML script as needed”. I’ll provide some suggestions in the next episodes. We’ll see in particular that the behavior of calendar views is different from other Web Parts.

A note for the jQuery fans

In jQuery, getting all the Web Parts can be done with a single selector:
$(“#MSO_ContentTable div[id^=’WebPartWPQ’]”)
What you need to understand is that the above line, although shorter than its JavaScript equivalent, will take the same time to execute. So there is no benefit from using jQuery here.

Case study: course compliance report (Part III)

Author: Larry Pfaff, Sr Systems Analyst, Convergys Corp.

Adding The Final Pieces

Can you see the finish line yet? As you remember in Part 1 we reviewed the requirements and expectations of the data. This output needed to be easy to read and use by all end users throughout the entire organization and most of the views returned the desired results.

One advantage of using these views was no additional scripts were required to display data in table format with one column. Once we had a direction, in Part 2, we discussed some of the obstacles and limitations we ran into and how we overcame them. Although we are very close to completion there are still some details to review and modifications to make. For this all to work we will be adding the final parts (scripts) into a Content Editor Web Parts (CEWP).

Create a view – one column
Begin creating a Standard view with the listed options. This should be fairly simple. If you need details for this you may go here.

  • Added one column, which contained all the data for the table layout.
    • Table – (table of data)

  • Select your Sort and Filter options. I set sort to Ascending by Process Total. This allowed the teams who were less compliant to show closer to the top of the view
  • No Group By
  • No Totals none available
  • Style – tested in all views
  • Item limit – your choice, this one was set at 150

CSS

Add a CEWP above your list view. This will contain the classes and formatting of the table. Be specific in the class names otherwise the formatting would be transferred to other parts of the page.

There are several references available on the net to provide you with more details: HeatherSolomon.com is one of the most commonly used.

Once you have added your CEWP to your list view you can add the CSS below. Initially my styles were off, which cause some formatting conflicts. Thanks Christophe for the style update for the table borders mentioned in Part 1.

<style type="text/css">
table.myboxed, table.myboxed TD, table.myboxed TH {
				border: 1px solid black!important;
				border-collapse: collapse;
				font-size: 10pt;
}
.twidth {
				width: 100px;
}
.comp {
				color: green;
				font-weight: bold;
}
.nc {
				color: maroon;
				font-weight: bold;
}
.e {
				color: orange;
				font-weight: bold;
}
.label {
				font-weight: bold;
}
</style>

HTML Calculated Column

The key script to display the images, color and table layout can be downloaded here with instructions for installation.

Final product looks like this:

Final thoughts

As I sat down to complete this final part in this 3 part series “Case study: course compliance report” I found IE and FF definitely don’t play well together. I also found there are several things I would do differently. With all the pieces placed together the results are astounding, but this is only one of many ways that scripts, intuitiveness and desire can produce outstanding results.

One of the changes I thought about making was to the progress bar. Currently I am in the process of showing both Complete and Exempt on the same bar as one continuous bar like this.

54% 8%

In theory this works. I can replicate it in a regular html table with div tags, but when implemented into a CC I lose either the color or the percent for the % Exempt. Will keep you posted on the progress, but for now I will continue to hack away until I figure it out.

Since the beginning of this article I was inspired to start my own blog at http://sharepointhacker.wordpress.com. Although this blog may never have any new intermediate scripts it will have scripts implemented in real world scenarios, which I feel will provide beneficial learning. For a long time I always considered myself just a hacker, never wrote any original scripts, so I thought. I would always find someone else’s code and tweak it to fit my needs until one day a blogger said to me:

“More than the script we come up with, what is important is the innovative way in which we approach a real world issue”.

Christophe

Thanks again Christophe for your support.

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

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

Part I: fallback to SP 2007
Part II: edit in SharePoint Designer

In this part II, I am going to show a straightforward method that can be applied via SharePoint Designer.

In SharePoint 2010, lists are rendered through a new component, the XSLT List View (XLV) .

Let’s add to a page a list with an HTML Calculated Column. Naturally, this column will be rendered as text. If we edit the page in SharePoint Designer, in split mode, it will look like this (click picture to enlarge):

The XSLT hierarchy is displayed at the bottom of the page. The tag that renders the field reads as follows:

<xsl:value-of  select="$thisNode/@*[name()=current()/@Name]">

This is a complicated expression, but for our purpose we don’t need to understand it. What we are going to do is tell SharePoint to interpret the field as HTML instead of just text, by adding the disable-output-escaping attribute (see picture below):

<xsl:value-of select="$thisNode/@*[name()=current()/@Name]" disable-output-escaping="yes">

 And here we go, the field is now rendered as HTML:

 The final result in the browser:

Note that the HTML rendering is done in the XSLT view itself. So if you have asynchronous updates enabled, all content refreshes will be rendered as HTML.

How about SP 2007?

We’ve actually already used this method before, in cross-list views:
– with Content Query Web Parts, as demonstrated by Eric Proshuto
– with Data View Web Parts, in my KPI roll-up series

Dealing with a standard Data View Web Part is more difficult, as explained here.

Conclusion: the HTML Calculated Column plays better with the XSLT List View in SP 2010 than it did with the Data View Web Part in SP 2007.

Your feedback and suggestions are welcome, they will guide me in my investigations. Also, let me know if you need more details on the step shown in this post.

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…).

Live demo: calendar with color coding and tooltip

This week, Mark Miller and I will be hosting our live online workshop series dedicated to calendars. Mark will start on Tuesday (“Create a SharePoint Master Calendar Solution“), and I’ll join him on Wednesday (“Add Color Coding and Tool Tips to Your SharePoint Calendars“) and Thursday (“SharePoint Calendar Resizing and Enhanced Views“).

To give you an idea of what you can expect on Wednesday, see this live demo that showcases two of the solutions I’ll be presenting (hover over an event to display the tooltip):
http://www.pathtosharepoint.com/Lists/May2010/calendar.aspx

On Thursday, we’ll review several solutions to make the calendar month view user friendly:

  • Resize a month view
  • Switch from week view to month view in a snap
  • Display weekdays only
  • Hide start time for intraday events
  • Skip the “more items” button and display all events
  • Display a calendar from another site or site collection

All these solutions work on wss v3 and MOSS, and don’t require any server side installation.

Talk to you soon!

Case study: course compliance report (Part II)

Table

Guest author: Larry Pfaff, Convergys.

I left off explaining how I went from raw data to manipulating views to achieve a table layout with usable results.
Continuing forward I will describe how to achieve this output through the use of Calculated Columns (CC) in a custom list. It’s fairly easy to build a table in a CC with some simple HTML. I built my first test table with some test data and found it worked well. I began interchanging my test data with my list data until I hit a wall. The simple HTML table worked perfectly with test data, but when all my formulas replaced my test data it failed. Initially, I thought my formulas contained errors, but upon further research I found it to be the result of a character length limitation in the CC.

Size Limitation

There isn’t much discussion on the web about the character length limitation of a CC. I actually stumbled upon it without realizing. To troubleshoot this limitation I began to break down my formula into useable chunks. By adding the useable chunks to the CC I could confirm parts of the formula were correct. Once I confirmed all the parts were correct I added them back together, but they failed again! I was receiving the error “The formula contains a syntax error or is not supported.”
I confirmed the formulas were correct piece by piece, but once I put them all together it was no good.

This was very frustrating so I reached out to our SP Admin to inquire why this is happening. He stated something about the formula was too long and I couldn’t use it. Well, I am not one that takes no for an answer. So I began trying to figure out ways to reduce the size of my formula string. This is when I had two ideas for reduction.

  1. Remove the inline styles and replace with CSS classes
  2. Go back to breaking the formula into usable chunks, leave them as separate CC’s and use a final CC to pull all the parts together.

This result was very successful. In fact later research I found a single post “Calculated Column Limits (1024 Characters)”.

Now that we have a work around for the size limitation we can discuss the fields and calculations.

My list data was updating twice daily through the use of a macro in an Access database paired with the use of scheduled tasks. Below is the list of fields used in my reporting requirements. The gray highlighted rows in the table below is my raw data automatically updated. Each CC will be described and what calculations were used, when necessary.

Field Name Field Type Function
Sr. Executive Single line of text Raw data
Name – Executive Single line of text Raw data
Completed Number Raw data
Not Completed Number Raw data
Exempt Number Raw data
Grand Total Calculated Total of the 3 above
Report Calculated not required
% Not Comp Calculated  
% Complete Calculated  
% Exempt Calculated  
Progress Totla Calculated Display progress bar
Table Calculated Display table with data
Chart Calculated Chart Image

Calculated Columns

Grand Total
Data type returned as Number with Auto decimals places. Simple field returning the summed value of three columns for a specific row.

 =SUM([Completed],[Not Completed],[Exempt]) 

Report – Progress by Executive Data type returned as Single line of text

This is not a required field, but it was useful in providing a navigation method to the source data (numbers/details). Here I used an IF statement to build a hyperlink back to the original excel file stored in a local document library. The excel files were names according to the Sr Executive, making the hyperlink easier to build dynamically.

“Ayers” the first name in the formula had the most line items. The report was so large my Access macro would fail trying to upload data into SharePoint. So I split the report into 3 separate files, this formula takes that into account and links to one of three excel files for Ayers.

 ="<DIV><a href="http://cvgsharepoint/sites/learning/DataSecurityPrivacy/Data%20Privacy%20and%20Security%20Status%20-%2520"&IF([EXEC%20Select]="Ayers,Andrea%20J",IF(OR(Name="Frank%20J%20Corso",Name="Victoria%20Perez%20Mueco"),[EXEC%20Select]&"-New",IF(OR(Name="Steve%20M%20Heffron",Name="Sukant%20Srivastava"),[EXEC%20Select]&"-New",[EXEC%20Select]&"-New")),[EXEC%20Select])&".xls' title='Report Details for "&[EXEC Select]&"' target='_blank'>Report Status Details -
  "&[EXEC Select]&".xls</a></DIV>"


figure 2.1

The next three formulas are just calculating a percentage for that value

% Complete
Data type returned as Number with 2 decimals places.

 =ROUND(([Completed]/[Grand Total]),2) 

% Not Comp Data type returned as Number with 2 decimals places.

 =ROUND(([Not Completed]/[Grand Total]),2) 

% Exempt Data type returned as Number with 2 decimals places.

 =ROUND(([Exempt]/[Grand Total]),2) 

Progress Total
Another Idea came from a progress bars and adding color.
Currently this CC totals [% Complete] and [% Exempt] as total progress. Because some new employees and upper management were exempt from taking the course Exempt could not count against the total. .

This formula worked perfectly in IE, but I found it to fail in Firefox (FF). I tried multiple changes, but still no success. Christophe brought to my attention I was missing the non breaking space discussed in this post Progress bar + color coding. Now it made sense why it wasn’t working correctly in FF. I made the simple change and confirmed no visual difference between both IE and FF. This is the resulting formula:

 ="<DIV style='position:relative;'> <div style='background-color:#ccccff;'>
<div style='background-color:#0000ff; width:"&(([% Complete]+[% Exempt])*100)&"%;'>&nbsp;</div> <div style='position:absolute;top:0px;color:white;font-weight:bold;padding:0 0 0 2px;'>"&TEXT(([% Complete]+[% Exempt]),"0%")&"</div> </div></DIV>"


figure 2.2

Table This formula is a simple table with some added styles and nested CCs. Styles and nested CCs were used to help with the character length limitation by minimizing total string length. A few trial and errors later and a successful table output. This formula was tested in both IE and FF with success. This is the resulting formula:

 ="<div><b>Sr. Exec.: </b> "&[Report]&"<table class='myboxed'><tr><th colspan='2'><b>Exec.:</b> "&[Name]&"</th><th class='twidth'>Completed</th><th class='twidth'>Not Completed</th><th class='twidth'>Exempt</th><th class='twidth'>Total</th></tr> <tr style='text-align:right'><td rowspan='2'>"&[Chart]&"</td><td  class='label'>Number</td><td class='comp'>"&[Completed]&"</td><td class='nc'>"&[Not Completed]&"</td><td class='e'>"&[Exempt]&"</td><td>"&[Grand Total]&"</td></tr> <tr style='text-align:right'><td class='label'>Percentage</td><td class='comp'>"&[% Complete]*100&"%</td><td class='nc'>"&[% Not Comp]*100&"%</td><td class='e'>"&[% Exempt]*100&"%</td><td></td></tr> <tr><td colspan='6' style='text-align:left;'>"&[Progress Total]&"</td></tr></table></div>"

Table
figure 2.3

Note: the styles used for the above classes will be provided later.

Chart
Data type returned as Single line of text. There are several types of chart providers. At the time I was developing this I only knew of google charts. This CC was created for 2 purposes. 1. to return the data as a chart and 2. to save on character length and to be shared in the Table field.

 ="<DIV><img src='http://chart.apis.google.com/chart?cht=p3&chs=150x50&chd=t:"&[% Complete]&","&[% Exempt]&","&[% Not Comp]&"&chl=Comp|Ex|NC&chco=0000FF'/></DIV>"


figure 2.4

Conclusion
Although we are not quite finished we are well on our way. In Part I we discussed the process that lead me to the table layout and styles described. In Part 2 we discussed some obstacles and work rounds to get the required results. In our final installment, Part 3, I will describe the CSS and scripts that provided the final presentation.

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