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.

 Enjoy