When the Senior Manager of Systems Development at Charles Jones, LLC realized that his company’s monthly sales reporting system threatened to curtail his dream of golfing on Friday afternoons he turned to Perl for help. As we would expect, Perl was right there when he needed it. Here’s Rob Lauer’s story about how Perl helped him automate and spiff up his company’s sales reports.
Perl Keeps Golf Dream Alive
Our company is a public records search company that cut it’s teeth on computers made by a company known as GE. I’m not sure if our president got a discount on his washer and dryer for purchasing the computer from GE but I do know that the man was brilliant in his recognition of the power of computer technology for his industry. So much so that to this day his company has a virtual monolopy on a specific market because of his dedication to computerizing it before the competition.
Anyway, fastforward to 1995 when Unix machines replaced Prime computers. ‘C’ became the lingua franca and those not speaking the native tonque were frowned upon. Move ahead to 1998 when I became a Senior Developer in a group of ‘C’ drones. Don’t get me wrong, there were lot’s of problems for which that tool was very appropriate. The problem was it was used for everything you could shake a stick at when a language like Perl would have made mince meat out of most of the tasks facing this group. My abhorance to actual hard work and a desire to someday take Friday afternoons off and play golf led me to Perl. I’m embarassed it took that long, but I started a campaign to infect every server with DBI to make our database and data manipulation tasks easier.
Now here we are in 2002 and I am the Manager of Systems Development. The previous manager handed over a bunch of sales reports to me that he would distribute on the first of every month. He kept a tickler in his Outlook scheduler that reminded him to run the queries from T.O.A.D. (an Oracle GUI interface). He ran each query by hand, exported the data to a spreadsheet then integrated the reports into one spreadsheet with multiple worksheets.
Yikes! First, I have a lousy memory. Second, I don’t use the Outlook scheduler. Third, this problem cried for an automated solution, ’cause God forbid the first of the month might fall on a Friday and interrupt the golf dream! Enter Perl.
I had heard about a Perl module that wrote Excel spreadsheets but I had never bothered to check it out. I assumed it had some basic ability to write a spreadsheet file, but I never imagined just how powerful it really was.
We gathered up the queries, put a perl/DBI wrapper around them, learned a little about Spreadsheet::WriteExcel and voila. We now have a Perl script running as a cron job that does the following:
a) runs the 1st of every month
b) executes the query and writes the spreadsheet
c) creates a worksheet for each sub report
d) sets some nice options in each sheet so it can be printed in landscape and has titles on each page
e) mails the spreadsheet to individuals listed in a file containing e-mail addresses
The reports are actually nicer than before and (assuming cron is running OK) have a 100% better chance of ending up in someone’s e-mail in-box then if I were responsible for this chore.
Better still, the program is very maintainable and new reports can be added very simply. The amazing part of this story to me is how much of other people’s software we were fortunate to have access to. DBI and Spreadsheet::WriteExcel saved the day. And let’s not forget the power of Perl…
That’s my story and I’m stickin’ to it.
P.S. The details..
Lines of code: ~250 (driver and classes)
Frequency of execution: 1/month
Users: all the big wigs in our company (~5)
Time to develop: 4 hours, including creating a generic class that sub classes Spreadsheet::WriteExcel so that we can add new reports with minimal effort.
P.P.S. I’m not sure that Perl had anything to do with my promotion from Senior Developer through to Manager of Systems Development but it didn’t hurt ;-)
Senior Manager, Systems Development
Charles Jones, LLC
To learn how large and small companies are using Perl to meet their goals, check out Perl Success Stories.
If you have a Perl success story of your own that you’d like to share, please let me know. You can reach me at: firstname.lastname@example.org.