Re: SQL query - help please

A little known feature in Informix 4GL is that you can open, output to and finish a second, third, etc. report from within another report. You could, if you desired, do a start report in a before group of sales_rep clause, do an output to second report in the every row clause and do a finish report, print file combination in the after group of sales_rep clause. Similarly, you could open a third report in the first page header section, output to it in the on every row clause and close/print file in the on last row clause. Personally, I would opt to start and finish the 'grand total' report outside of the original report and do a print file in the on last row clause AFTER the finish report statement is executed. (If you don't do the finish, you may get either partial or no output depending on whether you do a sort/external sort by in the 'printed' report.) One warning. I have used this successfully in some instances and failed miserably in others. It seems that Informix does not always keep track of the line count properly with a print file, at least in a couple of reports I had to do. If this is the case, give up early and do as Art suggested in his reply. I wrote: > Hello, > > I've written a 4GL program to produce a report and the SELECT > statement looks like this: > > SELECT sotrn.repnum, stmst.grplup, sotrn.accnum, > SUM(sotrn.totalord), SUM(sotrn.totalord*sotrn.price) > FROM sotrn, stmst > WHERE sotrn.co_id = g_conum > AND sotrn.orderid >= p_orderid > AND sotrn.repnum BETWEEN p_startrep AND p_endrep > AND sotrn.stat_ind != p_cancode > AND sotrn.ordertype = "F" > AND sotrn.crt_date <= p_crt_date > AND stmst.co_id = g_conum > AND stmst.productid = sotrn.productid > AND stmst.season MATCHES p_season > AND stmst.grplup MATCHES p_productgrp > GROUP BY sotrn.repnum, sotrn.accnum, sotrn.stmst.grplup > ORDER BY sotrn.repnum, sotrn.accnum, sotrn.stmst.grplup> > The variables are g_conum, p_orderid, p_startrep, p_endrep, > p_crt_date, p_season, p_productgrp, p_cancode > > The report produced looks a bit like this ( superfluous stuff not > shown): > > Sales rep 001 > > Account Product Group Qty Value > G0113013 001 13590 216711.50 > 003 6552 70440.00 > > -------------------------------- > 20142 287151.50 > > G0115647 001 10640 182735.00 > > -------------------------------- > 10640 182735.00 > > -------------------------------- > Total for rep 001 30782 469886.50 > > Sales rep 002 > > etc..... > > The problem I have is that I want to extend the amount of information > shown on the report to include the following: > > 1. After each sales rep I want a total count of each product group. In > the above example I would get: > > Product Group Total Qty > 001 24230 > 003 6552 > > 2. I would like a total at the end of the report to show the total > count of each product group for all salesman > > 3. After each salesman I would like a count of the number of accounts. > In then above example I would get: > > Accounts > 2 > > I've been struggling to get tis information easily and I wonder if I > have put restraints on what I can do with the structure of the main > SQL query with its GROUP BY clause and the structure of the REPORT > section which looks something like this ( in the interests of brevity > I have missed out some irrelevant stuff): > > FORMAT > PAGE HEADER > ------ > > PAGE TRAILER > ----- > > BEFORE GROUP OF f_repnum > ----- > > AFTER GROUP OF f_repnum > PRINT GROUP SUM(p_totalord) > PRINT GROUP SUM(p_totalvalue) > > BEFORE GROUP OF f_accnum > ----- > > AFTER GROUP OF f_accnum > PRINT GROUP SUM(p_totalord) > PRINT GROUP SUM(p_totalvalue) > > ON EVERY ROW > PRINT product group, qty, value > > Well, this posting is reaching epic proportions and for that I mst > apologise to the ng. I hope I've included enough to work on if anyone > can help. > > I would appreciate any input as my brain is beginning to hurt on this > one :) > > Thanks, Ian.