DataWindows - Aggregates of Aggregates: Providing Sums of Group Totals in the Summary Band of a DataWindow

When using computed fields in a datawindow it is not possible to compute aggregates of aggregates, meaning that, if you have a computed field in the group trailer band of a datwindow you cannot summarize that information in a calculated field located in the datawindow's summary band. The reason for this is that the aggregate data is computed at run time and is not stored anywhere which makes it unavailable for another aggregate function to act upon. It is possible to summarize the calculated data in a script and then write the result into an object that has been placed in the summary band of the datawindow for that purpose.

A sample scenario would be a datawindow which retrieves rows from a table containing employee data (first name, last name, department, salary, etc.) and groups those rows by department. A calculated field in the datawindow's group trailer band provides a salary total for the department. A salary total for departments with dept_id=200 or 300 is required in the summary band of the datawindow.

The following script, when placed in the RetrieveEnd event of the datawindow, will find each group break, add the value of the calculated field for that group to a summary variable and then write the grand total to a text object which was placed in the datawindow's summary band for that purpose. To provide a consistent look and feel to the report, make sure that the font for the text object is the same as the other objects in the datawindow. Note that the RetrieveEnd event has a single argument, "rowcount", which is the number of rows that were retrieved. The name of the calculated field in the group trailer band is "sal_tot" and the name of the text object in the summary band is "dept_200_300".

long brk_row = 0,cnt
decimal{2} g_tot, lsum=0

For cnt = 1 to rowcount
brk_row = this.FindGroupChange(brk_row,1)
If brk_row <= 0 then Exit
     IF this.GetItemNumber(brk_row,'dept_id') = 200 or &
  this.GetItemNumber(brk_row,'dept_id') = 300 THEN
  g_tot = this.GetItemNumber(brk_row, 'sal_tot')
  lsum = lsum + g_tot
END IF
brk_row = brk_row + 1
Next
this.object.dept_200_300.text = string(lsum, "$#