Conditional Printing in Report


    This note demonstrates a method of making information contribute 
to a column in a report only when certain conditions are met.

    The example will produce a report of aging balances from a 
database containing the following fields:

         Invoice Number:
         Billing Date:
         Account Name:
         Invoice Amount:

    The Column/sort spec for the report is:

         Invoice Number: 2,AS,CS, H(:Invoice)
         Billing Date: 3
         Account Name: 1,AS, H(:Account)
         Invoice Amount: 4,I

    The report will have three columns which total amounts for 
invoices that are respectively, 0-30 days old, 31-60 days old, and 
more than 60 days old.  To do this, use the following derived columns.

         Heading: 0-30 days
         Formula: (@DATE-#3 < 31) * #4
         Column Spec: 5,ST,T

         Heading: :31-60 days
         Formula: (@DATE-#3 > 30 AND @DATE-#3 < 61) * #4
         Column Spec: 6,ST,T

         Heading: :60 plus days
         Formula: (@DATE-#3 > 60) * #4
         Column Spec: 7,ST,T

    Note:  The colons at the beginning of the heading for columns 6 
and 7 are indented.  They allow the headings to begin with a number.


    Programming Explanation

         (@DATE-#3 < 31)

    The portion of the formulas used which appear within parentheses 
are actually statements which can be true or false.  If the statement 
is true, it becomes a 1 when used in a formula.  When the 1 produced 
by the true statement is multiplied by a number from a field, the 
result is that number.  If the statement  happens to be false, it 
becomes a 0 when used in a formula.  Any value multiplied by the false 
statement results in a 0.  Thus, an invoice amount will appear in a 
column in the report only if its date fits within the specified range 
for the column.



Q & A Application Note       Rev. 6/92           #2212     Page 1 of 2


                    Conditional Printing in Report


    The report will look like this:

Account    Invoice   Billing Date  0-30 days  31-60 days  60 plus days
---------  -------   ------------  ---------  ----------  ------------
Acme Mfg.     1012   Jan 4, 1990       $0.00       $0.00        $45.00
              1013   Feb 5, 1990       $0.00      $66.33         $0.00
              1021   Mar 10, 1990     $23.66       $0.00         $0.00
                                   ---------  ----------  -----------   
Total:                                $23.66      $66.33        $45.00

Baker Co.     1016   Feb 8, 1990       $0.00       $7.89         $0.00
                                   ---------  ----------  ------------
Total:                                 $0.00       $7.89         $0.00

etc.

    The derived columns printed in this report will consist of zeros 
and amounts copied from column 4.  If you want blanks instead of 
zeros, use derived columns like the following instead.

         Heading: 0-30 days
         Formula: @TEXT(@DATE-#3 < 31,  #4)
         Column Spec: 5,ST,T,F(M,C)

    The @TEXT statement will produce 1 or 0 copies of the data in 
field 4.

    The F(M,C) in the Column spec tells Q&A to report the column as 
money with commas.  Other formats are available;  refer to Table R-3 
in your manual for a list.
























Q & A Application Note       Rev. 6/92           #2212     Page 2 of 2