Discussion: How do I exclude weekends when counting the number of days between a start and end time?

The only purpose of this post is to relay a discussion that has been active for almost two years on LinkedIn, in the SharePoint User Managed Solutions group:

How do I exclude weekends when counting the number of days between a start and end time?

You would think that such a question would bring a straightforward answer. But apparently it’s not the case, and depending on their site settings people get different results.

Feel free to try out the suggestions in the discussion, and let the group know which one(s) worked for you!

The SharePoint User Managed Solutions group on LinkedIn currently has ~600 members. It’s purpose is to discuss customizations done from the user’s side (Calculated Columns, SharePoint Designer, Web services, client-side scripts, Office 365, etc.).



Interactive demo: charting in SharePoint

Last month I published a first demo based on the SPELL mini-BI solution, showcasing the ability to build multi-level drill-down charts.

Today we are taking a look at the edit mode, thanks to an interactive demo. I think the best way to understand what this is about is to simply follow the link, read the instructions on the page, and start playing with the charts. Then you’re welcome to post feedback and questions here, and I’ll try my best to respond.

The previous demo used the Google GeoChart API. This time, the pie and column charts rely on the Dojo Toolkit. Dojo offers a wide range of charts, as well as multiple themes (I only picked a handful for the live demo). It also comes with plugins like animations and tooltips (also featured in the demo).

The Matrix View is homemade, and part of SPELL.

Here again, the data comes from grouped views (3 to choose from for the demo). This time I have only set up a two level drill-down, and clicking on a chart element will directly open the SharePoint list.

I made a recent addition to the demo that I find interesting, I call it “client side calculated column”. It allows you to enter directly in the form a formula, similar to what can be done with calculated columns in SharePoint lists, just much simpler (currently only simple operations). In the demo, the “Value” field plays the role of the calculated column (see the context help for an example).

I won’t describe this advanced customization in detail on my blog, but I’ll be happy to elaborate on specific points. If you think it could address your business needs, you’re welcome to subscribe to the SPELL interest list and you’ll receive in a couple weeks a sample to test on your own site. And if you are looking for a free, entry-level charting tool, remember to check out the SharePoint User Toolkit!

Trick or Treat? Text to html, the wicked no-code way (Part II)

At the end of the previous post, we had this aha moment when we realized that rendering a field as rich text might not be that difficult, after all.

I’m back, after taking the time to set up a brand new demo to confirm our assumptions:



It works! As you guessed, just using a SharePoint Designer workflow to copy the content of the calculated column to a rich text field did the trick. For the record, here are the detailed steps:

  1. Create a list (I used the Tasks template here).
  2. Create a calculated column called cc, with the html formula. For the above demo I used calculated gradients (just did a copy/paste of the formula in the post).
  3. Create a column of type “Multiple lines of text” called HTMLcc, and specify that the type of text is “Enhanced rich text”.
  4. Create a one step workflow that copies the cc column to the HTMLcc column, every time an item is created or modified (screenshot below)
  5. We’re done!


The benefit of this approach is that we now have a clean field that renders our graphic – no ugly code around. I left the cc field visible for the purpose of the demo, but in production I would naturally make it a hidden field.

Although this looks great and easy, there are a couple gotchas to be aware of, if you decide to use this trick.

First, it is rather heavy: two fields and one workflow! And of course it won’t work for you if SharePoint Designer is not allowed in your environment (or you’ll need another way to copy cc to HTMLcc).

Second, it won’t help in views such as calendars, as these views don’t render rich text fields.

Third, as with any workflow action there’s a latency. The HTMLcc field will not refresh as quickly as other fields.

One last, important point: SharePoint is going to “sanitize” the html you copy to the HTMLcc field, and might decide to remove some of the content. This can actually be considered a good thing, as it addresses a security concern I reported in a previous post. On the other hand, it will block some script-based enhancements, like hover effects or countdowns.

Enjoy, and see you for next year’s trick: how to sort a list by month name. Happy Halloween!

Trick or Treat? Text to html, the wicked no-code way

As I am actively working on a new training program called SPELL, it recently occurred to me that I should make Halloween a special day. So let’s start the tradition, the SPELL program annual trick or treat!

I have described many times my HTML Calculated Column technique on this blog. The idea is to use a calculated column to build html content. You have certainly already tried them out, for example for color coded calendars, progress bars, or KPIs.

The catch is that SharePoint won’t naturally interpret the string as html, so extra work is needed to do the conversion – usually done using JavaScript or xslt.

Now, rendering an html string as actual html is something any rich text editor can do, why is it so complicated with SharePoint? Oh yes, here is the thing: the output of calculated columns is just plain text. Ah, if only I could change it to rich text! Or find a way to transfer the content of my calculated column to a field that understands rich text!


[To be continued]

Workaround for the SP 2010 calendar resize bug

In SharePoint 2010, the calendar views are rendered via a script on the client side. This is a significant improvement over the heavy calendars of the previous versions (2003 and 2007), and in the 2013 version client side rendering has been extended to all list views.

Unfortunately, the SP 2010 calendar also came with a bug that I reported a couple years ago. It is made of two layers – one for the grid and one for the events – and when the calendar gets resized the two layers get out of sync. Really annoying! As far as I know, Microsoft has never fixed this. I keep receiving reports of this bug from users, especially as it occurs when calendar views are combined with the Easy Tabs.

If you have to deal with this annoying behavior, here is a simple workaround: go to the Web Part settings and give the Web Part a fixed width.

Hope this helps!

Teaser: real time Business Intelligence in SharePoint


I am making progress on my SPELL project. Its main component, the SPELL JavaScript library ($P), recently reached version 0.6 and has been implemented on a couple sites. I expect to reach version 0.7 by the end of the month and version 1 by the end of the year.

To showcase some of the capabilities of the SPELL library, I have set up a live demo featuring airline ontime statistics. In this demo, you can explore data across 3 dimensions: State (first level), Month and Carrier (second level). The third level are the list items themselves.

The first level is rendered via Google GeoCharts (for v1 SPELL will have other map options). The second level – matrix view – is a custom SPELL solution that mimics an Excel pivot table (much simpler though). You saw it in the slides if you read my previous post.

“Real time” refers to the fact that data is directly pulled from the SharePoint list. The charts always read the latest updates, as opposed to traditional BI patterns where data transfers are done at regular intervals. And there is no need for intermediate storage, as would be the case for Excel Services for example.

Note: for practical reasons the demo takes some shortcuts. For example the map doesn’t collect data directly from the list.

 I collected the airline data  (January to July 2012) from the Bureau of Transportation Statistics, I’ll add August as soon as it becomes available. Obviously interacting with more than 3 million items would not be reasonable, so I have done some pre-processing to aggregate the data by month and state. This brings the number of items down to ~500/month, for a total of 3,500 since the beginning of the year.

This is the downside of this solution: because it directly interacts with lists, it is subject to SharePoint’s usual limitations, for example the 5000 item threshold on views in SP 2010. Technically the tool could work with much bigger data sets, but this would require some adjustments and might not be desirable.

The solution showcased here has no ambition to compete with well established tools like PerformancePoint, SSRS or even Excel, but rather to offer a lightweight alternative. It presents a number of advantages that make it attractive :

  • Compatible with SharePoint 2007, 2010, Office 365 (evaluation of SP 2013 in progress)
  • Can easily be implemented by an experienced end user. No server side install, files are simply uploaded to a document library.
  • Direct, real time access to the SharePoint data (which means for example that it follows site, list and item level permissions).
  • Pure html (no Flash or Silverlight), accessible from both desktop and mobile devices.

This could for example be the ideal tool for a mid-size matrix organization, with multiple teams working on multiple programs. Managers could monitor the organization health, use the matrix view to analyze trends (what brings my organization up/down, a specific project or a specific team?), and finally access the items themselves.

My slides from the March 2011 San Diego SharePoint User Group meeting

No, there’s no typo in the title, I am posting today the slides I presented 18 months ago in San Diego. My main purpose is to use them as reference in future articles, as it turns out that their content is very consistent with what I’ve been doing since then, and also with the way SharePoint has been evolving from the 2007 version to the 2013 version.

The slides feature in particular the Matrix View, a simplified form of pivot table. I’ll talk about it in future posts, but you can already visit this page for a live demo (click on a state).

Some of the other tools can be downloaded from the SharePoint User Toolkit.

For some reason the embedded slides below are distorted. They are better rendered in the original location on Slideshare.

Security concerns with the HTML Calculated Column

The HTML Calculated Column is certainly my most popular technique. It is mainly used to color code calendars, but its scope is much larger than that. The technique is especially useful when you… actually do calculations, for example in these progress bars with color gradients. You can see some other examples in this live demo, and in many other live demos and posts in this blog. Any html tag could be added using this technique.

For the record, the original post is outdated (and so are the hundreds of articles that copied it). You’ll need to read the more recent ones for updates on SP 2007 and SP 2010.

Did I just say any html tag? mmm… so how about script tags? Does it mean that a user could push a malicious script to your computer? Well, the short answer is yes. This is something I have already discussed in the past, in private or in forums, but I never posted a detailed explanation on this blog – until today.

The question was actually raised in a recent project I worked on. I accidentally stumbled upon an experiment that the IT team ran behind my back (well, to be fair, maybe they just didn’t know I was the author of the code). Let me tell you the story.

For the sake of simplicity, I’ll use these short snippets instead of the actual code:

  • our malicious script: alert(“Boo!”)
    The script simply opens a pop-up that says Boo!, be prepared to be scared…
  • our calculated column: =”<div style=’font-weight:bold;’>”&Title&”</div>”
    This will render the Title in bold. It is actually the example used in the original post from September 2008.

For example if Title is Chandler, the value of the calculated column will be
“<div style=’font-weight:bold;’>Chandler</div>”
and the page will render it as Chandler.

Now, here is what the IT team tested: in the Title column, they entered:

In this case, the calculated column returns:
“<div style=’font-weight:bold;’><script>alert(“Boo!”);</script></div>”

What happens when you use my technique? You might expect to get a “Boo!” alert, but in practice… nothing! The code simply doesn’t execute. This is due to the technique I use in the HTML Calculated Column: script tags are added (just like other tags), but do not run.

I guess IT was satisfied with the test, as they agreed to leave my code on the page.

What they overlooked is that there are actually other ways than script tags to add code to the page. Imagine for example that you enter the following string in the Title field:
<img src=”/_layouts/images/blank.gif” onload=”alert(‘Boo!’)” />

This time, when you use my technique the above code loads the image (blank.gif), and once it’s loaded it triggers the code in the onload event. Wicked!

As I said, there’s nothing new here. I even have a blog post from 2009 that explains it:

So is there really a security risk here? In theory, yes. In practice, only people who have contributor access will be able to inject code, so in collaborative environments the risk should be very low. Also, you’ll be very limited in the amount of code you can put (a text column like Title only accepts up to 255 characters). However, I would advise against using the HTML Calculated Column in open environments, for example if anonymous users have write access or if your list is mail enabled (and by the way in such cases you’ll have other concerns, not just with the HTML Calculated Column).

In the next few weeks, I’ll publish two techniques that avoid the security risk described in this post.