How To Write Spreadsheets From SQR

SQR and the Peoplesoft Process Scheduler work together to allow users to choose output format. The favorites in my organization are document (PDF) and spreadsheet (XLS). I mentioned this in 4 SQR Resolutions and I’ve had three readers ask me to elaborate. For this blog, that’s a tsunami of feedback.

Finding Common Ground With Excel

A wonderful feature of SQR that we sometimes overlook is that the interpreter will execute our naïve print commands according to the command line flags. SQR can “print” files in Brio format, HTML, comma separated values, PDF, Postscript, PCL, or its native formats “lis” and “spf.”

Microsoft Excel can read files in a variety of formats, including HTML and comma separated values. If we set up our “file open” options on our Microsoft Windows desktop properly, we can double click on an SQR output file and trigger Excel to open it. But what will we see?

If our customers are exporting to Excel, the reports are probably textual rows and columns, not graphs or graphics or free form prose. The SQR interpreter is clever, but it can’t read our minds. We need to remember a few rules.

One-To-One

There is a one-to-one correspondence between print commands and comma separated values. It might be more convenient and compact to print all the column headings as in one command, but that will put them all in the first column of the spreadsheet.

Wrong:

print 'EMPLID DEPTID JOBCODE HIRE_DT NAME' (+1,1)

Right:

print 'EMPLID' (+1,1)
print 'DEPTID' (,9)
print 'JOBCODE' (,17)

We might think that we’re saving something (a millisecond? 30 bytes of file storage?) with this code, but if we skip a print command the rest of the values on that line will be shifted left one cell in the spreadsheet.

Wrong:

if &DEPTID <> ' '
  print &DEPTID (,9)
end-if

Similarly, we’d better not use multiple print statements to build a single value.

Very, very wrong:

print #dollars (,10) edit 9999
print '.' ()
print #cents () edit 09

Formatting Numbers, Annotating Pages

The SQR print command includes formatting. I wrote about making output easier to read in SQR Masks. Excel has its own formatting and expects numbers to be numbers, without commas, dollar signs, parentheses, or trailing minus signs. This suggests that we cannot format with these clarifying characters in any report for fear that our users will want to export to spreadsheets.

Similarly, headings, footings, and subtotals are valuable in documents but hindrances in spreadsheets. Even without them, SQR will periodically devote a line to the form feed character. That is another annoyance to clean up on a spreadsheet.

Dual Reports

There are a few options to deal with the question of output formatting.

  • Make the customer choose the format in the functional specification and program for that. Advise the customer to ask for that format in the Peoplesoft Process Scheduler or the command line.
  • Write the program with output bland enough to work as a document or a spreadsheet.
  • Add a run control parameter that allows the user to specify the output format, hoping that the user will be consistent when selecting output format for the Peoplesoft Process Scheduler.
  • Extract the output file extension from the $sqr-report variable and use it to choose between two versions of every print statement.
  • Produce both outputs every time.

Sometimes users want a well-formatted report and a ready to use spreadsheet. They could run the program twice, but that seems wasteful if there is a lot of database work to collect the report data. The extra work to write the data to a disk file as well as to print the data to a virtual page is minimal to program, minimal to execute.

First, we open a file for writing variable length records in the directory named by $sqr-report so that user can find it after the Peoplesoft Process Scheduler posts it (see How To Parse A Filename In SQR). We also need a variable called $tab or $t equal to chr(9). Tabs are better than commas because we usually know there won’t be tabs in our data. If we use commas, then we have to modify any string that includes commas to be enclosed in double quotes. Another advantage of tab separators is that our strings can include double quotes (inches, anyone?).

Next, after we finish printing each row of detail data – not headings, footings, or subtotals – we need to concatenate all our values into a single variable to write to the file. We don’t want to use the ability of the write command to output a list of variables because we don’t want to pad each value to a fixed length and therefore we cannot specify a literal numeric length for each variable.

let $line = &EMPLID || $tab || $lastname-comma-firstname || $tab || edit(&HOURLY_RT, ‘999.99’)
write 1 from $line

Note that we must convert numbers to strings to use concatenation. We would prefer to write numbers as strings anyway since writing a number to a file will store it in binary, which would be inaccessible to Excel. Ironically, we have to format numbers twice, in the print command and for the write command. The formats need not be the same.

Readability may benefit from right justification, commas and currency signs, rounding to a particular number of decimal places, or highlighting negative numbers. Numbers for the spreadsheet should be left justified, without commas and currency signs, maybe rounded to more decimal places (risky but possible), and without special treatment for negative numbers.

Rounding for the spreadsheet needs some consideration. Does the organization expect any calculation on the spreadsheet to match a parallel pen-and-paper calculation from the document? If so, round the same way. Does the organization want the spreadsheet to be as accurate as possible, recognizing that any document must be a rounded approximation of the truth? If so, give the spreadsheet more digits.

Comments are closed.