4 SQR Resolutions

It’s not New Year’s Eve, but I have some resolutions to use more of the rich features of SQR to write programs that are richly featured themselves.

More Color

My internal customers are so used to monochrome, monofont reports that they’ve never thought to ask me for color.  Most of my reports output to PDF files in the Peoplesoft Process Monitor environment, and most of my customers have access to color laser printers.  I’ll try adding color sparingly to highlight parts of the report: subtotals, inline error messages, and unusual data.

Better Headings And Footings

My report headings include the name of my organization, the name of the program, the title of the report, the run date and time, and the page number.  At the end of the report, I print “End of Report” to ensure that something is printed, even if there is no appropriate data.

I’ll start using the last-page command to print “page x of y” on each page.  I’ll add variables to the report title to describe the content of the report, like these.

  • As Of August 31, 2009
  • From August 16, 2009 To August 31, 2009
  • For Department 501 Finance
  • Active Employees

I’ll include other parameters that affected the contents of the report, either in the heading on the last page (balancing the availability of the information with the size of the heading).  I’ll describe the report, perhaps on a cover page, including its purpose (finding errors in paychecks), functionality (search for excessive hours), and context (part of the biweekly payroll process).

Dual Output

SQR and the Peoplesoft Process Scheduler work together to allow operators to choose output format.  The favorites in my organization are document (PDF) and spreadsheet (XLS).  Careful programming will allow the same print commands to work either way.  (Would that be a good blog topic?)  However, headings, footings, and totals are wonderful in documents but hindrances in spreadsheets.  Sometimes I write the program to produce both outputs every time.  This may be a good practice for all reports.


The stop command causes the SQR program to rollback any database changes and stop executing.  It skips the stdapi-term procedure and sends an “error” return code to PSSQR.EXE, causing the Process Monitor to set the run status to “Error.”  The commit command followed by the stop command keeps the database changes but still gives us a run status of “Error.”

The “Error” status was designed to flag a bug in our programs, but it also gives us a way to alert the operator that we are dissatisfied with the outcome of our program.


  1. Bob Josephson says:

    Yeah, I think talking about XLS/PDF output would be a good blog topic.

    Good post, but a little short this week. Taking a little vacation? Hope you’re spending lots of time relaxing, having fun, and avoiding stressful situations!

    - Bob

    • administrator says:

      I was busy last week moving out of my house and preparing it for sale. You never know how much you have until you’ve tried to put it all away in a new house.

      When I started writing this entry, I thought I would have more to say. I was satisfied with only four resolutions. I started, then deleted, two more. The fifth one was to use the chart commands, but I couldn’t think of a use for charts in my organization. The sixth one was to use the column commands, but I decided that there was a better approach to printing columnar reports.

  2. Evan says:

    Good stuff. Definitely do an XLS vs PDF article