Re: Informix - Reports

->From: vkg1@Ra.MsState.Edu (VINAY GIDWANI) ->Subject: Informix - Reports ->Date: 9 Sep 1993 18:42:20 GMT ->Reply-To: vkg1@Ra.MsState.Edu (VINAY GIDWANI) ->Organization: Mississippi State University -> ->I have a report which prints out in the following way: -> ->AISLE WAVE SUM ->012 R00 2 ->012 R21 3 ->012 R22 4 -> -> TOTAL 9 -> ->013 R01 4 ->013 R21 6 ->013 R22 4 -> -> TOTAL 14 -> ->014 R01 3 ->014 R21 4 ->014 S00 3 -> -> TOTAL 10 -> ->I need the above report to print in a vertical fashion -> ->AISLE R00 R01 R21 R22 S00 TOTAL -> ->012 2 0 3 4 0 9 ->013 0 4 6 4 0 14 ->014 0 3 4 0 3 10 -> ->A few problems that I see are: ->The headers (except aisle) are part of the data. ->Aligning every sum under the proper aisle and waves. -> ->The approach I thought I would take was to select distinct wave from the ->table. and somehow have them as headers. Have them printed out at a particular ->column. Then select from the table and for every values of sum for a particular ->aisle print it out at the particular column. -> ->I was wondering if anyone else had any other suggestions to the above -> ->Thanks ->Vinay ->-- ->Vinay Gidwani ->email vkg1@ra.msstate.edu If you have the budget for tools software, then Intelligent Query from IQ Software, 1-800-458-0386, does this type of cross-tab report automatically. If no budget, then you must do the cross-tabbing yourself. The following technique works if you have a fixed number of wave forms: create table aisle_wave ( aisle char(4) , r00 integer , r01 integer , r21 integer , etc., etc. ); insert into aisle_wave ( aisle ) values ( select distinct aisle from other_table ); Now you need many updates similar to this: update aisle_wave a set r00 = ( select count(*) { This r00 is column name. } from other_table o where wave = 'R00' { This 'R00' is value. } and o.aisle = a.aisle); Now you can do selects against the aisle_wave table, including the column r00+r01+r21+etc to get your totals. If you don't have a fixed number of wave forms, then this becomes somewhat harder. First you need to select distinct wave from other_table; to get your wave forms, then proceed pretty much as described above. It could be automated in 4GL or ESQL/C: the program would need to construct the SQL to create aisle_wave table, then construct the various update statements. An interesting challenge. Regards, Alan +---------------------------+--------------------------------------------+ | R. Alan Popiel | Internet: alan@den.mmc.com | | Martin Marietta, Tech Ops | Voice: 303-977-9998 | | P.O. Box 179, M/S 5422 | My opinions may not reflect Martin policy. | | Denver, CO 80201-0179 USA | In fact, we often disagree. | +---------------------------+--------------------------------------------+