Reporting with dBASE Plus: A Tutorial Michael Nuwer October 28, 2002

Goals and Objectives

The goals for this Session are:

• To use conditional aggregates;
• To use the immediate if function;
• To create aggregate summaries for a page;

Introduction

Reports often need to count items and sum subtotals. Typically this is done for a range of data by adding a group (or groups) and using the built in aggregate functions. The dBASE reporting tools permit you to perform aggregate calculations (sum, minimum, maximum, count, average, standard deviation, variance) on fields within groups in a report and for the report as a whole.

In the dBASE application Tutorial, we used these aggregating functions in both the Invoice and Statements report. Sometimes however, the developer needs to aggregate data based on a conditional test within the group or over a range other than a group in the report.

The standard technique for summing numbers in a program is to declare a program-level variable to store the aggregating total. Then loop through the items and add the current amount to the total. Most programmers are familiar with something like the following:

   nTotalValue = nTotalValue + nCurrentValue

dBASE will do the same thing with += arithmetic assignment operators:

   nTotalValue += nCurrentValue

If you want to count something, such as the number of dogs in a litter, you need another program-level variable to store the counter. Then, while looping, increment the counter variable by one:

   nCount = nCount + 1

With dBASE we can also use the unary numeric operator ++ to do this:

   nCount ++

Lets look a bit more closely at counting and accumulating data in a report.

Conditional sums and counts

The first report is going to count items in a group based on a conditional test. Open the report named Counting1.rep in the report designer. This report groups female dogs who have had a litter and lists their children. The Children could be male or female and we are going to count for each sex. The results will be displayed in the footerband.

The idea for designing this report is to initialize a custom property when the group header-band renders; increment that property when each of the detail-bands renders; and then display the results when the group footer-band renders.

The first step is to create an onRender event handler for the group headerBand and add the following code.

  function HEADERBAND_onRender
      // headerband.group1.streamsouce1
      this.parent.parent.nMales = 0
      this.parent.parent.nFemales = 0
      return

The onRender event fires after the group headerBand prints. This is where we are going to initialize the two counters that will accumulate the number of male and female dogs. These custom properties are assigned to the streamSource1 object. In the code above, "this" is the group headerBand, its parent is the group1 object, and the streamSource1 is the parent of the group.

The second step is to create an onRender event handler for the detailBand. This is where we will increment our custom properties based on whether the field value is an "M" or an "F".

   function DETAILBAND_onRender
      if this.parent.parent.hunde1.rowset.fields['sex'].value = 'M'
         this.parent.nMales ++
      else
         this.parent.nFemales ++
      endif
      return

This event fires after the detailBand renders and continues firing for each detailBand within the group. The counters are thereby incremented for the group.

The final step is to display the counter values in text fields. The values will be displayed in the group footerBand, so we will assign the values in the preRender event handler. (We could instead use the canRender event handlers of the respective text fields.)

   Function FOOTERBAND_preRender
      this.kmFieldText1.text = {||this.parent.parent.parent.nMales}
      this.kmFieldText2.text = {||this.parent.parent.parent.nFemales}
      return

Now when the footerBand prints, the counters will be printed in the text controls. If there is more data for another group, the headerBand will print and the counters will be set back to zero.

When this reports runs you should see counts for the number of male and the number of female dogs for each mother dog.

Using the IIF() Function

A second way to create conditional aggregates is to use the IIF() function. This function is named "immediate if." It is a shortcut for the if...else...endif programming construct and it works like a spreadsheet's if() function. IIF() has three arguments: the logical test, the value if true, the value if false.

We can embed the immediate if function in an aggregate summary function, and thereby, generate a conditional summary. The aggregate functions work only on non-null values. So if IIF() returns a null, then the aggregate function ignores the row. This way the aggregate summary functions will operate only on rows where IIF() returns true.

In general the functions we are going to use in the example report look like this:

   agCount(iif(condition,field_value,NULL)

When we add the object notation to this expression, we will have the following (all in one line):

text = ;
   {||this.parent.parent.agCount(  
      {||iif(
       this.parent.rowset.parent.parent.dogs1.rowset.fields["sex"].value= "M",
       this.parent.rowset.parent.parent.dogs1.rowset.fields["sex"].value, 
       NULL  
      )} 
   )}

Open Counting2.rep in the report designer and add two kmFieldText fields to the report's group footerBand. Then enter the above expression to each of the text fields. One should evaluate sex as "M" and the other as "F."

When you run this report the conditional summaries in the footer-band should be the same values as we produced in the previous report.

Page Summaries

Another common question related to the use of counters is how to accumulate subtotals for a page. This example will print a numerical field and, for each detailed item, we will print another field that displays the cumulative sum of the numerical field.

Begin by opening Counting3.rep in the report designer. This is a simple list of Dams that show their name and the number of offspring they have delivered. Lets assume that we want to add a third column that shows the cumulative number of offspring on the page.

To do this we will need a custom property that starts as zero when a new page begins; add the numerical field value for each item prints in the detailband; and display that sum in a text field.

We will first create a custom property and set it equal to zero when the query opens. In the report find the Dam1 query object and add an onOpen event handler. The custom property will be attached to the report object:

    Function Dam1_onOpen
       this.parent.nPageTotal = 0
	   return

Next we need to add another kmFieldText object to the report's detailBand and add a canRender event handler. The text property of this object will be determined by a calculation and we will accumulate the nPageTotal property. Here is the code you should add:

    function KMFIELDTEXT3_canRender
       this.text = ;
       this.form.dams1.rowset.fields["Child"].value + this.form.nPageTotal
       this.form.nPageTotal += this.form.dams1.rowset.fields["Child"].value
       return true

Finally we need to reset the accumulator property (nPageTotal) each time a new page starts. To do this we will use the report's onPage event. Here's the code:

    function form_onPage
       this.form.nPageTotal = 0
       return

When you run this report you should notice that the values in kmFieldText3 are a cumulative sum and that they are reset for each page in the report.

The Legal Stuff: This document is part of the dBASE Reporting Tutorial created by Michael Nuwer. This material is copyright © 2002, by Michael Nuwer. dBASE, dBASE Plus, dBASE SE, and dB2K are copyrighted, trademarked, etc., by dBASE, Inc., the BDE (Borland Database Engine) and BDE Administrator are copyrighted, trademarked and all that by Borland, International. This document may not be posted elsewhere without the explicit permission of the author, who retains all rights to the document.