Category : Dbase (Clipper, FoxBase, etc) Languages Source Code
Archive   : TN8911.ZIP
Filename : GROUPSUM.TXT

 
Output of file : GROUPSUM.TXT contained in archive : TN8911.ZIP
REPORTing by Group Sum
Tom Woodward
We have been assigned to create a sales report which should be grouped by
geographic area. Within that geographic area, the salespeople should be
ordered by their total sales from highest to lowest, and the groups should be
ordered in relation to their total sales, from highest to lowest.
Unfortunately, databases can only be ordered on fields or expressions. So
now we have a choice:

1. Tell our boss to take a hike; or
2. Do some creative work at the dot prompt.
Let's try choice "2" and see what happens. We'll keep choice "1" under wraps
as a backup.

First, let's assume our database "Sales" has the following structure:

Field Type Length Decimal
---------- --------- ------ -------
1) Emp_No Character 5
2) Salesman Character 25
3) Area Character 20
4) Amt_Sales Numeric 10 2
5) Mo_Ending Date 8

All fields are self-explanatory accept for the Mo_Ending field, which
represents the last day of the month/year that the record represents. For
example, if the record pertained to November 1989, Mo_Ending would hold
11/30/89.

Now, assuming we have created a report which groups on Area and sums Amt_Sales
properly (for more info on reports, refer to Chapter 9 in your Using the Menu
System manual), we're going to need to perform several procedures before our
report produces the desired output. First, modify the structure of Sales,
and add a numeric field called Tot_Sales, Length 12, Decimal 2. This is the
field that will be the primary key of the index tag that will drive our
report. Next, we must create a database which holds our sub-totals. To do
this, order the database on Area, and use the TOTAL ON command to create a
new database.


USE Sales ORDER Area
TOTAL ON Area TO SumDB
For those of you unfamiliar with the TOTAL ON command, this new database will
have two fields, Area and Amt_Sales. The number of records corresponds to

the number of different Areas, and Amt_Sales now holds the total sales for
each area.

Next, we need to relate the two databases, and replace our newly added field
with the totaled Amt_Sales.


SELECT B
USE SumDB
INDEX ON Amt_Sales TO Amt_Sales
SELECT A
SET RELATION TO Area INTO B
REPLACE ALL Tot_Sales WITH B->Amt_Sales
This puts the same group total value in each record for an area. The final
step, is to create the index which the report will run under. Remember, it
is necessary to have the records in Amt_Sales order within each area (I'm
using an .NDX Index because this entire process will need to be performed
after any change in the database).

INDEX ON STR(Tot_Sales, 12, 2) + STR(Amt_Sales, 10, 2) TO Perform
Note that ordering on Tot_Sales will keep the records in order by Area.
Therefore, it is not imperative that we go back into our report and change
the field to group on to Tot_Sales. Assuming we want to run our report for
November 1989, with our index activated, our Area Sales Performance Report
would be activated as follows:

REPORT FORM AreaPerf FOR Mo_Ending = {11/30/89} TO PRINT
We can now save Solution "1", presented early in the article, for a time when
we really need it. s



  3 Responses to “Category : Dbase (Clipper, FoxBase, etc) Languages Source Code
Archive   : TN8911.ZIP
Filename : GROUPSUM.TXT

  1. Very nice! Thank you for this wonderful archive. I wonder why I found it only now. Long live the BBS file archives!

  2. This is so awesome! 😀 I’d be cool if you could download an entire archive of this at once, though.

  3. But one thing that puzzles me is the “mtswslnkmcjklsdlsbdmMICROSOFT” string. There is an article about it here. It is definitely worth a read: http://www.os2museum.com/wp/mtswslnk/