Share via

VFP - 2 Methods to Report Totals in the Header of a Data Grouping

  • Article

Sometimes reports call for a total of a field in the detail section of a report. This is easy to accomplish when the total can be given at the end of the Details records. Within VFP you create a data grouping based on the field. Add a reporting field to the Group Footer band and set its attributes to Sum the required field.
Nothing new or difficult here.

However, what about when the total of the Detail records is required in the Header Band - before the Detail records have been encountered/printed.
Not so easy.

One method would be to add an additonal field to the reporting cursor to indicate a "Total Record" and at the same time via an SQL Select statement produce total values based on the group field and add these records into the reporting cursor. Then report these total records in the Header Band of the group within the report. Doing this would of course require the program to be changed to perform these tasks. Thats all well and good when you know in advance of the reporting requirement to give totals in the header band.

But how do you accomplish this when the requirement is not foreseen?

In other words, you want to be able to amend an existing report or create a variant of it without changing the routines that create the reporting cursor.

1st Method
 **************
First we need a function to perform an SQL Select and summing, then to return its value.
Three paramters are used:

  1. the field to be summed
  2. the name of the field on which the Where clause or grouping is to be based
  3. the value of the group whose total is required.

Hence:
*********************************
FUNCTION gettotalfromcursor
LPARAMETERS p1, p2, p3
* p1 is field name to be totalled as char
* p2 is field name of group as char
* p3 is value of group field for which total is required
* report cursor is always rc - could be an additional parameter
 LOCAL lrtn, ls1
DIMENSION zzztot(1)
m.ls1 = "SELECT SUM(" + p1 + ") as zzz FROM rc WHERE " + p2 + ' ='

DO case && deal with each type of variable that can be passed
CASE VARTYPE(p3) = CS_L
m.ls1 = m.ls1 + iif(p3, " .T. ", " .F. " )

CASE VARTYPE(p3) = CS_D
m.ls1 = m.ls1 + "{^" + dasheddate(dtos(p3)) + "}" && date into string format

CASE VARTYPE(p3) = CS_I
m.ls1 = m.ls1 + STR(p3, 10)

CASE VARTYPE(p3) = CS_Y
m.ls1 = m.ls1 + STR(p3, 12, 2)

CASE VARTYPE(p3) = CS_N
m.ls1 = m.ls1 + STR(p3, 16, 6)

CASE VARTYPE(p3) = CS_T
m.ls1 = m.ls1 + convertdatetimetochar(p3) && date time into string format

OTHERWISE && CS_C and others
m.ls1 = m.ls1 + '"' + p3 + '"'

ENDCASE

m.ls1 = m.ls1 + ' INTO ARRAY zzztot'
* perform the select statement
&ls1
if _tally > 0 then
   m.lrtn = zzztot(1)
ELSE
   m.lrtn = 9999999.99 && default return value when no records selected
ENDIF

RELEASE zzztot && the array is no longer required
RETURN m.lrtn && return total
*********************************

Now we need to look at the report structure.
A Report Variable is required - RptCalcdTotal
Whose "Value to Store" and "Initial value" are 0 (zero) and "Reset value based on" is set to the group field.
A reporting field is added to the Group Header Band to report this variable.
Now right click the Group Header Band and select Properties.
Now set the "Run Expression - On entry" to

_vfp.SetVar(@RptCalcdTotal, gettotalfromcursor("tamt", "tbno", rc.tbno))

_vfp.SetVar() just assigns the value for the second parameter to the variable passed in the first parameter
TAMT is the field to be totalled
TBNO is the data grouping field
rc.tbno is the current value of the grouping field whose records are to be totalled.

That's it done.
A variant would be to handle multiply fields to be used in the Where condition of the Select statement.

2nd Method
*************
Under VFP Version 9 there is another way of doing this without having pre-defined the Function gettotalfromcursor() to determine the Sum amount.
This is how:

  1. for the defined report add a new report variable (say HBTot) under menu Report Variables. Set the "Value to store" and "Initial Value" to zero.
  2. Within the header band add a new field and on the field properties General tab set the expression to HBtot. Under Format set the numeric format required. (These 2 steps are as normal for adding a report variable.)
  3. Right click the header band and select Properties.
  4. Click Other tab on the Group Header Band Properties dialog. Then click Edit Settings under Run-time Extensions.
  5. On the resulting dialog window put BeforeBand into Execute when: attribute.
  6. Click the Code Zoom button to open the Run-time extension script window.
  7. Put in the following code to summate your Detail amount field via a Select statement changing the Select statement as required.
    Here the grouping value is in rc.dtype

LPARAMETERS toFX, toListener, tcMethodToken,;
tP1, tP2, tP3, tP4, tP5, tP6, tP7, tP8, tP9, tP10, tP11, tP12
* this assumes a 2 pass process in creating the report
if toListener.currentpass = 1 then
   local ls1
   DIMENSION aaattt(1)
   m.ls1 = rc.dtype
  select sum(amt) as tttamt from rc where dtype = m.ls1 into array aaattt
   _vfp.setvar("HBTot", aaattt(1))
   release aaattt
endif

  1. Close and Save all open dialogs

That's it done - the total of Detail records in the Group Header Band.