
This file contains information about:
REPTW : Report Variables                          

******** THREADS2.DBF, 2.0 BETA MESSAGES ********

Msg#:  20347   Date:  24-May-91
Fm: Willie Lam 73040,3330
To: sysop (X)

Possibly a bug, definitely an oversite ...

Environment:   Related databases with 1-to-many relationship set
Creating a new report form Master database currently selected

Problem:      Attempting to define a field in the report (anywhere)
that will count the number of master database records reported on.
When attempting to do this with a CALCULATE field, it always counts
each composite record individually.  What I want is to count the REAL
number of              master database records. What I tried was to
set a COUNT field on any field in the master database and set the
SUPPRESS repeated values ON.               (I know that was not the
intention of that function.)

I hope this makes enough sense to decipher!

Msg#:  20693   Date:  25-May-91
Fm: Lisa Slater 72077,2417
To: Willie Lam 73040,3330 (X)

Willie,

This should be do-able.  How are you defining the relation?

I got this to work with "main" selected, as you have it, and SET SKIP
TO children.  The detail records print info from both the main and the
children, so sometimes there are several entries for one main.

OK so far? <s>

I define two report variables:  the first one evaluated (that means
the first one in the list), x stores this expression:

IIF(RECNO("main") = y,0,1)

.. and I tell it to ***SUM*** in the calculate area.

Then I define variable 'y' to store the value of RECNO("main").

I can print x (make sure you make it sufficient chars wide, since the
RW doesn't know how to do that itself!) anyplace I like and it comes
out correctly for the current number of main records that I've gone
through up to that point.

>L<


Msg#:  27672   Date:  24-Jun-91
Fm: Lisa Slater 72077,2417
To: Alan Schwartz 70033,146 (X)

I've been out of touch for a few days, sorry.  Josh's bar mitzvah.
But I've done this (even wrote it up for the book, I think <g>).

You need to set up another report variable that indicates when the
master record has changed.

If this isn't enough of a "hint" (for you, Alan, it usually is <g>)
let me know and I'll flesh it out for you.  I'm sort of late tonight
and I may not say this exactly right, because I'm not looking up the
reports in question.
Basically, the report variable I list first is called newrec.
Initialize it to 0.  Store a value like this to it:

IIF(RECNO("master") = x,0,1)

The report variable x is also intialized to 0.  It stores the value of
RECNO("master") to x.

The various things I have to do (usually with more report variables)
that involve the master records all store values that are something
like this (I'll use your invoice case, where this is going to be
summed)

IIF(newrec = 1, invamt,0)

I deliberately use the 0 and 1 instead of .t. and .f. or a number of
other approaches for one simple reason:  it makes it very easy to get
a running *count* of master records...

So, to answer your various other messages on this thread:

Yes, you are overlooking an obvious solution!!!!

And no you don't need a UDF to do this.

>L<

Msg#:  28613   Date:  27-Jun-91
Fm: Lisa Slater 72077,2417
To: Alan Schwartz 70033,146 (X)

Alan, of course the form IIF(tempinvno = inv.invno,0,1) was only a
suggestion. As I said, I was doing item *counts* and found that form
convenient. It would allow you to get the  330.00 subtotal you're
looking for by doing this as a sum'd report expression:

IIF(2nd_r_var = 0,amt,0)  (I may have this backwards.)

But you could do this directly in the report expression by summing
this:
IIF(1st_r_var = inv.invno,0, amt)

as a report expression *without* the 2nd report variable, I think.
Maybe you'll find this less convoluted for endusers?  I think the
reason I was using the 2nd variable is that there were a bunch of
things that I might want to do based on the comparison of the invoice
number and I found it clearer to do it once and then use the result
all over than to make the comparison in each report expression.  It
would be clearer still if I hadn't been doing an item count and made
the result .T./.F. rather than 0/1.

It also allowed another level of calculation to be done on top of it.
Let's say this subtotal is by customer. If I had to put the *average*
invoice sum amt for my customers somewhere in the report, I'd want to
have the subtotal itself as a summed report variable (not report
expression) so it could be further manipulated.

>L<

Msg#:  28628   Date:  27-Jun-91
Fm: Alan Schwartz 70033,146
To: Lisa Slater 72077,2417 (X)

You've got it correct, Lisa, and I understand.  I was responding to
Cathy's message about using SQL - SELECT -- it seemed she hadn't
understood the question I'd originally posed, so I rephrased it, not
to nag the point further, but for clarity.

Once you've got temp_rvar holding the current group key, a 2nd
r_variable can indeed return 0 or 1, 0 or amount, or any other data
item.  In fact a 0 or 1 result is handy, since you can multiply it by
any other number in the master record...

new_key = IIF(temp_rvar=inv.invno, 0, 1)  && tests on record movement
temp_rvar = inv.invno                     && stores the key from
current rec

then you can sum any expressions built like this:
new_key * inv.invnet ...
new_key * inv.freight ...
new_key * inv.invgross
etc.


Msg#:  28652   Date:  27-Jun-91
Fm: Lisa Slater 72077,2417
To: Alan Schwartz 70033,146 (X)

Right, it *is* handy -- and I like what you're doing a *lot*, it's
exactly what I had in mind -- but it ain't necessary <g>.  You can do
the same thing directly in the report expressions you're summing, most
of the time. (Sum IIF(temp_rvar = inv.invno, 0, inv.freight) as a
report object.)

Since doing without the second report var means you don't have to
explain about the order/relationship of report vars to one another, I
was just suggesting that you might want to do it this alternative way
with your end users/trainees.

I don't mean to take you to task over this one -- but I just got my
DevCon stuff and they want me to do the RW session <groan -- I thought
they promised I wouldn't have to do this!>, so I'm honestly trying to
figure out the best ways to explain some of this stuff to beginners...
your opinion is appreciated, as always.

The way I see it, they lose the ability to do additional calculations
afterwards but they get what they'll need most of the time.  Then you
can introduce the order of report variables later, and stick new_key
in the 2nd report variable as another issue when they need to express
some relationship between inv.net and inv.freight sums.

What do you think -- is it easier to explain this stuff in two steps,
or one?

>L<

Msg#:  5477   Date:  21-Mar-91
Fm: Lisa Slater 72077,2417
To: Simon Clark 100014,2602

Nope (what's with the Simon Punch/Simon Clark?)  -- the question as I
understood it then had to do with group footers showing such a result.
A big advantage of 2.0 is that you can use a report variable that's
been totaled for a group as part of a calculation to get a percentage
(or other calculated) result for the group and show that.  You
couldn't do this with totaled fields or expressions before.

But that's different from each detail *line* showing a result of a
calculation that requires "knowledge" about all the detail lines for
the group. If you want that, you still have to get that pan-group
information first (in the group header), yourself, before you go
through the detail lines.  Or so I grok it at the moment.

In 2.0 I think you'll have the advantage of sticking that information
into a report variable instead of one you need to initialize
elsewhere. This would be a *big* help. All your detail-line percentage
calculations will then go ahead and use that variable's value.  I
think you'd initialize this variable to the result of a UDF (so it
would be correct for the first group) and store the value of a UDF to
it, to re-set on group. But each time you'd really be storing the
value for the *upcoming* group, not the one just finished... I've gots
to think this one through but you see where it's going... <g>
>L<


******** THD201.DBF ********
Msg#:  125002   Date:  30-Jul-91
Fm: Lisa Slater 72077,2417
To: Mike Giunta 70262,3264 (X)

Yes, you can skip every 10th line.  No problem!

Create a report variable, I call it xx below, INITIALIZE it to 0,
STORE the value 1, set it to COUNT.  (If you want to watch how it
works, or if you would like linenumbers in your report, just put xx in
as an expression somewhere on your detail line. )  You may or may want
to choose to RESET on the end of page; that's up to you.

Now one of your report expressions should do this:  construct it as
follows and use the FORMAT @;  :

    the_real_expression + IIF(MOD(xx,10) = 0, ";"+ " ", "")

Got that <s>?

>L<

Msg#:  127423   Date:  03-Aug-91
Fm: Lisa Slater 72077,2417
To: Jim Murdock 76012,2610 (X)

To get your group break on the basis of 7 days from your original
report date, try something like this -- it may not be the most obvious
way, but it's what comes to mind.  It turned out to be a little more
complex than I thought, if I don't assume you have data for every
single day (this method will work either way).

There might be an easier way to do this with functions and a UDF that
does not occur to me at the moment.  This way, I think, is *faster*
than UDFs, and we're having a special on report variables this week
<g>.

Start with one report variable, let's say zz, initialize that variable
to rdate, leave re-set on end-of-report.

Store its own name to it (not rdate, zz).  What we're doing here is
keeping the *first date* in the report available at all times, it
never changes.
Next report variable is yy. Initialize it to 0, again don't worry
about re-set, store this value to it:

IIF(rdate - xx >= 7,yy+1,yy)

The trick here is that yy *IS OUR GROUP EXPRESSION*.  The value of yy
is only going to change at the appropriate time.

The last report variable (and they *must* appear in this order on your
report variable list!), xx, is initialized to this expression:

zz+(yy*7)

Again, store its own name to it (xx).  *AND RESET IT ON GROUP*. You
won't get that option until you've set up the group expression in the
report.
Do you see what's happening here?

>L<


Msg#:  127462   Date:  03-Aug-91
Fm: Anders Altberg 100021,232
To: Jim Murdock 76012,2610 (X)

Jim,

You can group by week by applying a function to any date that returns
the number of the week as counted from a basedate equal to day 1 of
week 1 of the year:
INT((any_date - basedate)/7)+1
The trick is to specify a suitable base date. In the official Swedish
calendar day 1 of week 1 is the nearest Monday within 3 days of
January 1. If Jan 1 is a  Friday, Saturday or Sunday, it'll be the
following Monday, otherwise the preceding Monday. Thus curiously
enough, week 1 can start December 29 of the year before for the
purpose of this function. In an American calendar the week starts on
Sunday, though.
January 1 1991 was a Tuesday so I use BASEDATE=CTOD("12/31/1990") and
for an American calendar I'd make it "12/30/1990". You can make a
function to calculate this too, based on what
DOW(CTOD("01/01/any_year") returns. It works well for me as a grouping
expression in the report writer.
- Anders

Msg#:  140383   Date:  28-Aug-91
Fm: Stephen Downey 71630,330
To: Lisa Slater 72077,2417 (X)

Lisa,
The SET SKIP, RELATIONS, etc were obvious (meaning I spent *weeks*
working on them in DBIV), but how did you solve the other half of the
problem, that is, restricting then number of records per account to
15. I could use that...           -SMD


Msg#:  140510   Date:  28-Aug-91
Fm: Lisa Slater 72077,2417
To: Stephen Downey 71630,330

It's like this, Stephen:

Two report variables, both of them reset on group if you're grouping,
if not you can reset them on the end of the report. In Chip's case, if
each report is a discrete invoice for a single account/customer, you
reset on end of report. In your case, probably reset on group.

One of them, LCOUNT, initialize to 0 and it doesn't matter what you
store (I use .T.) -- you just do a count on it. This one is giving you
your running total of detail lines for the group or (in a non-grouped
report) for the report as a whole.

The other one, LASTREC, initialize to a UDF and *store its own value
to it, so it does not change*.  The UDF, which I call whichrec(), does
this:
  PRIVATE xx, thisrec
  xx = RECNO("parent")
  thisrec = IIF(EOF("children"),0,RECNO("children"))
  SCAN WHILE RECNO("parent") = xx
     thisrec =  RECNO("children")
  ENDSCAN
  GO xx          && go back to the beginning of this group
  RETURN thisrec && last child record of this group

As you can see, since the UDF is called in the initialization, it just
needs to be done once per group, not too bad at all.

Now you need *one* more UDF as a report expresion at the *very end* of
your detail line:

  FUNCTION Skiprecs
  PARAMETERS limit   && pass it the number of detail lines you want
  IF ! EOF("children") AND lcount = limit
     SCAN WHILE RECNO("children") # lastrec
     ENDSCAN  && go to the end of this group of children
  ENDIF
  RETURN ""

.. this will bring you to the end of this SET SKIP group and the RW
will move, by itself, appropriately to the next record (which will be
the next parent record).

Got it?

>L<




******** THD202.DBF ********
Msg#:  145593   Date:  08-Sep-91
Fm: Lisa Slater 72077,2417
To: Dave Marsh 72510,2326 (X)

I *do* have a GETVAR dialog, Dave.  That's exactly what I am talking
about. I am perfectly aware that the thing has to be there *before*
the report is run.

I'm not sure you understood me. I insulate myself from this problem
with a report variable that takes the value of the outer one, where
available, and asks for help, where not.  I have certainly never
resorted to a SUSPEND!!
My rept vars are initialized like this, to use your example:

   NAME:           Rcustomer
   STORE VALUE:    Rcustomer
   INITIALIZE TO:  IIF(TYPE("customer") = "C", customer,;
                        LOCVAR("customer","XYZ Company","@!"))
   RESET ON:       End of report.

The first param is used for a question/reminder in LOCVAR:

"Pick a value for the "+param1+" variable: "

The second param is a default value (btw my function works with all
data types).

The third is optional and has pictures/functions -- I mostly use it to
size numbers.

In the actual program, the inner (report variable) takes its value
from the outer (customer) variable.  It is Rcustomer, not customer,
that appears in the report expression!  In my tests, I get prompted to
stick something in, and mostly I just accept the default -- you could
avoid the whole LOCVAR() problem by simply initializing Rcustomer like
this, you realize:

    IIF(TYPE("customer") = "C",customer,"XYZ Company")

.. voila.  So, yes, Fox could have provided it but you are *not* stuck
with the problem.  Get it now?  Repeat after me "Report variables are
not just for grunt work <g>."

>L<


Msg#:  145379   Date:  08-Sep-91
Fm: Eric B. Freedus 73467,1113
To: Lisa Slater 72077,2417 (X)

Lisa,
I have not played with the RW at all.
But from what I read, if I want a report to appear:

name   amount    percent_of_total_of_amounts

Joe      10             20
Mary     15             30
Ed       25             50
  total  50

In order to get the percent column in detail lines, do I still need a
udf() summing "amount" to xxx (or pass the sum from the calling
program) for column 3? ie (amount/xxx)*100 as the field value?  IOW,
report variables aren't created "up front" with a separate "pass" the
way a UDF is, are they?  I've only seen reference to them in summary
lines, not detail lines.
TIA,
Eric

Msg#:  145450   Date:  08-Sep-91
Fm: Lisa Slater 72077,2417
To: Eric B. Freedus 73467,1113 (X)

Ah, funny you should mention that, Eric.  I just finished doing my
DevCon RW presentation notes, and percent-of-total in the detail line
like this is one time that UDFs are not only still useful, they are
*still required*.
HOWEVER, they are changed in two important ways!  There is no longer
*any* reason, even with a calculation on top of a calculation, to do a
UDF every detail line (which in some cases there were in 1.02,
although not in your example).  Whenever you need to know values for
the whole group *throughout the detail lines of the group* as opposed
to in the group footer/summary, you do something like this at the
beginning of each group, much like before:
  PRIVATE thisgroup, thisrec
  thisgroup = the_key_field
  thisrec = RECNO()
  CALCULATE SUM(amount) ;
     WHILE the_key_field == thisgroup ;
     TO sum_amt
  GO thisrec
  RETURN sum_amt

.. but set the variable Sum_amt up as a rept variable so you don't
have to "manage" the process.  In most cases, you can initialize
Sum_amt to the UDF() and store Sum_amt to it (so it doesn't change by
itself), re-setting on group. But this appears not to work properly at
the moment with nested groups in some circumstances and besides
prevents you from storing more than one value for the group in one
CALCULATE pass; so I actually initialize the (various) report vars
necessary to 0, re-set on end of report, store their own value to
them, and do them all in one UDF that RETURNS "" and is stuck into the
group header.
Either way, you can see the savings.  In my example, I show the
percent of total and then the *cumulative summ* of percent of total as
we march down the details, which was one of those real slow PITA
UDF-in-the-detail-line in 1.02.



******** THD203.DBF ********
Msg#:  171092   Date:  01-Nov-91
Fm: Lisa Slater 72077,2417
To: tom hubbell 70032,243 (X)

You can do print two passes for the details if you want... there are a
couple of different ways.

First off, you need a report var reset by employee group to a UDF.
The UDF saves the record pointer position and then does a SCAN while
empfield == this_employee and then SKIPs -1, so you can find out what
the recno() is for the last record of this group. It GOs back to the
first record, and RETURNs that last recno for the group. Store this
report var's own name to it, so it doesn't get changed on the detail
lines.

A second report var is set to .T. , reset by group, and has its own
value stored to it. A third report var stores recno() for the first
record in this group. It too has its own value stored to it.

A UDF at the right end of the detail band checks to see if we're on
the record saved in reptvar1. If we are and reptvar2 is .T., it stores
.F. to reptvar2 and GOs reptvar3 (so it goes back to the beginning of
the group).
All your detail line items have IIF()'s in them.  They print a certain
way for the first pass and another way for the second pass, by
checking the condition of reptvar2.

Totals are accumulated in report variables, also.  You have one detail
line that does the totalling, which follows the regular detail line.
It too has items with IIF()'s in them:

   IIF(reptvar2 AND RECNO() = reptvar1,"Total Work Hours: ","")
   IIF(reptvar2 AND RECNO() = reptvar1, worktotal,"")

.. IOW it only prints at the end of your first detail pass.  Make sure
to check off "suppress blank lines" in your report layout options
dialog.  You can use the actual group footer to print the totals for
the second detail pass, or you can overlay the two expressions above
with two more that do almost the same thing:

   IIF(!reptvar2 AND RECNO() = reptvar1, "Total Expenses: ","")
   IIF(!reptvar2 AND RECNO() = reptvar1, exptotal,"")

>L<


Msg#:  173368   Date:  05-Nov-91
Fm: Lisa Slater 72077,2417
To: Paul Bienick 71510,3217


It is very easy to save the pageno of the report.  All you have to do
is create a report variable that stores _PAGENO to it and un-check the
option to "release at end of report".  Then it will "persist" and you
can use in the definition of your second report.

This is exactly what I meant when I said there were "better ways to do
it" than calling a 2nd report from a UDF in the first, which is what I
thought you were asking me about.  Basically, even if you had lots of
these -- let's say you had to have these summaries after groups
instead of just at report end -- I always end up doing it like this
(obviously one is indexed on the group expression):

   DO WHILE ! EOF()
       mgroup = the_group_expr
       REPORT FORM the_main WHILE the_group_expression == mgroup
       SEEK mgroup
       REPORT FORM the_summary WHILE the_group_expression == mgroup
   ENDDO

.. this works well because the WHILE will leave you on the first
non-matching record.  Sorry for the dissertation, which may have
nothing to do with your current problem <g>.  But you certainly don't
have to settle for writing a report with a fixed number of zones, I
just took the easy way out because that *might* have been your
situation. <g>

There *is* a way to automatically adjust the
report-for-fixed-#-of-zones programmatically to handle more zones if
necessary, but I don't think it's worth it, frankly.

>L<


******** THD206.DBF ********
Msg#:  220539   Date:  19-Feb-92
Fm: Randy Brown 71141,3014
To: All

Has anyone out there figured out an easy way to do group/summary
averages on a field excluding null/zero values. I know you can create
report vars to count for <field>#0 and have that divided by another
report var which sums the <field>. This is fine for a few, but I have
a report which I want to show 10 fields. Under this scenario, if I
wanted to have a single group average and a grand average, I would
need 40 report variables.

Any way to do this without a lot of overhead?

RandyMsg#:  221423   Date:  20-Feb-92
Fm: Lisa Slater 72077,2417
To: Randy Brown 71141,3014 (X)

Hi Randy,

I don't think there is an easier way.

If you prefer, however, you can do this with one UDF, at the beginning
the group footer band. You'd still have to initialize a bunch of
variables, though.

    thisgroup = groupexpr
    SEEK thisgroup
    CALCULATE WHILE groupexpr == thisgroup ;
               SUM(field1), SUM(IIF(field1 = 0,0,1)), ;
               SUM(field2), SUM(IIF(field2 = 0,0,1)), ... (all fields)
               TO sum1, count1, sum2, count2, .... (etc)
    RETURN ""

 Now your report expressions become:  sum1/count1, sum2/count2 ...
.. And you'd have a similar UDF in the report summary band of course.
I'm not really sure that this would be easier, in fact I think just
letting the reptvars take care of it would be faster -- but this way
you could initialize all the variables as one two dimensional array if
you wanted. The first column would be the sums, the second column
would be the count of non-0 items, and the rows would be the number of
fields in which you were interested.  Might look a little "neater"
than 40 report vars, and you'd only need 20 elements rather than 40
since the same set could be used for the grand totals in the summary
band UDF as you used for the group totals.

>L<