Building an Asset Tracking Application in Excel Services – Part 1 of 5
Today's author, Dan Parish, a Program Manager on the Excel Services team, will discuss over the next few posts how he built an asset tracking application using Excel Services.
At the SharePoint Conference in Seattle in March, I did a presentation entitled "Leveraging Excel Services to Build Rich SharePoint Applications". The talk included a demonstration of how to build a SharePoint application that used Excel Services to solve a data collection scenario in which group administrators had to maintain a list of all the machines in their ever changing organization.
I promised at the end of the talk that I would put the source code that I showed online, but I've decided to take it one step further and create a series of five blog posts that will describe how to build this solution from the ground up. The last post will also include all the code for all five posts. The five posts will be:
- The Overview (this one)
- Creating the Workbook
- Creating a SharePoint List and a User Defined Function
- Building the Web Part Pages
- Creating the Archive Web Part
The solution: Data collection of an organization's machine information
In our group it can be challenging keeping track of who currently owns a given machine. While this may be a problem specific to the way we work, it is generally applicable to everyone since it really boils down to a data collection problem.
If I have a machine that I'm not using anymore (maybe I got a new one, or my responsibilities changed) I can just give it to someone else in my group who may need it instead. This exchange happens all the time for various reasons, and is great for making sure everyone has the equipment they need. However, for our group administrators, this creates a problem: they need to know where all those machines are, and they need to know if they need replacing.
There have been many solutions to this in the past: we've emailed details of all our current machines to our admin, we've entered data into SharePoint Lists, and we've even filled out InfoPath forms sent through email. There are two problems with all of these solutions though. First, there is little motivation to fill them out quickly, and second, once our admin has all of the data, they still need to put it all together and analyze it, which they usually do in Excel.
This solution addresses both of those problems by leveraging the power of SharePoint combined with Excel Services. With this solution, our admin simply needs to send out an email with a link and tell everyone to follow it and fill out their machine information. After filling out our machine information, we get feedback on how our machines compare to the rest of the organization's machines. Our admin gets her own web part page that shows who has and hasn't filled out the survey, and a listing all of the machines that have been entered, color coded to show which need upgrades and which don't. There is no need to collect and analyze the information, it's all done automatically. Further, our admin can at any time archive a copy of the report for future record keeping.
Here is what the first page looks like:
Here is what the second page looks like:
In my next post I'll detail how to create the workbook that is displayed both to the people that fill out the form and to the admin, and to analyze the machine information and display the results, both to the people that fill it out and to the admin.
