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

One Voice Walk 2007

crossposted to Journey Inside My Mind Blog

One Voice Walk 2007
Originally uploaded by danieljohnsonjr

Have you've been wondering where I've been this past month? I've been doing some online publicity as a New Media Consultant for the 4th Annual One Voice Domestic Violence Community Awareness Walk, which was held in Cincinnati, Ohio this morning. I took lots of pictures and recorded lots of audio from the event.

Hearing personal stories really moved me. I honestly felt a flood of emotions ranging from sadness, outrage, hurt, compassion, and more. It was really a lot to take in. And rightfully so. Domestic violence is an anathema to our society and must be stopped.

I really enjoyed interviewing people from the various local organizations and finding out what they are doing to help raise awareness for and to prevent domestic violence.

During the walk, I talked with other participants. In nearly all the interviews, I asked folks why they came. Really great discussions.

Do a Google search (aka 'google') "onevoicewalk", and you'll find this content as it all becomes available. For now, I encourage you to check out these specific websites:

http://onevoicewalk.org - the official website, with information about how to donate
http://flickr.com/groups/onevoicewalk - pictures taken during the event, with more to come
http://twitter.com/onevoicewalk - microblog of updates from before, during, and after the event
http://myspace.com/onevoicewalk - MySpace page
http://jimmpodcast.com - Journey Inside My Mind Podcast, where audio content from the walk will be posted

Speaking of the Journey Inside My Mind Podcast, head over there for the recent episode, Music for the One Voice Walk.

Related tags: onevoicewalk, onevoicewalk2007, domestic+violence+awareness, cincinnati, one+voice+walk, sawyer+point, jimmpodcast, jimm, journeyinsidemymind, journey+inside+my+mind, daniel+johnson+jr

Server-side processing rocks the house!

I've spent the last couple of weeks learning how to optimize the search tool we built. The short story is that we're now using a pass-through query in Access to pass parameters to stored procedures on SQL Server. Processing time has gone from about 2 minutes to less than 2 seconds.

Now that I know how to work with stored procedures, I'm thinking of how else to implement them with other types of applications.

BACKGROUND

The company has clients spread across multiple databases for various reasons; namely specific business rules (e.g., worker's comp rate, state tax rate, unemployment insurance, etc.) apply across an entire database.

Quite often someone in the company receives paperwork from an agency or client with nothing but the client employee's name or their SSN. Searching for this employee has been trial and error, going through each database at a time. Also, in many cases the person in our company just needs to see some basic information about the employee. For example, during tax season we just needed to verify a client employee's address and phone number. Hence the reason for such a tool.

We had initially designed the tool to have individual pass-through SELECT queries, uniting and applying criteria to them in Access. It was the uniting and applying criteria that has been slowing things down.

OPTIMIZING

So, I created two stored procedures with parameters: one that searches for an SSN one one that searches on a name. Each stored procedure unites the individual SELECT queries on a database separate from the other 12 that were being searched.

In VBA code, based on which criteria the user enters, we determine which stored procedure to run. We create a pass-through query to EXECUTE the stored procedure. This pass-through query then contains the search results, which is what we had with the earlier version of the tool.

PREVENTING SQL INJECTION

While reading the literature, I came across the concept of SQL injection and the need to prevent it. Since users are passing text to a SQL statment, we need to prevent the intentional or unintentional insertion of code that would prematurely terminate a text string and append a new command.

I wrote one procedure that scans what users enter in the first and last name fields for "--", "CREATE", and other such items. My coworker gave me some code to prevent users from entering other illegal characters. This double-barreled approach will prevent any SQL injection from occurring.

COMMUNICATING WITH THE MASSES

I enjoyed sharing the news of the updated tool with the rest of the company. I knew many had not even heard of it. I constructed the email the way I would a friendly blog post, including pictures.

One user immediately replied: "Awesome! Works very fast. Good job." I was contacted by two people in Accounting and Payroll who asked that I put the tool on their machines as well.

Much joy all around, I tell you. And IT looks good.

----------------
Now playing: Tim Hawkins - KidsRock
via FoxyTunes

How do you draw the line between effective promotion and spam?

This is a question I've asked my LinkedIn network, and I also ask here:

How do you draw the line between effective promotion and spam?

When marketing a brand or doing some other type of publicity for someone or something, there are effective ways to promote, and there are not so effective ways. How do identify, especially in the digital age in which we live, where the line between effective promotion (self- or otherwise) is and where you've crossed it?

Comments are open and genuinely appreciated!

Related tags: getthatjob get+that+job job+hunt job+search career+advice networking professional+development daniel+johnson+jr self+branding

crossposted to http://getthatjob.blogspot.com

JIMM 90 - Crazy Busy Sticky Hot

Listener feedback about past episodes and such. Police and ER visits, Twitter tools, AMP, the upcoming One Voice Walk. Music with a country feel. 2-year anniversary in podcasting coming up this month. Other warm, juicy goodness.

Head over to the Journey Inside My Mind Podcast to listen and read more.

Related tags: journey+inside+my+mind journeyinsidemymind jimm daniel+johnson+jr

Notes from a Performance Review

I've been going through some old paperwork this morning, and I found a copy of the only formal performance review I've received in the past 2-3 years. Some comments are particularly notable, and I want to emphasize and remember them here.

Potential

• I received an Above average potential rating, which indicates "demonstrated ability to perform a higher number of tasks at a higher level of difficulty"
• "Dan has been an invaluable asset to the IT department and the company. His development and implementation of various software solutions has allowed numerous payroll and risk management functions to be streamlined, allowing for more efficient company operations. He has the potential to handle even greater responsibility."

Strong areas of present performance

My manager had these comments:

Focused. Expert in area. Responds well to suggestions. Able to grasp organizational needs. Possesses a humility and thoughtfulness that most often attracts good will from others. Able to learn new techniques on own.

Thoughts on social media etiquette

Chris Brogan has many of us thinking about social media etiquette today, especially with respect to Twitter. I definitely recommend reading his post so that my comments here make sense:

As an early adopter of Twitter, I used to get uptight when I saw the proliferation of '@' messages. Now I get it.

My purpose in using Twitter has changed over the months since I started using it. I now use it to participate in conversation, let others know what's going on with me, point to content too large on Twitter, and make/keep in touch with friends.

I've especially enjoyed finding people in my own geographic area through TwitDir.com, and I hope that I can connect through other online means and even meet up with more friends locally in person.

I've decided to use Twitter and other social networks to for relationship-building and personal branding, so knowing how to "take the pulse" of the current climate of a social network is something of value.

Knowing where to fix a problem pays off in quick response

I was on the phone with a Payroll Specialist about 30 minutes ago. One of the company's biggest clients is a trucking company. We set them up back in January with a bridge application to facilitate payroll entry. The client emails their payroll in a spreadsheet with a consistent layout that I and my coworker mapped with the program, and their payroll needs to be done early Monday morning for a Tuesday check date.

Last week, the client left the first column blank for one of their employees, and the payroll specialist didn't find out he wasn't paid until the day after the payroll went out. She called me this morning to have me check to see if the program would do that again. In less than 10 minutes, I was able to diagnose and fix the problem so that it won't happen again.

The code looked for something in that first column before mapping the columns into specific payroll items. In this case, the data in the first column isn't as important as that in, say, the third column. I changed the code that does the test to check the third column instead.

I'm pleased that my knowledge of how this program works lets me respond so quickly and adeptly to a problem like this.

Yet Another Social Media Network?

It looks like the buzz on Twitter this weekend is about Pownce.com. Chris Brogan put up a post a short while ago entitled, "Why Join Another Social Network?"

Here are comments to what he said:

In the early days of blogging, before Blogger had its own commenting system, there was a commenting system called YAACS. YAACS stood for Yet Another Commenting System. Probably still exists today - I wouldn’t know.

I keep waiting for someone to start YASMN - Yet Another Social Media Network. All of this talk of more places to put oneself online is frustrating. I can definitely understand how it would feel cliquish.

I haven’t yet decided what to do with my Pownce invite yet, nor how to develop my Facebook profile. I feel okay about holding off for a bit and using the current tools I’m using.

I think it's interesting that, in an attempt to reach more outward and gain a wider audience, it can also come across as being exclusive. What I mean by that is the same situation that many of us in the blogging, podcasting, twittering community find ourselves in - only associating with each other. The fishbowl. The echo chamber. We're only talking to ourselves.

So I guess I don't see social media networks as a bad thing in and of themselves if it widens one's circle of influence. For me, I have a rough time of continually fragmenting myself over and over. Something I'm still trying to manage.