Reorganizing clients in Darwin provides opportunity for SQL Server clean-up

This is a post where I share more of the technical aspects of my job as a 'conscientious programmer/analyst'.

The company I work for is reorganizing clients into new databases in the Darwin business system (a customized version of Microsft Great Plains Dynamics), and the business sees this as a great opportunity to clean up a lot of things. This view is especially shared by us who work in the IT department.

The 12 current databases are, for the most part, the same in their structure; i.e., same tables, views, stored procedures, and so forth. The company has used these multiple SQL Server databases to for specific types of clients, based on their industry classifications, etc.

Street Sweeper,
originally uploaded by itsray.

One bit similarity is in the paycodes that are used. Paycodes, for the purposes of what I refer to in this and other posts on this blog, refer to specific codes that are used to signify specific payroll transactions. The company has paycodes set up for regular and overtime hours, commissions, bonuses, mileage reimbursements, and other types of income. Additionally, there are codes for deductions, such as cash advances, uniforms, payments made by the employee for benefits and 401(k). Moreover , there are codes set up for benefits, which include an employer's contributions to health care and 401(k), and the like. Finally, there are a separate set of codes for both state taxes and local taxes.

It may be easy to imagine, then, over time, and as clients come and go, that the databases would have lots of various codes. Mirror that across 12 databases, and it becomes more complicated. Furthermore, I've discovered that the code descriptions are not consistent from database to database. That the business has a need to reorganize clients into new databases presents a great opportunity to clean things up, as a result.

Yesterday, the Director of IT and the Director of Special Projects asked for a list of active codes for active employees, across all 12 databases. I am the guy they turn to in order to get this done quickly. Because of my experience with how the databases have been set up, I usually know pretty quickly which tables to use in my SQL scripts.

In this particular case, I was interested in the Transaction History table, since it contains the three most important elements my internal customers needed: check date, transaction type, and paycode.

I initially set up the script to pull all paycodes, but I found close to 10,000 codes in use since the business started using Darwin in 2005. I checked with the Director of Special Projects, and she asked me to limit to just those codes in use since October 2007. Thankfully, that narrowed the list to just under 2000. I also included, at her request, the name of the database in which the codes were used. This proved especially helpful, since not all codes are in use in all the databases.

On my way home last night, I called into Jott to remind myself to set this up as a stored procedure.

Just another way I'm able to help keep the business engine going.

-----
Check out my other blogs:
Journey Inside My Mind Blog
Journey Inside My Mind Podcast
Get That Job!
QuotesBlog
Twitter.com/danieljohnsonjr
Utterz by danieljohnsonjr

Related tags: daniel+johnson+jr, sql+server, programming, business+intelligence, microsoft+great+plains, payroll, peo, darwin

Keeping upper management knowledgeable and salespeople paid

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 TAKEN

I 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.

RESULTS

Within 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

Reaching the point of diminishing returns on a project

Over the past month, I have been working on a project to automate payroll entry for a client that has close to 300 employees and is growing. Naturally, it is the company's best interest to get this payroll automated as much as possible.

To do this I've been developing an application to bridge the payroll info to Microsoft Great Plains Integration Manager.

One of the criteria I've used to evaluate a client's payroll is a consistent layout in a good format, so that I can tell the program where to map regular hours, overtime, cash reimbursements, etc. In some cases I'm able to tweak the payroll to make it into a layout that is consistent enough for me to automate.

This particular client is a construction company, and the only electronic version of the payroll is a text file that is not delimited. The file is basically a report from the client's system, and it has page headers and department footers, as well has breakdowns of withholding, etc. Most of that I can ignore in the program.

My colleague was able to import it into Microsoft Excel, using spaces as the delimiter. From there I saved the file as tab-delimited text, the format used in the other bridge applications I've developed.

The big problem is the inconsistent layout, which has come from using spaces to delimit the text, depending largely on how much detail is on a particular line of text. Look at the following 3 lines of text:

• E 22 Per Diem - C AZ 3 99
• E 22 Per Diem - C AZ 3 XYZ111 99
• E 22 Per Diem - C 99

In each of these lines, the payroll item is Per Deim, and the amount is 99. When you space-delimit the lines the amounts are in different columns.

This is just one major aspect of complexity that has come from my attempt to automate the payroll. After talking it over with my boss, we realize that we've come to the point of diminishing returns.

I'm off this project until there is another way to parse the payroll information and am able to move on to another project that's in the queue.

Related tags: microsoft+great+plains integration+manager payroll+automation microsoft+access vba programming application+design peo daniel+johnson+jr

Microsoft Great Plains - Integration Manager in action

The company uses a version of Microsoft Great Plains (GP) that's been customized for the PEO industry. One piece of that is Integration Manager (IM). I've talked about the bridge application a number of times before. With an Excel file from a client and, with some sophisticated programming, we create a tab-delimited text transaction file for Integration Manager. We've set up some profiles in Integration Manager to upload payroll transactions into a batch in the Great Plains system, saving a lot of time and money. In some cases we've seen a 91% drop in the amount of time it takes to run a payroll.

Over the next few weeks or so, I'll be running Integration Manager for the Payroll department. Here are some numbers for today:

Industry	Transactions	Emps
Trucking	149	51
Trucking	168	90
Restaurant	86	43
Restaurant	94	48
Restaurant	72	39
Restaurant	82	34
Restaurant	94	40
Restaurant	97	40
Restaurant	87	47
Manufacturing	173	98

1002 transactions, for 530 employees of 10 clients.

Migrating Existing Access Applications to Access 2007

This post is one of the more esoteric ones where I delve into the geeky details of some of my programming work. I know - it's really sexy, isn't it?

I have mentioned the bridge application I developed that helps make payrolls run faster, helping client employees get paid faster, and so forth, using Microsoft Access 2003 with VBA, ADO, Excel, Office, etc.

Some members of the company are starting to migrate to Office 2007, and we can see the entire organization moving there soon. A few weeks ago, I tried opening and running one of the bridge applications in Access 2007, and it bombed horribly, specifically in how I've written it to use the Office 11 FileSearch object.

I just found a couple of few documents on MSDN and TechNet that I hope will help understand what is involved in the migration:

• Transitioning Your Existing Access Applications to Access 2007
• Migration Considerations for Access 2007
• Changes in Access 2007
• Office 2007 Object Model Reference
• Migrating a VBA Solution to a Visual Studio Tools for Office Add-In

Okay, to be honest, I picked that last item because it sounds interesting.

Helping 145 employees get paid

Not my normal function, but the payroll specialist was in a pinch, so she called and asked me to run the program that loads transactions into the business system, helping 145 client employees get paid on time.