This is another post where I share technical details about a project I have been working on.
SITUATION
Sales executives within the company receive monthly commission checks based on active client employee counts and gross payroll, for clients that they have brought on. In addition, upper management needs to see high-level numbers such as active clients, active employee counts, and gross payrolls - dashboard-type information.
A easy-to-use tool to generate this information did not exist at a user level. Previously, upper management relied on IT or the Controller to generate this information and send it to them.
Through some personnel reorganization, the process for generating this report fell through the cracks. Salespeople were waiting for their commission checks for the previous month, so the project was both urgent and important.
As usual, this information needs to come from the multiple SQL Server databases the company uses to manage client information through the Darwin PEO System, a customized, version of Microsoft Great Plains for the Professional Employer Organization (PEO) industry.
TASK
I was asked to develop a tool that upper management can use to generate information themselves. Some of the application requirements and thoughts that guided the development:
- Let users pick the date range, click a button, and have the system produce a report.
- Develop the application quickly to meet the immediate needs of the organization, yet with the ability to be reused whenever upper management so desires.
- Since upper management is most comfortable with Microsoft Excel and will want the data in a workbook anyway, use Excel Visual Basic for Applications (VBA)) and ActiveX Data Objects (ADO) within a single Excel workbook to produce the results.
- Choose Excel over Access because the application overhead is low (i.e., no need for tables, forms, reports, etc.).
- Since the company doesn't mark employees and clients as inactive in the system immediately when they are terminated, define an active employee during a date range as a paid employee.
- In addition to a paid employee count, obtain a total check count and gross payroll amount for each client during the date range.
- If an employee received a check and it wasn't voided, it counts.
- Take advantage of server-side processing to achieve the best performance.
ACTIONS TAKENI first developed the SQL statement to unite data across the twelve SQL databases, based on prior knowledge of where to find information. Then I wrapped the SQL statement up in a stored procedure, with start and end dates as parameters.
After testing the procedure with different date ranges to make sure the information was accurate and made sense, I moved on to the Excel piece. I wrote code in Excel VBA and ADO to execute the stored procedure and output the results to a worksheet in the workbook.
Once I had tweaked the completed application to make sure everything ran smoothly, I e-mailed it to the director who requested it.
RESULTSWithin a few minutes I received a phone call from her, telling me how awesome I am. She also sent the application to the owner of the company so that he can run the report as often as he wants.
Now they are able to generate the information in a matter of seconds themselves, versus waiting for the Controller or someone else in IT to generate it for them; or, even worse, spend hours compiling the information themselves.
-----
Check out my other blogs:
Journey Inside My Mind Blog
Journey Inside My Mind Podcast
Get That Job!
QuotesBlog
Twitter.com/danieljohnsonjr
Related tags: daniel+johnson+jr business+intelligence sql+programming microsoft+great+plains data+mining
