Two for the road, Report from Clerk & Nonstandard Subtotals
John Esak
john at valar.com
Mon Mar 14 00:51:34 PST 2005
(Go put on a pot of coffee.. this is a long one! I mean _really_ long. ...
probably the longest I've ever done. Apologies up front. :-) )
The last idea about browse brought up some great comments and enhancements.
Nice to see the good traffic again and learn some new things as well. Here
is some more food for thought. Let's keep it up, why not?
I'll start slow and then once you have the basic ideas, I'll ramp up through
more complicated stuff until I can show you a twist to it all... something
which will be very useful to you should you choose to try the reports from
clerk paradigm I like so much. On your mark, get set... plunge in...
I've been using a technique for some time now that gives me really good
flexibility and better looking user-interfaces for our reports. I run the
report from clerk as a system command. Yes, yes, I know it eats up two
license keys to do this, but in our case, we have plenty to spare (that's
an all-around good recommendation in my opinion anyway) and the added
usability makes up for this hit. The flexibility comes from the fact that by
using clerk as a wrapper I can put up a SCREEN on which to gather the
desired report criteria instead of some less suitable methods when using
only report. Users can move around on a screen and check their entries
before submitting them. It's just better.
Normally, running a report from clerk is easy. You just pick a temporary
file in witch to "stand" and enter the file in Add Records Mode (using -xa
on the command line) like this:
dreport stand -sQuery -z run_report_rom_clerk -xa
You present a query screen that ask for all the criteria for the report, and
when the user SAVE's the record, you pass that information to the report via
the parameter variables, and run the report with a SYSTEM command. When the
report is done, you delete the record on which you are standing and then
exit clerk. All very neat and simple.
As I said, by putting up a regular screen for the user. They get the ability
to move around through the fields until they have things right. This
functionality is hard to duplicate in report.
The Query screen could be something as simple as this....
+---Enter Report Criteria------------------------+
| |
| Cust Code: *co |
| |
| Begin Date: *bd |
| |
| End Date: *ed |
| |
+---------------------------+
Press ESC to run the report
Press DEL to cancel
The code is pretty simple and could look something like this:
File: stand
prc: run_report_from_cler
If: 'define vars for screen
1 Then: bd(8,mdy/); ed(8,mdy/); co(4,allup)
2 Then: declare params(33,,g)
If: 'assign parameters for the report from the screen entries
3 Then: params="-rw" < bd < "-rx" < ed < "-ry" < co
If: 'run the report through a SYSTEM command
4 Then: system "dreport stand -f simple_usage -v get_recs -u" < params
5 Then: delete
6 Then: exit
One of the best ways to make use of the parameter data on the command line
is to use it in a -v (sort/select) table, and thereby select only the
records requested for the report. There are MANY ways to enhance this type
of selection process! Shown here is just something dead-simple so we can
focus on the discussion at hand.
The -v table code might look like this...
File: stand
prc: get_recs
If: 1 ne @pw 'only get correct customer records
1 Then: end
If: 2 lt @px or 2 gt @py 'disallow dates out of requested range
2 Then: end
If: 'select all requested records
3 Then: select; end
The report process itself can make use of the @p parameter variables to
display the criteria in the heading or elsewhere. The report might look
like...
Special Report For !@pw Date; *@td
From !@px to !@py Page: <@pn
Date Cust_PO# Line_Item# ItemCode Qty Charge
----------------------------------------------------------------------
*7 *14 *15 *18 *20 *21
=========
Total Charge: =21
The output from the report might look like this...
Special Report For DU72 Date; 03/13/05
From 01/01/03 to 12/31/04 Page: 1
Date Cust_PO# Line_Item# ItemCode Qty Charge
----------------------------------------------------------------------
01/15/03 12345-992 (1) CanXJ57 1000 950.50
01/15/03 12345-992 (2) LidXJ57 1000 250.50
01/15/04 54321-672 (1) PL5-999 500 150.00
04/18/04 992-verbal (1) Coreflex 1575 2550.50
=========
Total Charge: 3900.50
The report executes and returns back from the SYSTEM command, where filePro
is immediately told to "delete" the record (we r standing on) which we got
by virtue of doing this all in Add Records Mode (the -xa). Finally, the
process EXIT's back to the calling menu or whatever invoked this whole
procedure in the first place.
For those of you who haven't done this sort of thing, I urge you to try it
out. So many niceties become possible when you are in clerk rather than
report. For just a single, small example, you could put up a browse of the
Customer Codes when they are in that field, so they don't have to remember
them from memory. Your users will enjoy the new look and feel, and most of
all they will not be irked when they put in a wrong date by mistake along
the way and can't get back to fix it, so they have to abort the report and
try again. Some of us have written elaborate procedures in report or
sort/select processing that allows users to verify and re-enter various
criteria, but on the whole this is always harder than just putting up a
screen... and again, remember the availability of browses, popups, and
everything associated with @when field triggers. You can design report
request interfaces that have a very professional and clean look and feel.
(NOTE: Incidentally, I am using the parameter variables to show how to pass
the user's report criteria out through the SYSTEM command via "dreport".
This works great, and you can even concatenate many pieces of data into one
variable, such as: -rw 01/01/0412/31/04. Then, in the sort/select table MID
out the two dates as mid(@pw,"1","8") and mid(@pw,"9","8"). It quickly
becomes possible to send lots of data through these variables. However, in
my opinion, a much better way of handling this is to use PUTENV and GETENV.
You would use the PUTENV to send values into the environment, and in the
sort/select or output process of the SYSTEM "dreport ..." you would use
GETENV to retrieve these values. This gives you an unlimited number of
variables to pass. It is really a much better methodology, but it is for
another day.)
Okay, That was all pretty straightforward, I hope you all are with me so
far. Here is a small twist. The previous functionality is possible because
of the nature of Add Records Mode which gets you a free record to
temporarily stand on while you do your work. Then you can simply delete it
when you are done. The report itself actually happens with records in some
other file. What if you have a really strange requirement where you need a
record to stand on, but one that is unlocked in the file in which you are
standing. How can something like this be necessary? Well, in this case, it
is VERY strange and as I said complicated... so be patient while I stumble
around for the right words.
I have lots of files which have partner "archive files (or archive
qualifiers). For example, I have a current Orders file and an archived
Orders file. The current file may have only a few thousand latest records in
it, the archived file has hundreds of thousands of old, closed records. We
are constantly writing reports that need to look into both files. Suppose a
customer was to ask us for a listing of his Orders for the past two years.
It would be very sloppy to print a partial report for the current records
out of the current file and the rest on another partial report out of the
archived file, all on different pages. Besides, how would you get the
total(s) for the records from both files? This is a common problem, and
I'm sure you have all dealt with it lots of times. Normally, it is possible
to run through both files with two separate reports, dumping the required
records from each into a "spin-off" file and then running a report on _that_
file. This works very well, and I do it all the time. You can, of course,
sort and total the spin-off file, so it looks like the data all came from
one place. No problem.
However, let's add the silly request one of our biggest customers added
yesterday. He wanted the listing and totals for all Orders for a certain
period, subtotaled by the usual things like date and location, etc., but at
the end of the report, he wanted each of the "items" they buy subtotaled as
well. There are just too many fields and too many combinations to do it all
effectively with filePro's built in sorting capabilities. So, I thought
about using an array to hold the item numbers and their total quantities and
charges. Each time I hit a new item while running through the records of the
report, I would write it into a new array element, and each time that item
was hit subsequently, I would just increment the totals in that array
element. Yes, it does work, but this always means a lot of testing through
the array to get you the matching element... or adding the item if it isn't
in the array yet. A very slow, and to be redundant, time consuming affair.
So, I opted to use a spin-off file... but this time, not for the main data
in the two files, but to hold just the item totals. I would lookup to this
file for each item encountered and if it was already in the spin-off file,
increment it, if not add it to the file. This procedure is much faster than
traversing an array. (The code is simple and used throughout most everyone's
filePro code, but for those who haven't seen it, it looks something like
this...
File: stand
prc: PutSpin subroutine
PutSpin If: 'PutSpin
1 Then:
2 Then: declare tot_charge(9,.2,g)
3 Then: tot_charge=tot_charge + c
4 Then: declare uniq_num(6,.0,g)
If: uniq_num eq ""
5 Then: gosub get_num
6 Then: lookup there=stand_ords_temp k=(uniq_num & s) i=A -nx
If: not there
7 Then: lookup there=stand_ords_temp r=free
8 Then: there(1)=uniq_num
9 Then: there(2)=i; there(3)=s; there(4)=there(4)+q; there(5)=
there(5)+c
10 Then: write there
11 Then: return
Each time an item is encountered the totals are incremented on its unique
partner record in the spin-off file. The variable S is the unique item
number, so the lookup looks for a match and if not found, creates a new
record on which to do the incrementing. Just before doing _any_ lookup to
this file a global "unique_number" is retrieved from a control file and
prepended to each item number for the key of the lookup. By using this
global unique number, at the end of the report, all the item totals for this
running of the report can be retrieved and printed. There may be lots of
other records in the file, but they will be ignored since you only retrieve
those with the correct unique_number.
The output from this report looks something like the following. Note the
subtotaled items coming _after_ the main Grand Total. That is because in the
@wgt processing I do a lookup to the spin-off subtotal file and get them one
at a time for printing. All the information in the Grand Total area on the
report is specified as dummy variables. First, the Grand Total and the
headings for the subtotals re printed, then these variables are cleared and
the process loops through the records in the subtotal spin-off filling and
printing one line at a time. This works because I have chosen "Close Up
Blank Lines" on the output format. It looks quite neat, and the format and
code for it are shown after it. But, all of this is just prerequisite to
learning what the twist is, and why I need to be standing on an unlocked
(non-free) record in my stand file in order to print the following report.
(After this report and its format and code... you can start reading again at
"The Twist".)
Special Usage Report for ABC Company i Date: 03/12/05
PO#: 123456789 Page: 1
From 01/01/04 to 12/31/04
OrderDate LineItem NPI_P# Cust_Item# NPI_Item# Qty
Charge
--------------------------------------------------------------------------
--
01/16/04 157349 PL8-22A A00337166 10037 1
42.16
01/16/04 157350 PL8-22A A00337166 10037 10
421.56
03/08/04 (1) 158719 PL8-263 A00610182 26411 19
1255.82
03/08/04 (2) 158720 PL-8-15B 26412 8
544.19
05/18/04 (1) 160650 PL8-169A A00337362 15219 35
1438.96
05/18/04 (2) 160651 PL8-263 A00610182 26411 19
1255.82
05/18/04 (3) 160653 PL-8-15B 26412 8
544.19
05/18/04 (4) 160654 PL8-22A A00337166 10037 10
421.56
07/12/04 (1) 161987 PL8-263 A00610182 26411 19
1255.82
07/12/04 (2) 161988 PL-8-15B 26412 8
544.19
07/12/04 (3) 161989 PL8-90B A00337326 10236 30
3213.00
07/23/04 (1) 162330 PL8-90B A00337326 10236 30
3213.00
07/23/04 (2) 162331 PL8-263 A00610182 26411 19
1314.80
07/23/04 (3) 162332 PL-8-15B 26412 24
1708.20
11/17/04 (1) 165278 PL-8-15B 26412 8
569.40
11/17/04 (2) 165279 PL8-90B A00337326 10236 30
3213.00
=========
Grand Total Charge:
20955.67
Subtotal for Items: 01/01/04 to 12/31/04
DUP_Item# NPI_Item# Qty Charge
-------------------------------------------------
PL8-22A A00337166 10037 21 885.28
PL8-263 A00610182 26411 76 5082.26
PL-8-15B 26412 56 3910.17
PL8-169A A00337362 15219 35 1438.96
PL8-90B A00337326 10236 90 9639.00
H E A D I N G / T I T L E L I N E S
Special Usage Report for - !co Date: *@td
PO#: !po Page: <@pn
From *bd to *ed
OrderDate LineItem NPI_P# Cust_Item# NPI_Item# Qty
Charge
--------------------------------------------------------------------------
--
D A T A L I N E S
*d *in *p *i *ss *q *c
_____________________________T O T A L L I N E
S______________________________
G R A N D T O T A L
*la
*lb
*lc
*ld
*le
*lf
(Note: The printing of this report can most easily be understood by looking
at the PRINT statements in the @wgt area of the processing below. First the
variables la through le are printed one time, and then the variable lf is
printed as many times as it is found in the spin-off file.)
File: stand
prc: simple_usage
1 Then: lookup ord=npioarch k=(cust_code & po_num & begin_date) i=I -ng
mor_arc If: not ord
2 Then: goto do_cur
If: ord(2) & ord(4) ne cust_code & po_num
3 Then: goto do_cur
If: ord(6) gt end_date
4 Then: goto do_cur
5 Then: d(8,mdy/)=ord(6); in(4)=ord(32); p(6,.0)=ord(1); i(20)=
ord(89)
6 Then: s(5,.0)=ord(3); q(4,.0)=ord(37); c(9,.2)=ord(72)
7 Then: ss(7)="S#"&s
8 Then: gosub postfil
9 Then: print
10 Then: getnext ord; goto mor_arc
do_cur If: 'do_cur
11 Then: '
12 Then: lookup ord=npio k=(cust_code & po_num & begin_date) i=I -ng
mor_cur If: not ord
13 Then: end
If: ord(2)&ord(4) ne cust_code & po_num
14 Then: end
If: ord(6) gt end_date
15 Then: end
16 Then: d=ord(6); p=ord(1); in=ord(32); i=ord(89)
17 Then: s=ord(3); q=ord(37); c=ord(72)
18 Then: ss="S#"&s
19 Then: gosub postfil
20 Then: print
21 Then: getnext ord; goto mor_cur
postfil If: 'postfil
22 Then:
23 Then: tot_charge=tot_charge + c
24 Then: declare uniq_num(6,.0,g)
If: uniq_num eq ""
25 Then: gosub get_num
26 Then: lookup there=stand_ords_temp k=(uniq_num & s) i=A -nx
If: not there
27 Then: lookup there=stand_ords_temp r=free
28 Then: there(1)=uniq_num
29 Then: there(2)=i; there(3)=s; there(4)=there(4)+q; there(5)=
there(5)+c
30 Then: write there
31 Then: return
get_num If: 'get_num
32 Then:
33 Then: lookup stand.ctl r=("1") -p
34 Then: uniq_num=stand.ctl(2); stand.ctl(2)=stand.ctl(2)+"1"; write
stand.ctl
35 Then: return
@wgt If: '@wgt
36 Then:
37 Then: la="========="
38 Then: lb="Grand Total Charge: " & tot_charge
39 Then: lc="Subtotal for Items:" < bd < "to" < ed
40 Then: ld="Cust_Item# NPI_Item# Qty Charge"
41 Then: le="-------------------------------------------------"
42 Then: print; la=""; lb=""; lc=""; ld=""; le=""
43 Then: lookup itms=stand_ords_temp k=(uniq_num) i=A -nx
mor_gt If: not itms
44 Then: end
If: itms(1) ne uniq_num
45 Then: end
46 Then: lf=itms(2) & " " & "S#" & itms(3) & " " & itms(4) & " " &
itms(5)
47 Then: print; lf=""
48 Then: getnext itms; goto mor_gt
@once If: '@once
49 Then:
50 Then: declare tot_charge(9,.2,g)
51 Then: declare begin_date(8,mdy/,g), end_date(8,mdy/,g)
52 Then: declare cust_code(4,,g), po_num(16,,g)
53 Then: begin_date=@pw; end_date=@px; cust_code=@py; po_num=@pz
If: 'for printing on report
54 Then: bd(8,mdy/,g)=begin_date; ed(8,mdy/,g)=end_date
55 Then: co(4,,g)=cust_code; po(16,,g)=po_num
56 Then: end
"THE TWIST"
Okay, so what is the damn twist anyway?? When you run a report, you can only
get to the end of it by processing all the selected records. It is only at
this time that @wgt will run. Since I am standing on a free record in the
"stand" file and running this table as a SYSEM "dreport ..." scenario as
shown far above... I must choose one record in some file for the dreport to
function against. Preferably this stand file, because why have two different
stand files, and all the samegotchas still apply. I need to stand on some
single record for the dreport, do all the looking up of data from the
archived Order file and the current Order file, printing each one in the
data area one at a time as I go. Meanwhile building the spin-off subtotal
file. When I'm done, I must fall through to the @wgt section so these
special subtotals can be printed and the only way to have this happen is to
have the selected record hit its END statment. I can't select the record
I'm standing on because it is locked while I'm on it. So, the twist is to
trick filePro into looking up a free record, writing it out, and storing its
record number. Then, I hand that record to the SYSTEM "dreport ..." so it
has a single record to do its work. When it is done, I delete that record
_and_ the one I have been standing on. This works for files with NO records
in them as well as files that already have some records on which to stand.
It is very hard point dreport toward a record somewhere in a muli-user
situation. It is too bad you can't point it to the free one obtained
with -xa... just won't ever get to the Grand Totals that way. So, it looks
like this... dead-simple... once you've worked it out that is. :-) It is
only a small variation on the first run_report_from_clerk thing a hundred
pages above... but it is enough to make it all work.
File: stand
prc: run_spcl_report_from_clerk
If: 'define vars for screen
1 Then: bd(8,mdy/); ed(8,mdy/); co(4,allup); po(16)
2 Then: declare params(55,,g)
If: 'assign parameters for the report
3 Then: params="-rw" < bd < "-rx" < ed < "-ry" < co < "-rz" < po
If: 'save the record, which moves us to a new record (we're using -
xa)
4 Then: write; end
5 Then: '
@wefco If: '@wefco
6 Then:
7 Then: declare hold_rec(8,.0,g)
If: hold_rec eq "" 'save this temp record
number
8 Then: hold_rec=@rn; gosub prompt; end 'first time into this field
If: hold_rec ne @rn 'only do the report from the next record (-xa)
9 Then: gosub do_rpt
10 Then: end
do_rpt If: 'do_rpt
11 Then:
If: 'run the report using the previous record number to stand on
12 Then: system "rreport stand -f simple_usage -u -sr" < hold_rec <
params
If: 'get the previous record after report is done
13 Then: lookup del=stand r=(hold_rec) -p
If: 'delete the previous record
14 Then: delete del
If: 'delete the current record
15 Then: delete
If: 'get out of rclerk
16 Then: exit
prompt If: 'prompt
17 Then:
18 Then: cls("21")
19 Then: showctr("22") "Enter the criteria for this report"
20 Then: showctr("24") "Press \K4 to run the report, \KY to cancel."
21 Then: return
So, you may think this was a long way to go... but there were all those
things which had to be gone over first so you would understand the final
actual _need_. Otherwise, you might have ignored this little note and never
learned this reasonably cool trick. I am very interested in seeing other
ways to accomplish this same thing. Having kicked my butt for a few hours of
pondering, I was almost tempted to give up and just throw away my
run_from_clerk methodology... reverting to the less sophisticated report
interface... but you were willing to read this far, so I was willing to suss
it all out. I'm glad I did. I find myself using this gimmick over and over
again. Hope you don't feel like it wasn't worth the reading journey.
(By the way, I am nearly ready to release my "spoken" movie-based
tutorials... They are so much a better way to do this kind of thing. No
reading (well, not much), just listening and watching what I'm doing, then
trying it out yourself. More about them later.)
Take care,
John Esak
Visit The FP Room www.tinyurl.com/yuag7 24/7
More information about the Filepro-list
mailing list