Re: ISQL help
John,
This is an ACE report. I'll post the entire code below so that we're
on the same page. The report as it stands is perefct except it lacks
a sub-total for each fee type at the end just above the grand total
for all of the fees. When I added semi-colons between SELECT
statements, it gave an error regarding submiting to TEMP tables. Your
suggestion sounds great but doesn't quite work either, but I'm sure
that's a syntax error on my part. Here is the report in its entirety.
BTW, your help is greatly appreciated.
database
family
end
define
variable bdate date
variable edate date
end
input
prompt for bdate using "Enter Period Start Date: MMDDYYYY "
prompt for edate using "Enter Period End Date: MMDDYYYY "
end
output
report to "/reports/prechkfees.rpt"
left margin 1
right margin 80
page length 66
end
select ms_case_number, pa_cp_py_code, pa_prechk_date,
pa_amt_paid from flmastfile, flmastpymt
where ms_cgn = pa_cgn
and pa_prechk_date between $bdate and $edate
and pa_cp_py_code in ("DOC", "POS", "DLQ", "TRU")
and ms_4d_flag = "Y"
and (pa_conv_void_code is null or
pa_conv_void_code <> "V")
order by pa_prechk_date
end
format
page header
print column 1, "Clerk",
column 56, "Report Date ", today using "mm dd yyyy"
print column 1, "Pre-Check Clerk Fees for DLQ, POS, DOC and INT"
print column 1, "Beginning date ",
column 16, bdate using "mm dd yyyy",
column 30, "Ending Date ",
column 42, edate using "mm dd yyyy"
skip 1 lines
print, column 44, "----------- Pre-Check Info ------------"
print, "Case Number",
column 16,
column 44,
column 56, "Date",
column 66, "Type",
column 74, "Amount"
skip 1 lines
on every row print
column 1, ms_case_number,
column 56, pa_prechk_date using "mmddyyyy",
column 66, pa_cp_py_code,
column 70, pa_amt_paid using "###,###.##"
after group of pa_prechk_date
skip 1 line
print column 34, "Sub-total for",
column 50, pa_prechk_date ,
column 70, group total of pa_amt_paid
using "###,###.##"
skip 1 line
on last row
skip 1 lines
print column 45, "Total Clerk Fees",
column 70, total of pa_amt_paid using "###,###.##"
page trailer
skip 1 lines
print column 35, "Page ", pageno using "<<<<"
end
Leo wrote: > This is an ACE report. [...] I think I'm correct in saying Leo now has a workable (albeit not desperately elegant) solution to his problem. It involved using conditional overall aggregates (TOTAL OF ... WHERE ...) in the ON LAST ROW block. Given that he knows the 4 payment type codes, it is not too bad; if the list was variable, the coding in ACE would be awful. -- Jonathan Leffler #include <disclaimer.h> Email: jleffler@earthlink.net, jleffler@us.ibm.com Guardian of DBD::Informix v2003.04 -- http://dbi.perl.org/