How To Print Custom Statement Subtotals

This is an ***ADVANCED*** topic.

Requirements

- Use of variables and WHERE clauses
- Designing reports

When designing statements, you may want to create subtotals based on transactions types listed in the statement. The way to do this may not be obvious.

For example, you might want to provide subtotals of all transaction types 201 (Legal Fees) and 205 (Legal Advances).

The following instructions show you how this is done.

Introduction

The general structure of a Statement Report includes:

- Header information.

- A list of all transactions accounted for in the statement.

This is the section that contains the transaction details we will reference in our report.

- A summary section with subtotals and totals.

Procedure

To examine the transaction details, we will proceed as follows:

1. Declare the variables that will hold our subtotals.

2. Add to the subtotals if the transaction types are 201 or 205.

3. Print the subtotals at the end of the report.

Declaring The Variables

At the top of your Report Body, add the following code:

@varLegalAdvances = 0
@varLegalFees = 0

Always declare variables at the top of your Report Body. We will use our variables for subtotals. If you want to generate subtotals for more than just the example transaction type we are using, please declare your additional variables here.

For more information on using variables, please review the online help and How-To examples accessed when you are editing a report body.

Adding Up The Subtotals

The following procedures must be followed accurately in order for this to work.

We will fetch a debtor based on the file number in the invoice/statement line item.

Then we will check the debtor's transactions to find the matching transaction type.

Then we will save the dollar amounts from the transaction to the variables we declared above, but only if the transaction matches the type we want. It's just four things - it's pretty easy.

Here is the section of a sample invoice body that does this:

@is.li No Total
@de WHERE (@de.fi = @il.fi) MAX = 1
@de.tr no total where (@tr.pda = @il.da) where (@tr.di = @il.di) where (@tr.ca = @il.co) max = 1
@varLegalAdvances = @(varLegalAdvances+tr.ca) if (@tr.ty = 205)
@varLegalFees = @(varLegalFees+tr.ca) if (@tr.ty = 201)
@de.tr
@il.da @de.na @de.sta @de.ow @de.ac @il.su @il.tu>12> @il.di>12> @il.co>12> @il.re>12>
@de
@is.li

There are a few interesting things to note in this code:

The @is.li statements mark the start and the end of the line item record iteration. In other words, for each line item in the statement the report section between the @is.li markers will be run one time.

The statement @de WHERE ... actually goes
into the database and fetches the debtor record that has the file number stored in the invoice line item.

The @de.tr statements mark the section where we search through the debtor's transactions to find a transaction with the same payment date and commission amount as the transaction.

The two places where we add up the legal fees accumulate the transaction commission amount.

@varLegalAdvances = @(varLegalAdvances+tr.ca) if (@tr.ty = 205)
@varLegalFees = @(varLegalFees+tr.ca) if (@tr.ty = 201)

Printing The Results

So finally after all this, we get to print the subtotals with the following code:

Legal Fees ....... @varLegalFees>12>

Legal Advances ... @varLegalAdvances>12>