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