Building Dynamic BI Using Excel/SharePoint List Data – part 2

Since in Microsoft Office Excel 2007, the ability to synchronize data between Excel table and a SharePoint list is deprecated we need to download and install an add-in. The add-in then will enable us to update the information in a SharePoint list from Excel 2007.

The first thing we need to do is download and install the Excel 2007 Add-in: Synchronizing Tables with SharePoint Lists into our Microsoft Office 2007 client application.

Please consult this MSDN paper as to how you can install the add-in into Office 2007 and publish the data to a SharePoint list.

Please note that you cannot save your Excel workbook in the new Office Open XML Formats (.docx). Instead, to retain the functionality, you need to save the workbook in the Excel 97-2003 file format (.doc).

If you follow these steps you have now an Excel workbook that has a data and synced to a SharePoint list.

In my next and final blog I will explain as to how we can create the Visifire chart, to display the synced SharePoint list data, using SharePoint designer 2007.


Building Dynamic BI Using Excel/SharePoint List Data – part 1

Recently I got a request from a client, which reads as “…currently, we track our budgets and spending for the work units using a series of linked Excel spreadsheets. Admin support enters invoice information into these spreadsheets and the information gets categorized according to pre-defined budget and program codes. We need a Dashboard for our SharePoint that would allow us to present summary information for our spending (budget against spending to date) by extracting information from the spreadsheets..”

A common business issue is the inability to display meaningful Business Intelligence (BI) information in a cost-effective, efficient manner. In Microsoft Office SharePoint Server 2007, we can generate powerful graphs through Performance Point, Excel Services, and SQL Server Reporting Services (SSRS) but these technologies can come at a high cost. The costs make sense for many large companies that are setting up their own SharePoint farm and running extensive analytics. However, this may be overkill for smaller businesses who only want to generate a few basic charts.

In the coming few days I will be blogging as to how I tackled this issue by using Excel 2007 as data source then sync the Excel data to a SharePoint list. Later create a visually appealing chart using SharePoint designer 2007 and Visifire charting control. For today I will show you the end result of my solution which potentially met this client’s requirement.


SharePoint 2010 is Cool with First Class Tool

I can not believe it’s exactly one year since I promised to my self to blog about SharePoint. What I can confess is that, yes, I have been heavly involved in SharePoint 2007 apps such as Document Management, Project Management Tracking as well as Service Request Tracking apps using my favourite tool SharePoint designer 2007. I have always wanted to do a project using the SharePoint object model but never had a chance. My problem in using the object medel in SharePoint 2007 has been the bootstapping process/tools that I had to go through. Thanks to SharePoint 2010, though, that seems to be history. Visual Studio 2010 is now taking care of all the nitty gritty jobs behind the scene, which is fantastic.
The aim of my today’s blog is once again to express my excitement about the new SharePoint 2010 and the tools that are associated with it. If you are like me who is waiting eagerly for the release of SharePoint 2010 public beta, look at these new cool Channel 9 SharePoint 2010 Developer videos, which I thought were magnificient resource for people like me who want to dive into it. Last night I had a chance to go through some of these videos with my favorite instructor, Ted Pattison, and they are fantastic. My only comment is that some of the videos appear to be unreadable. Enjoy it!!