SharePoint Tip of the Month

April 2010: Building a Real-Time Bonus Tracking and Management System Using SharePoint

Workflows. Document management. Collaboration. Flexible security model. Search. Performance Management.

These are widely recognized strong suits for the SharePoint world.

Complex database models. Custom form pages. Connections to back-end systems.

These are widely recognized as being part of the custom application development world.

The occasional IT challenge comes along that requires a hybrid of the two worlds as the solution. Abel Solutions has a very successful record combining the SharePoint world with the custom .NET application development world.

One recent client needed a web-based system that allows employees to track their expected bonuses. The company has 600 stores, located in 7 states, with a base of 2400 employees who are eligible for monthly bonuses. The employees fall into different levels of the organizational hierarchy – Regional Managers, District Managers, Store Managers, and Assistant Store Managers. The bonus structure for each of these roles is based on profitability metrics at the regional, district and store levels, respectively. Complex formulas are used to calculate bonuses based on the number of hours the employee works in addition to the profitability. Additionally, a business requirement stipulates that employees can only see bonus figures for themselves and for those who report to them.

Prior to implementing this system, the company tracked employee bonuses in a series of Excel spreadsheets. The spreadsheets lacked visibility for the employees, and provided employees with little or no mechanism to dispute the calculated bonuses.

The system that was implemented uses a combination of SharePoint functionality, custom SQL Server databases, and application pages that run inside of the SharePoint framework. The implemented system has the following key components:

• A centralized data mart that collects data on profitability and hours worked from three external systems
• Visibility of expected profits through SharePoint pages
• Ability for employees to review their hours reported to the system and request that changes be made
• SharePoint workflow to handle the approval of “hours worked” change requests
• SharePoint’s security model, tied to an Active Directory organizational structure, to control an employee’s visibility of bonus metrics

One of the biggest benefits of the approach – combining SharePoint with custom .NET development – is the savings for developing the solution. Because the user interface, security architecture, and workflow features of SharePoint are already built in, the cost of development of the system reflected over 60% savings over those projected had the system been built as a pure, custom .NET application.

This month’s tip contributed By Ben Nadler and Mark Henderson, Abel Solutions Senior SharePoint Consultants.