Two for the road, Report from Clerk & Nonstandard Subtotals
tom heine
tch at aljex.com
Mon Mar 14 04:30:37 PST 2005
Hi John,
An easy improvment is to use the putenv command instead of using
a parameter. in some cases, we give the option for 20-40 selection critera
in with putenv. its just more flexable to put them each in separately,
and then use the getenv to see what to select. Our main selection screen
is getting pretty full.
Howie took it a step further and made it so we can put into a filepro file
the file name, report name, selection set, auto prc, input prc of the
report you want to run. Then for each record in the reports file, it will
show a selection in a listbox. It makes it very easy to add or edit a
report. He also made it to you can define the report or processing
from the report generator as well. We also made samples of our
reports, and give the user an option to just view a sample of the report,
in their web browser so they don't have to run it to find out what is on it.
Also, while you are at it, we use something we call destprt for the 2nd half
of this. You print the report to a file, and then destprt will either print,email,
fax, turn it to pdf, turn it to excel, turn it to word doc, or display on screen.
One nice part is that you run a report, can display it on screen, then decide
if you want to print, fax,email etc. Every single report in our system runs
through this, and an excellent side benefit is that if we need to change
something, faxing for instance, we only need to change it in one place.
Brian White will make the code available to the list if anyone wants it.
Tom Heine
Aljex Software
----- Original Message -----
From: "John Esak" <john at valar.com>
To: "Fplist (E-mail)" <filepro-list at seaslug.org>
Sent: Monday, March 14, 2005 3:51 AM
Subject: Two for the road, Report from Clerk & Nonstandard Subtotals
>
> (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
>
>
>
>
>
>
>
> _______________________________________________
> Filepro-list mailing list
> Filepro-list at lists.celestial.com
> http://mailman.celestial.com/mailman/listinfo/filepro-list
>
More information about the Filepro-list
mailing list