Modern office processes are usually a combination of manual, semi-automated, and automated operations.  SQR programmers often contribute software to these processes, but our customers don’t always tell us enough about their processes for us to make the best possible contribution.  We can classify some of our work into a hierarchy of solutions.

Report Collage

Not college, collage.  Wikipedia defines it as “a work of formal art, primarily in the visual arts, made from an assemblage of different forms, thus creating a new whole.”  Remember taking a dozen photos out of magazines and gluing them in a jumble on poster board?  It was the origin of the term “cut and paste.”

Sometimes a process requires people to gather information from several reports, performing the equivalent of a table join in their heads.  This week I talked to a financial analyst who takes reports of vacation balance liability from two successive years, plus a report of vacation accrual in the year between the two reports, and calculates the amount of vacation time taken for that year.  (First year balance plus accrual minus second year balance.)

I have to wonder about the history of this process.  Did somebody start with a goal to measure vacation usage and decide that we needed those two programs?  Or did somebody have those two programs and decide to use them to solve a new problem?  Did they ask one of my predecessors for help that was delayed or denied, or did they feel they had all the resources they needed already?  How many analysts have passed this algorithm along like great-grandma’s recipe for sweet potatoes, for how many years?

I plan to meet with the financial analyst, make sure I understand her needs, and probably propose a targeted report.

Targeted Report

A single report that includes all the necessary information for an office process is better than a collage, even if we end up with multiple reports with overlapping data.  That’s not a sign that we’ve wasted our programmers’ time.  It’s a sign that we’re saving our business analysts’ time.  Something a small as rearranging columns or changing the sort may be worth doing to save time and effort, and to reduce the potential for error.

Starting from scratch, it seems like the easiest way to assess vacation usage is to sum the hours and dollars of the appropriate earnings codes from the paychecks.  The analyst approached me to add another layer of information, to indicate the vacation usage by general ledger fund.  We have a table with the hours, dollars, and account codes that we generate for the GL interface.  We may be able to satisfy this new requirement with a single SQL statement.

We shouldn’t stop there.  The next question should always be “what are you going to do with the report?”  The answer might be to file it for possible future reference, or to act on some unpredictable subset of the results.  But often the use is specific, repeated, and clearly defined.  We can take our service role to the next level of the hierarchy.

Data Export

Data export is a half step and a quarter step down from targeted report.  It’s an advance in that gives an analyst the freedom to do ad hoc data analysis that might vary with content of the data.  It’s a decline in that we usually must stick to detail data and strip away interpretive information; subtotals, descriptions, even report titles and column headings.

Exporting data to ad hoc databases and spreadsheets may be a practical necessity for an overly busy programming staff, but it may be a false economy, creating more work in other parts of the organization than it saves in IT.  What initially seems like an unpredictable ad hoc process may settle into a stable routine ripe for partial or full automation.

Decision Support

Getting an answer to the “what are you going to do with the report” question can take some persistence.  People who request reports usually believe that they know the best way to reach their goals, and the fastest way to get there is for the programmer to get to work and stop the interrogation.

If the answer is to analyze or research the data further, the report should do that analysis or research.  If the answer is to pick out the exceptions or problems, the report should print just that data.  If the answer is to apply some hard-to-describe judgment and to use some data outside the Peoplesoft system (hair color, weather conditions), the report should help as much as it can.

If the report tells us that we need to call certain employees and discuss certain matters, it should supply phone numbers and relevant details.  My organization offers life insurance for our employees’ dependents.  We have a program that reports on various anomalies in the database, including employees who have dependent life insurance but have no dependents.

There are several ways that can happen.  A married employee might become divorced or widowed.  An employee’s children might outgrow their eligibility.  An employee might enroll by mistake, thinking that we will cover his only relative – his brother.  A simple report will just indicate the anomaly.  A decision support system will tell us what happened:

  • Congratulations, your last child got married.
  • You haven’t told us whether your youngest child started graduate school.
  • Condolences on your loss, don’t you want to cancel your payroll deduction?


Nobody wants to be replaced by a machine, unless it’s a machine to do our unpaid overtime work.  Sometimes it takes some tough love to help our colleagues recognize that what once took savvy judgment and honed skill has become rote.

There are several programs that my organization runs in rapid succession.

  1. Check the timecards in our third party system and report on the ones that haven’t been approved.
  2. Mark all the unapproved timecards as approved.
  3. Export all the approved timecards to flat files.
  4. Run a Peoplesoft Application Message to read the flat files and import the timecards to Peoplesoft.
  5. Compare the Peoplesoft table and the third party table to report the timecards that the Application Message lost.

I’ve been asked to write step 6, a program to copy any lost timecards from the third party system to Peoplesoft.  It’s only a little harder than copying all the timecards, regardless of their approval status.

Originally, we thought we would investigate the results of step 1 and work to have those timecards corrected and approved.  Then we realized that it was more efficient to have them corrected after we imported them to Peoplesoft.

Originally, we thought that the Peoplesoft Application Message was a more robust and reliable method for importing data than a SQL copy.  Then we thought we could manually correct any omissions listed in step 5.  Now we’re realizing that we know exactly how to correct the omissions and it should be automated.