How to Sum or Count Specific Fields in a Report

ID: Q106659

2.50 2.50a 2.50b 3.00 | 2.00 2.50 2.50a 2.50b

WINDOWS               | MS-DOS

The information in this article applies to:

• Microsoft Visual FoxPro for Windows, version 3.0
• Microsoft FoxPro for Windows, versions 2.5, 2.5a, and 2.5b
• Microsoft FoxPro for MS-DOS, versions 2.0, 2.5, 2.5a, and 2.5b

SUMMARY

You can use a report variable in conjunction with the Immediate IF function (IIF) to count or sum a specific report field.

MORE INFORMATION

The IIF function returns one of two values, depending on the value of a logical expression. If the logical expression is true, the first value is returned. If the logical expression is false, the second IIF return value is returned.

NOTE: To count or sum ALL possible values of a field, do not use this report variable technique. Instead, sort the database on the field, and then create a data grouping in the report for the field.

To Count Fields

1. Create a report variable containing an IIF function.

2. In the logical expression of the IIF function, type the condition

   you want to count. In the first IIF return value, type "1" (without
   the quotation marks). In the second IIF return value, type "0"
   (without the quotation marks).

To Sum Fields

1. Create a report variable containing an IIF function.

2. In the logical expression of the IIF function, type the condition

   you want to test for when summing. In the first IIF return value,
   type the name of the value (which must be numeric) you want to sum.
   In the second IIF return value, type "0" (without the quotation
   marks).

Example Using FoxPro for Windows

This example creates a database of different types of computers and their prices. The report variable, DESKTOPCT, created in steps 5 through 8, counts the number of records whose type equals 'DESKTOP.' The report variable, DESKTOPSUM, created in steps 9 through 11, sums the price of all records whose type equals 'DESKTOP.'

 1. Use the following code to create the database:

       CREATE TABLE test ;
          (type C(20), price N(5))

       INSERT INTO test (type, price) ;
          VALUES ('DESKTOP', 1500)
       INSERT INTO test (type, price) ;
          VALUES ('LAPTOP', 2000)
       INSERT INTO test (type, price) ;
          VALUES ('DESKTOP', 4000)

 2. Use the Report Builder to create a new report.

 3. From the Report menu, choose Quick Report, then the OK button.

 4. From the Report menu, choose Title/Summary. Under Report
    Summary, select Summary Band. Choose OK.

 5. From the Report menu, choose Variables, then choose the Add
    button.

 6. In the Variable Name text book, type "desktopct" (without the
    quotation marks).

 7. Choose the Value To Store button, which opens the Expression Builder
    dialog box. Under Value To Store In Variable Desktopct, type the
    following:

       IIF(test.type = 'DESKTOP',1,0)

    Choose OK.

 8. In the Variable Definition dialog box, under Calculate, select
    Sum. Choose OK. (This variable counts the number of records whose
    type equals 'DESKTOP').

 9. In the Report Variables dialog box, choose the Add button. In the
    Variable Name text book, type "desktopsum" (without the quotation
    marks).

    Builder dialog box. Under Value To Store In Variable Desktopsum,
    type the following:

       IIF(test.type = 'DESKTOP',test.price,0)

    Choose OK.

    Sum. Choose OK twice to return to the Report Layout window. (This
    variable sums the PRICE field for all records whose type equals
    'DESKTOP').

13. Place a field object in the Summary band. In the Report Expression

    dialog box, choose the Expression button. Under Variables, double-
    click DESKTOPCT. Choose OK to return to the Report Layout window.

15. Place a field object in the Summary band. In the Report Expression

    dialog box, choose the Expression button. Under Variables, double-
    click DESKTOPSUM. Choose OK to return to the Report Layout window.

    DESKTOPCT displays "2," and DESKTOPSUM displays "5500."

Keywords          : FxtoolGeneral 
Version           : 2.50 2.50a 2.50b 3.00 | 2.00 2.5
Platform          : MS-DOS WINDOWS