Sean Cleaver - Software IT Consultant, Hastings

Create a Timesheet Application with SharePoint and Exchange

Overview

This example shows how you can build a real-time (almost) timesheet reporting system by using Exchange Server, SQL Server and SharePoint Server along with Data Synchronisation Studio. Each employee will record their time within their Exchange calendar and define the entry as a Work Hours category. Data Sync will then aggregate all the entries from each user calendar and present a summary view within SharePoint.

Preview of Summary Page

Preview of Exchange Calendar Item

Recording time is as simple as creating a calendar item and setting a few properties.

• Set the item Category to Work Hours
• Set the value of Location to the Job identification Code
• Set the body of the message to the description of the work completed

This data will then be synchronised via Data Synchronisation Studio to a SQL Server Table which can then be used for reporting.

 

Step 1 - Create Database

Create a SQL database with SQL Server called TimesheetDB to store the timesheet records from Exchange Server and job detail Records.

 

USE [TimesheetDB]
GO
SET QUOTED_IDENTIFIER OFF
GO
SET ANSI_NULLS OFF
GO
CREATE TABLE [dbo].[Job] (
    [DSID] uniqueidentifier DEFAULT(newid()) NOT NULL,
    [Code] nvarchar(255) NOT NULL,
    [Company] nvarchar(255) NOT NULL,
    [Name] nvarchar(255) NOT NULL,
    [Quote] int NOT NULL,
    [Active] int NOT NULL
)
GO
ALTER TABLE [dbo].[Job] ADD CONSTRAINT [PK_1AD0E075] PRIMARY KEY CLUSTERED ([DSID] ASC)
GO
SET QUOTED_IDENTIFIER OFF
GO
SET ANSI_NULLS OFF
GO
CREATE TABLE [dbo].[Billing] (
    [CalendarID] nvarchar(50) NOT NULL,
    [CreationDate] datetime NULL,
    [LastModified] datetime NULL,
    [StartDate] datetime NULL,
    [EndDate] datetime NULL,
    [Duration] int NULL,
    [Importance] ntext NULL,
    [Priority] int NULL,
    [Subject] nvarchar(255) NULL,
    [Body] ntext NULL,
    [Location] nvarchar(255) NULL,
    [Category] nvarchar(255) NULL,
    [MailBoxName] nvarchar(255) NULL
)
GO
ALTER TABLE [dbo].[Billing] ADD CONSTRAINT [PK_2FA475F7] PRIMARY KEY CLUSTERED ([CalendarID] ASC)
GO
SET QUOTED_IDENTIFIER ON
GO
SET ANSI_NULLS ON
GO
CREATE VIEW Timesheet_Stats

AS

SELECT
    [Billing].Location AS Code,
    Job.Company,
    Job.Name,
    Job.Quote,
    ROUND(SUM(CAST(Billing.Duration as float))/60/60, 2) AS Total,
    ROUND(CAST(LAST_MONTH.Duration as float)/60/60, 2) AS [Last Month],
    ROUND(CAST(THIS_MONTH.Duration as float)/60/60, 2) AS [This Month]
FROM [Billing]
INNER JOIN Job ON Job.Code = [Billing].Location
LEFT JOIN ( SELECT [Location], SUM([Duration]) AS [Duration] FROM [dbo].[Billing] WHERE Month([StartDate]) = Month(DATEADD(m, -1, GETDATE())) GROUP BY [Location]) LAST_MONTH ON LAST_MONTH.Location = [Billing].[Location]
LEFT JOIN ( SELECT [Location], SUM([Duration]) AS [Duration] FROM [dbo].[Billing] WHERE Month([StartDate]) = Month(GETDATE()) GROUP BY [Location]) THIS_MONTH ON THIS_MONTH.Location = [Billing].[Location]
GROUP BY [Billing].Location, Job.Company, Job.Name, Job.Quote, Job.Active, LAST_MONTH.Duration, THIS_MONTH.Duration
HAVING Job.Active = 0
GO

Step 2 - Create Exchange Sync Project

To connect Data Sync to your exchange server you will need to define a control file that contains the connection details to your server and the mailboxes that should be included in the synchronisation.

Create an Xml file call Timesheet.xml that is similar to the example below.

 

<Exchange>
  <Server>https://yourserver/exchange</Server>
  <Username></Username>
  <Password></Password>
  <Domain></Domain>
  <FormsAuthentication>False</FormsAuthentication>
  <Calendar>#SERVER#/#MAILBOX#/Calendar</Calendar>
  <DaysBackToReturn>90</DaysBackToReturn>
  <Category>Work Hours</Category>
  <MailBoxes>
    <MailBox>user1@domain.com</MailBox>
    <MailBox>user2@domain.com</MailBox>
    <MailBox>user3@domain.com</MailBox>
  </MailBoxes>
</Exchange>

 

Now connect Data Source A of Data Sync to this Xml file via the Exchange Calendars data provider.

 

 

Then connect Data source B to the Billing Table in the Timesheet Database and your Data Sync project should now look like this.

 

Step 3 - Create Job List

Connect Data Sync to the Job Table and then create a SharePoint List called Job to Sync the table.

Screenshot of the Job project in Data Sync

 

The Job list should be configured for 2-way sync so that new Jobs added to the SharePoint List are synchronised with the Database. To do this go to the File menu and choose properties and set the sync option to SyncAandB.

 

Step 4 - Create Summary List

Connect Data Sync to the Timesheet_Stats View and then create a SharePoint List called Summary to Sync the view.

Step 5 - Setup Schedule

This project requires that the synchronisation is run in a particular order for example.

• Sync Job List
• Sync Exchange Data
• Sync Summary List

To execute the synchronisation in this order you need to setup the Simego.DataSync.Run.exe command line application like this

 

Simego.DataSync.Run.exe Job.dsprj Exchange.dsprj Summary.dsprj

 

Now when the Synchronisation process is started each project will be processed in order.

Note - Mailbox Security

For data sync to read the entries from each user calendar the account that is used to run the sync requires Reviewer access at the Mailbox and Calendar level. Which can be configured via the Outlook Client.

How to Synchronise a SharePoint Document Library with a File Share

Overview

This article describes how to use Data Synchronisation Studio to Synchronise a File Share with a SharePoint Document Library. This could be useful during a migration from a traditional file server to SharePoint or to just publish internal documents to SharePoint.

Step 1 - Install Custom Web Services Solution

Download our custom SharePoint Web Services solution below and deploy to your SharePoint environment. To install the solution into your SharePoint environment use the STSADM application for example

STSADM.EXE -o addsolution -filename Simego.SharePoint.Services.wsp

Then deploy the solution from the SharePoint Central Administration console.

This custom web service allows us to update the Modified Timestamp on files that are synchronised with SharePoint.

Download SharePoint Solution Package

Step 2 - Connect to File Share

Enter the Path to your file share in the connection dialog for datasource A you can also define an ignore pattern list and optionally choose to recuirse through all sub directores.

• 

  Connect File Share Data Source

Step 3 - Connect to the Destination SharePoint Document Library

Enter the Url to your SharePoint Site in the connection dialog for datasource B.

• 

  1 - Connect to SharePoint Document Library Data Source
• 

  2 - Connect to SharePoint Document Library Data Source

Step 3 - Disable Compare on File Length

Set the compare option on the File Length Property to False to disable comparing the file length as the value reported by SharePoint is different to the file system length.

• 

  Data Sync Schema Map

Step 4 - Compare A->B and Synchronise

Run an initial compare to look for the new documents that need to be added to SharePoint in this case we should see all documents as the SharePoint list contains no documents.

• 

  Compare Results
• 

  Start a Synchronisation
• 

  Document Library in SharePoint Populated

Other Options

• Synchronise Document Library to a File Share by reversing this process.
• Synchronise Documents between different SharePoint Environments or Site Collections by connecting two SharePoint Document Libraries.
• Synchronise two file shares by connecting two different file shares.

Data Synchronisation Studio - Update

The latest build 1.0.156 of Data Synchronisation Studiois now avaliable for download from our web site.

This version includes fixes and improvements from customer feedback and support for the new MS CRM 4.0 add-on data provider.

We have also added a new Wizard that allows you to add new columns to an existing SharePoint List such as the DSID synchronisation column.

Also Create Scheduled Task is now working on Server 2008 x64

And finally you can now connect to SharePoint instances that are using Forms Based Authentication or are behind ISA server.

Use Custom AD Attributes with Data Sync AD Provider

Hi,

 

We have updated the Data Sync AD provider to allow you to add additional properties without the need for us to build a specific providers.

 

When you re-open your AD project file you should notice a new file is created called projectname.properties.xml in the same directory this file contains all the properties that are available to you and is a simple xml document.

 

For example :-

 

<?xml version="1.0" encoding="utf-8"?>

<Properties>

  <Property Name="objectguid" DisplayName="DSID" />

  <Property Name="displayname" DisplayName="Display Name" Updateable="True" />

  <Property Name="givenname" DisplayName="First Name" Updateable="True" />

  <Property Name="sn" DisplayName="Surname" Updateable="True" />

  <Property Name="telephonenumber" DisplayName="Telephone Number" Updateable="True" />

  <Property Name="mobile" DisplayName="Mobile Number" Updateable="True" />

  <Property Name="facsimiletelephonenumber" DisplayName="Fax Number" Updateable="True" />

  <Property Name="ipphone" DisplayName="IP Phone Number" Updateable="True" />

  <Property Name="postalcode" DisplayName="Postal Code" Updateable="True" />

  <Property Name="co" DisplayName="Country" Updateable="True" />

  <Property Name="c" DisplayName="Country Code" Updateable="True" />

  <Property Name="postofficebox" DisplayName="POBox" Updateable="True" />

  <Property Name="wwwhomepage" DisplayName="Homepage" Updateable="True" />

  <Property Name="objectcategory" DisplayName="Category" />

  <Property Name="distinguishedname" DisplayName="Distinguished Name" />

  <Property Name="adspath" DisplayName="AD Path" />

  <Property Name="samaccountname" DisplayName="Account Name" />

  <Property Name="userprincipalname" DisplayName="Logon Name" />

</Properties>

  

All you need do is edit this file to add the additional properties where Name=the name of the property in Active Directory. Then re-open the project. You can then drag the new property from the Schema window onto the blank area of the Map to add the additional column.

 

For example add these properties to your file.

 

  <Property Name="employeeID" DisplayName="Employee ID" />

  <Property Name="employeeNumber" DisplayName="Employee Number" />

 

This new feature is in Data Sync Version 1.0.44 and higher the latest version can be downloaded from http://www.simego.com/download or http://www.simego.com/beta

Adding SilverLight Charts to SharePoint

Would you believe it! After building the SharePoint Chart solution to work with Flash Charts I came across these SilverLight Charts from VISIFire which are just fantastic. So I have updated the code to work with these charts.

 

Remember to get SilverLight to work on your server you need to add the MIME Type "application/x-silverlight-app" for the XAP extension.

 

 

The Code adds a Web Part to SharePoint for displaying a Bar Chart from Data Stored within a SharePoint List.

Download Simego.SilverLight.Charts.zip (158.2K)

Create Dynamic Flash Charts with SharePoint

Previously I had detailed how to create fancy dashboard pages in SharePoint by using free flash charts from Fusion Charts. Now I have created a Web Part that does all the hard work to dynamically display a Bar Chart from Data held within a SharePoint List which could then be easily modified to use BDC Data or SQL Server etc.

 

Below shows how the Chart might look on your SharePoint page you simply add the Web part to the page and setup the List to get the data from.

Chart Web Part

Chart Web Part Design View

 

 

This solution assumes that the Flash SWF file for the Chart in located in a Charts document library within the current Web.

You may download the source code for this solution to see how to build the solution yourself.

Download Simego.Charts.zip (8.8K)

Billing Tracking System with Exchange and SharePoint

As an owner of a small IT business I am always looking for ways to better manage my time & billing tracking. I also want to track almost real-time how much time my other staff have used against budget. Exchange provides an easy way for every one to record their time as Calendar entries by using the Location to store a Job reference code and set the Category to Work Hours. So no matter where everybody is working they can update their exchange calendar with their times and description and I can then synchronise the data into a SQL Database and summarise the Data in SharePoint.

Below is a Screenshot showing the Job Status in SharePoint and the Jobs that are currently active. These Jobs can be editied in SharePoint and synchronised back to SQL Server with Data Sync.

Requirements

• Exchange Server 2007 User Accounts
• SharePoint Server 2007 (WSS)
• SQL Server Express 2005
• Data Synchronisation Studio

1) Configure Data Synchronisation Studio to download Billing Entries from Exchange Calendars into SQL Server.

The ExchangeGroupCalendarDataSourceReader custom provider can read values from multiple exchange mailbox calendars and takes a single xml configuration file for it's settings.

Example Xml File

<Exchange>
  <Server>https://myserver/exchange</Server>
  <Username>user@simego.com</Username>
  <Password></Password>
  <Domain></Domain>
  <FormsAuthentication>True</FormsAuthentication>
  <Calendar>#SERVER#/#MAILBOX#/Calendar</Calendar>
  <DaysBackToReturn>90</DaysBackToReturn>
  <Category>Work Hours</Category>
  <MailBoxes>
    <MailBox>user1@simego.com</MailBox>
    <MailBox>user2@simego.com</MailBox>   
  </MailBoxes>
</Exchange>

Crack open Data Sync and connect to the Exchange Store with the configuration xml file and create a SQL destination table called Billing.

2) Add a Table to SQL Server to list the JOB Codes we have in the System

CREATE TABLE [dbo].[Job] (
    [DSID] uniqueidentifier DEFAULT(newid()) NOT NULL,
    [Code] nvarchar(20) NOT NULL,
    [Company] nvarchar(255) NOT NULL,
    [Name] nvarchar(255) NOT NULL,
    [Quote] int DEFAULT(0) NOT NULL,
    [Active] int DEFAULT(0) NOT NULL
)

3) Create a View in SQL to show the calculated totals

CREATE VIEW Billing_Stats
AS
SELECT Location AS Code, Job.Company, Job.Name, Job.Quote, SUM(Duration)/60/60 AS Total
FROM [dbo].[Billing]
INNER JOIN Job ON Job.Code = Billing.Location
GROUP BY Location, Job.Company, Job.Name, Job.Quote, Job.Active
HAVING Job.Active = 0

4) Configure Data Sync to Synchronise the JOB Codes Table to a SharePoint List

5) Configure Data Sync to Synchronise the Calculated Totals View to a SharePoint List

This new Exchange Data Provider is currently in private beta with a few clients if you would like to know more please contact sales[at]simego.com.

Sync AD Users to SharePoint - 2 Way

So some of you have requested support to provide 2-Way Synchronization of AD Users between a SharePoint List and Active Directory. The good news is that the AD Provider for Data Synchronization Studio now supports this.

So you can effectively publish your AD Users to an Intranet Site to create a "Staff List" or "Telephone List" and then from this you can now allow your staff to update the personal information stored in the AD themselves by just simply editing the records in SharePoint. Then when the Synchronization occurs all changes are applied to the AD. There are a few limitations you can't create new AD Users this way and you can't delete users from the AD and certain properties are not updateable such as Member-Of etc.

This is part of release 1.0.48 and is available now from the download page.

2-Way List Sync SQL Server <-> SharePoint

Data Synchronization Studio supports 2 Way Synchronisation of data between SQL Server and SharePoint if you follow a couple of guidelines.

The main thing to consider is to use a GUID (uniqueidentifier) as the Primary Key (Unique Constraint) and if you call this column DSID then the Mapping will be easier.

Step 1 is to create a Table in your database that follows this schema like below.

CREATE TABLE [dbo].[ProductList] (
    [DSID] uniqueidentifier DEFAULT(newid()) NOT NULL,
    [CompanyName] nvarchar(40) NOT NULL,
    [ProductName] nvarchar(40) NOT NULL,
    [CategoryName] nvarchar(15) NOT NULL,
    [QuantityPerUnit] nvarchar(50) NULL,
    [UnitPrice] decimal(18, 0) DEFAULT(0) NULL,
    [UnitsInStock] smallint DEFAULT(0) NULL,
    [UnitsOnOrder] smallint DEFAULT(0) NULL
)
GO
ALTER TABLE [dbo].[ProductList] ADD CONSTRAINT [PK_ProductList_2FE911D7] PRIMARY KEY CLUSTERED ([DSID] ASC)
GO

Step 2 is to fill your table with the Data you would like to Synchronize.

Step 3 You will then need to fire up Data Synchronization Studio and create a List in SharePoint that will map to this table this should look something like the picture below.

 

Important: You should have a Column DSID mapped to a SharePoint column DSID of type GUID and it should be marked as the Unique Column.

 

 

Step 4 Modify the Project File (*.dsprj) to support 2-way sync by default the 2-way sync is disabled to prevent accidental deletion of data.

Open the *.dsprj file in your favourite text editor (notepad.exe) and look for the Xml Element Project you should see an Attribute called SyncOption change this value to be SyncAandB.

So the Project Element should look like this.

<Project Name="" Version="" SyncOption="SyncAandB">

You can now drop the project file into the Synchronization Service project folder (C:\Windows\DataSyncProjects) and start the service. Now you can make changes in both SQL Server and SharePoint and the change will be replicated in each system.

Create Fancy Flash Charts on SharePoint

To add fancy charts to SharePoint pages driven by data from either SharePoint, BDC or some other source is actually very easy.

You will need some good Flash Charts I recommend starting with FusionFreeCharts

Step 1 - Create a SharePoint Docment Library to hold the Flash SWF Files and upload them.

Step 2 - Create a Web Part to Generate the Xml Data For the Flash Chart

Using Visual Studio 2005 and the SharePoint Extensions Create a New WebPart and generate the Xml for the Chart in the Render Method.

Download ChartData.zip

Step 3 - Create a Web Page with SharePoint Designer to output the Xml Data to the Chart

This page should have next to no content i.e. no HTML Markup it would look something like this...

<%@ Page Language="C#" %>
<%@ Register Tagprefix="CD" Namespace="ChartDataProvider" Assembly="ChartData, Version=1.0.0.0, Culture=neutral, PublicKeyToken=9f4da00116c38ec5" %>
<CD:ChartDataProvider runat="server" id="ChartData1" __WebPartId="{BFE5A1BD-85F0-4E13-8756-6B223ECEBF33}" />

Step 4 - Add a Content Editor Web Part to a Page and Configure the Flash Chart.

Edit the Source of the WebPart to be something like this...

<object classid="clsid:D27CDB6E-AE6D-11cf-96B8-444553540000" codebase=http://download.macromedia.com/pub/shockwave/cabs/flash/swflash.cab#version=6,0,0,0" width="600" height="400" >
    <param name="movie" value="FlashCharts/FCF_MSColumn3D.swf" />
    <param name="FlashVars" value="&dataURL=ChartData.aspx&chartWidth=600&chartHeight=400">
    <param name="quality" value="high" />
    <embed src="FlashCharts/FCF_MSColumn3D.swf" width="600" height="400" flashVars="&dataURL=ChartData.aspx&chartWidth=600&chartHeight=400" quality="high" type="application/x-shockwave-flash" pluginspage="http://www.macromedia.com/go/getflashplayer" />
  </object>

That's it real fancy charts in SharePoint you could do the same with Silverlight but I don't know of any free good Chart Controls.

Manage SSIS Configurations in SharePoint with DataSync Studio

DataSync Studio allows you to publish SQL Tables to SharePoint and keep them in sync with each other. So while I was working on a SSIS Project I thought it would be really cool to manage the Configuration Settings in SharePoint.

So the first thing is to use Data Sync Studio to Connect the SQL Table to SharePoint and define the mapping between the List Columns.

Then configure the List Synchronisation Service and get it running, next just save the Data Sync Project file into the Sync Service Monitor Directory and your done.

Now it's really easy for me to create SharePoint views for each of the SSIS Configurations that I have i.e. 'DEV', 'UAT', 'LIVE' and I can also enable Version Control and Configure who has access to update the items. Now I don't need to create an application to edit the settings or give someone instructions on how to use SQL Management Studio.

To be part of the BETA Program for DataSync Studio please drop me a line at sales[at]simego.com

DataSync Studio for SharePoint List Sync

Soon to be available is a solution that can easily import data from a SQL Source into your SharePoint Lists and allow you to keep both sources of data in Sync with each other.

All you need to is point the tool at your SQL Table and then either create a List from that or Map the fields to an existing List.

Screenshot

For more information please contact sales[at]simego.com

SharePoint QueryString(URL) Filter WebPart

WSS only ships with a few basic Web Parts one that I miss from MOSS is the Query String URL Filter Web Part this allows you to easily filter lists of data based on a parameter supplied in the URL. i.e. Filter a List of Invoices based on Client ID. I want to use WSS for more than just holding documents so I thought lets build one.

To deploy the Web Part solution from the Server run the following command.

STSADM.EXE -o addsolution -filename FilterWebPart.wsp

You then need to Deploy the solution from Central Admin Console.

Now on your site you need to activate the Feature

Next edit a page and add the "Query String Filter" Web Part

To Configure the Web Part you need to Choose the name of the URL Parameter that will be passed to your Filtered List i.e. ID or ClientID etc.

Now you just need to connect your web parts from a List or Document Library Web Part choose Connections -> Get Filter Values From to connect the parts together.

Now you should have something working like this.

Download Web Part from here

Download FilterWebPart.zip (4.1K)

SharePoint List Data Synchronization

Been looking at an alternative to the BDC which is both easy to set-up whilst compatible with WSS as well as MOSS.

So building on our SQL Database Differences Technology we have been building a SharePoint (or any other datasource) Synchronization application framework.

The idea is that you can now leverage all the features of SharePoint to manage your business applications static data or ease the migration from an old application to SharePoint.   

For Example with this Sync  Application you can expose your bespoke applications Leaf Tables in SharePoint and then receive Security, Content Approval, Workflow, Versioning, Auditing etc on your data.   i.e. You could maintain a list of Currency Conversion Rates for your application in SharePoint and then ensure only valid users can update the data and see a full log of who updated the values and when.

The Synchronization is two-way and column level so that if you update a column value in SharePoint then only that Column is updated in the SQL Database and the opposite is also true.

Were also planning for an Active Directory Adapter for the Synchronization to publish User Accounts/Network Computers etc to WSS to manage a Staff Telephone/Contact list etc.

If you would like to know more and have a project which could benefit from this technology then please drop me a line at sales[at]simego.com

MOSS BDC Design Studio Version 1.1 Released

We are pleased to announce the release of an updated version of our MOSS BDC Design Tool. This latest update adds the following features :-

• Wizards for Creating Entities
• Support for Creating User Defined Actions
• Updated User Interface with Docking Windows
• Improved Method Auto-generation
• Create BDC Connection to virtually any data source (Progress, Navision, DB2, Oracle and more...) via SQL Server Linked Servers and Entity Creation from SQL Statements

This is a FREE update to all existing customers and new customers can download a FREE 14-Day Trial version from the Web Site.

Download MOSS BDC Design Studio