Monday, 17 January 2011

Timesheet solution for SharePoint 2010 - Part 1

Part 1 - Design & data storage (This blog post)
Part 2 - Building the UI
Part 3 - Installation Steps
Part 4 - Final Part

Problem:  A common requirement on Intranets is to have a timesheet template on SharePoint.  There are solution for company timesheets.  I wanted to build a timesheet application that was scalable, reportable and friendly for SharePoint. 

Initial Hypothesis:  There are timesheet solutions for SharePoint and most are portable to SharePoint 2010.  Often the timesheet solutions integrate with Outlook as well.  If you have outlook this is often the best solution as users seem to accept Outlook based solutions fairly easily.

I came across, there timesheet solution was built for WSS3/MOSS, this looks like a solid solution based on SQL Server so their is no real reliance on SharePoint lists however, SharePoint surfaces the timesheets, administration and reporting requirements.  I have not implement the solution but it appears solid and expandable.

In WSS3 I think it would be very difficult to build a detailed timesheet application based on SharePoint lists due to the list size issue and the list relationship complexity required for a timesheet application.

Resolution:  I want a SharePoint Foundations solution that can be used on all SharePoint 2010 farms that can capture and administer timesheets for a business.  SPMetal has greatly improved application development when using SharePoint lists to store data in SP2010 however, due to the number of relational lists and the support for transactional CRUD operations I believe it is better to use SQL Server and store the data in relational tables.  Custom connected web parts will allow users to enter their weekly timesheets.  The solution allows for the use of BCS and External Content Type (ECT) in SP2010 for administration and reporting.

The data access technology choice is between Linq to SQL and using the Entity framework 4.0.  As SharePoint 2010 supports .NET 3.5, a separate Visual studio project/business layer would be required.  Additionally my take on these 2 competing Data access technologies is, if it's simple then use Linq to SQL, if it's complex and could change down the line use the entity framework.  I choose to use LINQ to SharePoint as I am using 7 tables to store all my timesheet task related data.  Below is the Entity Relationship Diagram (ERD) for the timesheet application.

Part 1 - Design & data storage (This blog post)
Part 2 - Building the UI
Part 3 - Installation Steps
Part 4 - Final Part

More Info:
Updated: 25/02/2011 Laura Rogers wrote this article on the time card that is useful.


Anonymous said...


Paul Beck said...

I appreciate your constructive comment however I do find your complex vernacular hard to follow perhaps a simpliar sentance would suffice next time.

Anonymous said...


Anonymous said...

Not since David Hasselhof single headedly broke down the hatred between east and west Germany leading to the removal of the Berlin Wall using his mastery of lyrics has anyone had such poignant comments - i thank-you from the bottom of my heart, the free world probably thanks-you. Your dazzling insights are appreciated.

P.S. I sincerely hope the Hoff has another number 1 hit in Germany. - paul

Tony Upward said...

"I appreciate your constructive comment however I do find your complex vernacular hard to follow perhaps a simpliar sentance would suffice next time."

Priceless lol

Anonymous said...

I think this is great. Thank you so much to the insite!

Mads Nissen said...

Hi Paul!
Interesting to follow your approach. We spent some time trying to rethink the entire concept of timesheets in a SharePoint + Exchange context. I'd love to hear your comments. Just followed you with @madsnissen on twitter:-)

Anonymous said...

please could you post the .bak file you used in your example step 1 from SQL Server so we can restore as a test? (cleared of any of your own data)



Paul Beck said...

Hi Jay,

The SQL Script will create an empty database, you can do a backup from SQL Management studio.

Staygreen Academy said...

Information was good,i like your post.
Looking forward for more on this topic
SharePoint 2013 Administration Training in Hyderabad

christyjames said...

Excel based project management templates are very affordable to use. Alongside instructions help you to carry forward the task as early as possible. Many organisations provide ready templates like for time tracking and management.
Business templates | Excel templates

Post a Comment