Re: Tricky ACE Report Problem

A while ago, Kate Juliff asked: >I have a table that amongst other columns has > >order_no line_item_no due_date > >and I've been asked to do a report with the following layout > >For the 7 days prior to today > >date1 date2 date3 ... date7 > >order_no, line order_no, line order_no, line > >such that there are 7 columns, with the date headings and below each >date a list of all items that fall due on that date. > >I can't see an easy way of doing this. I have ISQL. There have been various attempts to answer this, both in the c.d.i news group and by emails behind the scenes, with varying degrees of success. It's a very nasty problem, and it's much easier to pick holes in solutions than to come up with a good one. It isn't made easier by the fact that you can't create temp tables explicitly in ACE, so you can't create a SERIAL column, and you can't use ORDER BY when you do a SELECT INTO TEMP anyway, so you'd want to do "INSERT INTO temp_table SELECT ... ORDER BY ..." (if that is allowed at all!), but again ACE does not allow this. One of the problems which recurs with most of the solutions that use OUTER JOIN is that there is no guarantee that the first date or the last date is the one with the longest list of outstanding items. Historically, what I've done is have a report which generates column1, column2, column3 ... of the output into separate files, and I've then pasted these together using a shell script, adding header and trailer files: +----------------------------------+ | header | +----+----+----+----+----+----+----+ | f1 | f2 | f3 | f4 | f5 | f6 | f7 | | | | | | | | | | | | | | | | | | | | | | | | | +----+----+----+----+----+----+----+ | trailer | +----------------------------------+ It's repulsive, though it does work. I think all my files were guaranteed to have the same number of lines, though, and I'd not want to guarantee that this would work if the files were of wildly differing lengths... The best answer to date seems to be to use two reports run in tandem. This is a design which I've used in I4GL on a number of occasions, using ReportA to generate the data in some way, and ReportB to format it. It can be helpful for matrix reports or for spreadsheet type reports. For the problem at hand, we design an intermediate results file with the data in a format suitable for use by ACE, and then use a pair of reports, one of which generates data to be loaded, and the other of which formats it using a READ statement instead of a SELECT statement. The reports would look something like those outlined below. Note that in I4GL you would have the first report send its OUTPUT to /dev/null, and instead of doing PRINT operations, it would do OUTPUT TO report2(). This is very effective, but I can't think of a way to do it with just ISQL. There are still minor issues like getting the column headings sorted out in the second report -- I'd probably use one or two parameters to the second report for the start and/or end dates -- and making sure that more than one person can run the report at a time (those hard-coded file names become a liability). Yours, Jonathan Leffler (johnl@informix.com) #include <disclaimer.h> =========================================================================== -- report1.ace -- DATABASE somedb END DEFINE PARAM[1] end_date DATE VARIABLE row_num INTEGER VARIABLE col_num INTEGER VARIABLE delim CHAR(1) END OUTPUT REPORT TO "report1.out" PAGE LENGTH 1 TOP MARGIN 0 BOTTOM MARGIN 0 LEFT MARGIN 0 END SELECT order_no, order_line, due_date FROM orders WHERE due_date BETWEEN end_date - 6 AND end_date ORDER BY due_date, order_no, order_line END FORMAT FIRST PAGE HEADER LET delim = "|" LET col_num = 0 LET row_num = 0 BEFORE GROUP OF due_date LET col_num = col_num + 1 LET row_num = 0 ON EVERY ROW LET row_num = row_num + 1 PRINT row_num, delim, col_num, delim, due_date, delim, order_no, delim, order_line END -- report2.ace -- DATABASE somedb END DEFINE ASCII row_num INTEGER, col_num INTEGER, due_date DATE, order_no INTEGER, order_line INTEGER VARIABLE last_col INTEGER VARIABLE col_wid INTEGER VARIABLE col_off INTEGER END READ "report1.out" ORDER BY row_num, col_num END FORMAT FIRST PAGE HEADER LET col_wid = 10 LET col_off = 5 [...] PAGE HEADER [...] PAGE TRAILER [...] BEFORE GROUP OF row_num LET last_col = 0 ON EVERY ROW PRINT COLUMN (col_num - 1) * col_wid + col_off, order_no USING "#####&", ",", order_line USING "<<<<"; AFTER GROUP OF row_num PRINT END