Overview

When setting up SharePoint you might consider creating a master calendar for your team site. A master calendar is a central calendar that allows you to store companywide events to a single calendar. You can also use such a calendar to automate workspace provisioning for each event and to automate attendees list based security.

On a busy SharePoint site, a master calendar can easily become a problem. If you have more than 10 events per day, viewing these in a Calendar or list view is not very practical. A better approach in some cases might be a special list view which groups items similar to Microsoft Office Outlook.

How does Outlook group e-mails

Microsoft Office Outlook groups e-mail items in the following way:

• items received today are grouped under Today
• items received yesterday are grouped under Yesterday
• items received earlier this week are grouped under weekday name
• items received last week are grouped under Last Week
• items received two/three weeks ago are grouped under Two/Three weeks ago label
• all other items are grouped under Older label

Possible solutions

The easiest solution to solve this problem is to create a custom web part and use a 3rd party calendar component to display the items. But although this solution might look better, you will need some programming skill to create it. This web part will look different than a standard SharePoint list and it might confuse your users.

While reading a series of articles about calculated columns by Dessie Lunsford, I thought I could solve this problem by simply using calculated columns. However, the problem is you cannot use Today() as a variable in a calculated column.

There is a workaround that describes how you can overcome that problem but you would still have the problem with the fact that your calculation will not update. Dessie has a workaround to solve that, but it requires programming and you will not be able to apply this solution in a hosted environment.

So at the end I decided to use SharePoint Data View Control to format the data in Outlook style. Building a custom list to display events might be a good solution, allowing users to have a unique experience, not normally available in a SharePoint list.

Here are the prerequisites to create such a list:

• Microsoft Office SharePoint Designer 2007
• One calendar list (or any other) with some sample data
• Basic knowledge of HTML and XSLT

Calculating first day of week

To group items in an Outlook look-a-like way, you need a list with a date field. In this example I am going to use a default SharePoint calendar list. If you are going to implement this, you should test it using a SharePoint list with just a few items. SharePoint Designer performance deteriorates with a large list.

The first thing we need to do is to add one additional calculated column. It will calculate the first day of the week for each item in your list. Use the following calculation:

=DATE(YEAR([Start Time]),MONTH([Start Time]),DAY([Start Time]))+0.5-WEEKDAY(DATE(YEAR([Start Time]),MONTH([Start Time]),DAY([Start Time])),2)+1

Name the column Week Of and set column format to: Date and Time (Date only).

Note: In this article I will use a number of calculations over data fields. I have tested these with US regional settings (LCID 1033). In order to implement this solution for your region you will probably need to rewrite some calculations. Some additional problems might occur if your SharePoint server is in a different time zone.

Create a custom list view

To show grouped items we will need to create a view. You could also create a simple web-part web page to display Data View Control but “view approach” is much better because you can save your list as template and your view will be added to it. See my previous article for steps to create a list view in SharePoint Designer.

Configure grouping

The most important step of this process is to create grouping. Open Common Data View Tasks and customize grouping in the following way:

1. First group by: Week Of
• Sort properties: Descending
• Group properties: [Checked] – Show group headers
2. Then by: Start Time
• Sort properties: Descending
• Group properties: [Checked] – Show group headers

Your events will be grouped by week and start date.

Formatting dates to show weekday name

The first thing we need to change in the grid above is the appearance of the Start Time. We want it to show only weekday name. To change the output, use SharePoint Designer (Split View). Click on “Start Time:” in the design panel and corresponding part of XSLT code will be selected.

Use the following code to display only weekday name (e.g. Sunday):

Learn more about ddwrt:FormatDate function in this post. by Paul Grenier. The list of supported locale identifiers LCID is available at the Microsoft Support site.

The result: instead of full data and time, groupings will show only weekday name.

Calculate the difference between two dates and two weeks

In order to achieve Outlook style grouping you will have to calculate the difference between dates, e.g. Yesterday = Today – 1. To do this, we need to transform the date in a numeric format. In other programming languages this is easy, but when you want to do that with XSLT and SharePoint Designer things might get ugly.

The simplest solution is to use Julian Day Calculation. I already wrote about it in my previous article about KPIs.

In order to display week name correctly (Last week, Two weeks ago) we will have to calculate the difference between each Today and the value of Week Of calculated column.

You can use the following XSLT code to display the proper week label:

Your final output will look like this:

Adjusting the time filter

The final thing we need to do is adjust the filter. Outlook grouping makes sense only for past entries (in last 4 weeks). The built-in filter wizard does allow you to set such a time range, but you can do it manually. To display events in last 4 weeks use the following Select command.

Note: It must be HTML encoded before you pass it as SelectCommand. Use the HTML Encoding online tool to automatically create the encoding for you.

Conclusion

Outlook-style grouping can be used in any scenario that requires items to be displayed by time item was received. Grouping items can be extended beyond calendar. You can use this guide to customize your issues list to display items that were received in last few weeks.

What else? That’s for you to decide. Happy grouping!
Toni

Author: Toni Frankola
SharePoint Use Cases
Toni started his Web adventure in late 90’s and has been working with various web technologies ever since.

Toni is leading project engagements and managing a team of consultants specializing in Microsoft technologies. His primary focus is on Microsoft Office SharePoint and Dynamics CRM. He works at Perpetuum Mobile, a Microsoft Gold Partner from Croatia.