Programming Praxis

Regular readers of this blog know that in my day job I am a programmer charged with maintaining an enterprise-wide database system, running on HP-UX (we’re switching to Linux next year) and Oracle 12g. We are in the process of replacing our existing report-writing software with a new, user-friendly product. Personally, I think it’s crazy to train non-technical people how to join tables and produce aggregates (the very first example in the training manual tripled my age to 183 because a one-to-many join was done incorrectly), but the users are excited because they will be able to get their data themselves instead of relying on the IT department to produce reports. I won’t tell you which reporting software we bought (you would recognize the name) or how much it cost (you will cry).

Some of the reports that we produce (probably a quarter to a third of them) can’t be done using the new software because the necessary fields aren’t in the data warehouse. The IT department (I am one of six programmers) will have to rewrite those reports using some combination of Oracle SQL*Plus and PL/SQL; the result will be plain-ascii reports delivered as LIS files in our standard enterprise software. Yes, 1960s-era reports, 132 columns by 66 rows per page, printed on a modern laser printer instead of green-bar fan-fold paper — that’s called progress where I work.

I’ve been looking at report-writing software. SQL*Plus actually isn’t bad — it must have been written in the 1960s for 1960s-era reports — but I still have to count the print columns to specify the linesize. SQL*Plus provides report headers and footers, page headers and footers that can include values drawn from the data, and control-breaks with record counts and totals. The one feature that is missing is proper headers and footers on each control-break; the field that caused the break must be included in the body of the report (on each detail line) rather than on a break header (so it takes horizontal space on the report, which may be in short supply), and there is no provision to include the break value in the total line of the break.

So I am in the market for a simple report generator. It must work with Oracle and produce plain-ascii output. Features include reprt headers and footers, page headers and footers, break headers and footers, all of which must be able to include values from the output. It has to be callable from a Unix shell script so it will fit with our enterprise system.

I’ve been looking for something suitable, and surprised I can’t find it. All of the solutions I have found are some combination of expensive, overly feature-full, or hard to use. I don’t want to use MS-Access with an ODBC connection. I looked at Jasper and BIRT; neither is suitable. I looked at every old Unix book in my library; I figured Don Libes would have something in his useful book, but he doesn’t. I’m looking for something at the level of a Unix shell script, something like Awk but with more of a report-writing flavor. On the next page you will see a sample report specification for a real report that I will need to write; SQL*Plus can handle most of that, but not the breakheader and breakfooter elements.

So I come to my readers and ask for help. Does anyone know of a simple 1960s-era Unix report generator?

Maybe I’ll write one.

Many thanks.

Pages: 1 2