User's Guide - Designing Reports

This user's guide helps you get starting creating your own reports and modifying existing ones already in Collect! or downloaded from the Report Library.

Report Design Basics

You can build your own reports to print anything you see in the program. Check the existing reports for examples of how the reports work.

Each field displayed on the screen may be defined in a report. Select Print from the top menu bar and then select Settings, Printable Information to view the complete list of fields.

Other options, such as font size and style may be set using Printer Control Codes. Select Print from the top menu bar and then select Settings, Printer Control Codes to view the complete list of available fonts and formatting codes.

Print Menu Settings

Report Definition

The Report Definition form is used to create or edit reports, to define the body of the report, as well as headers and footers.

Report Definition Screen

Name
This is the name of the report. Each report in the system must have a UNIQUE name.

Start On
This is the type of record which must be currently displayed when you start printing the report. To have the report available anywhere, enter the asterisk [*] character as a wild card.

Report Body
This section contains the body of the report. This is where you enter text, field identifiers to print database information and formatting codes for fonts and styles.

Header
The Header definition lets you define text to appear at the top of each page of the report.

Footer
The Footer definition lets you define text to appear at the bottom of each page of the report.

Options
This displays a form which lets you specify a default destination for the report and other options.

Report Body

The Report Body contains the text and database information that will be printed in your report or letter. Reports may contain text as you type it and codes which tell the system what database information is to be printed and how the printer is to format the output.

Before you start creating your own reports, please review the existing reports in the system to get a feel for what they are doing.

Normal Text
Type any text. It will print exactly as you type it. One limitation is that if you want to print the % character, you must type %%.

Report Body with Normal Text

Printing Codes
Special codes control what and how the report prints.

Report Body with Codes

• Printable Information codes print information from your database
• Printer Control Codes control fonts and styles, such as bold and double wide.
• Special codes are used to display date, time, page numbers and various other functions.

Codes always start with the @ symbol in report definitions.

Printing Database Information
Database information codes are listed in the Print menu, Settings, Printable Information option. Each form and field has an identifier which is used when printing the information.

Inserting Field Information

To print a database field, just type the identifier for the field into the report. For example, to print the Client Name, type @cl.na in the body of your report. Fields are printed exactly as shown on the screen, unless you use the field formatting rules defined here. The information in the printed field is the content of the CURRENT record of that type.

One powerful feature of the reporting system is that you can print information from different forms in one report. For example, you may want to print a list of all Debtors and print the Client Name for each debtor in the report. Since each debtor printed could conceivably be owned by a different client, we need a way to tell the system to fetch the correct client information from the database before printing the Debtor and Client Names. If we specify @cl.na, the report system will print the name of the last client fetched from the database, not necessarily the client belonging to the given debtor. To tell the report system to fetch the Debtor's Client, we need to be more specific. So we would use the code @de.cl. This code refers to the Client field on the Debtor form. The system knows the relationship between Client and Debtor records. The database engine will fetch the debtor's Client record and will print the correct Client Name.

If you build a report and find that the wrong information is being printed, you may not have told the report system to fetch only records related to the current record that is displayed. Examine the form you want to print information from to get a feel for the information contained in that record and information contained in related records.

To review the list of available codes, please select Print from the top menu bar and then select Settings, Printable Information.

A Quick Print to Spreadsheet creates a very useful list of all the Printable Information codes that you can refer to as you define your reports.

Formatting Codes

By default, fields are printed exactly as displayed on the form. Many options are available to control how output looks when printed.

Text Field Formatting Codes

If you print the Client Name @cl.na in your report, the name will print in a 62 character wide space. If the client's name is less than 62 characters wide, it will be filled with spaces. The next field or text will be printed after these spaces. In some cases, you may want to control the width of fields, their justification and other details.

There are several codes which may be used to define the way a text field is formatted in the report's output. These codes are placed right after the code for the field.

Some examples are:

<
Usage: @cl.na<

This code left justifies the field. If the field has empty spaces, this code trims the trailing spaces from the field.

Example: @cl.na< prints 'Joe Customer'.

>
Usage: @cl.na>

This code right justifies the field. If the field has empty spaces, this code displays the data right justified with the given space.

Example: @cl.na> prints '@nbsp;@nbsp;@nbsp;@nbsp;@nbsp;@nbsp;@nbsp;@nbsp;Joe Customer'.

<nn>
Usage: @cl.na<10>

This code prints nn characters wide (nn is a number). This option forces a field to be printed within a specified width. If the field is too large, it will be truncated. If the field is too small, it will be left justified and padded with spaces up to the specified width.

Example: @cl.na<10> prints 'Joe Custom'.

Example: @cl.na<20> prints 'Joe Customer@nbsp;@nbsp;@nbsp;@nbsp;@nbsp;@nbsp;@nbsp;@nbsp;'.

>nn>
Usage: @cl.na>

This code prints nn characters wide (nn is a number). This option forces a field to be printed within a specified width. If the field is too large, it will be truncated. If the field is too small, it will be right justified and padded with spaces up to the specified width.

Example: @cl.na>20> prints '@nbsp;@nbsp;@nbsp;@nbsp;@nbsp;@nbsp;@nbsp;@nbspJoe Customer;'.

For a complete list of available options for formatting text fields, please refer to help topic How To Format Text Fields and Text Variables.

Numeric Field Formatting Codes

Numeric fields may be formatted using the same codes described above. In addition, there are several codes that pertain explicitly to numerics.

Some examples are:

<t>
Usage: @cl.li<t>

Prints numbers as text. Numeric fields will be printed as words.

Example: @cl.li<t> prints 1000 as 'One Thousand'.

<.>
Usage: @cl.li<.>

Print fractional part of number only. In numeric fields with decimal points, you may want to print only the fractional part, for example, pennies in currency fields.

Example: @cl.li<.> prints 1000.56 as '56'.

<!>
Usage: @cl.li<!>

Print whole part of number only. In numeric fields with decimal points, you may want to print only the whole part, for example, dollars in currency fields.

Example: @cl.li<!> prints 1000.56 as '1000'.

<.nn>
Usage: @cl.li<.3>

Print fractional part in nn spaces. This is the same as text field formatting, truncated or padded with spaces to nn wide.

Example: @cl.li<.3> prints 1000.56 as '@nbsp;56'.

<!nn>
Usage: @cl.li<!6>

Print whole part in nn spaces. This is the same as text field formatting, truncated or padded with spaces to nn wide.

Example: @cl.li<!6> prints 1000.56 as '@nbsp;@nbsp;1000'.

For a complete list of available options for formatting numeric fields, please refer to help topic How To Format Numeric Fields and Numeric Variables.

Date Field Formatting Codes

There are several options for displaying dates in your reports.

Some examples are:

@de.li prints 06/20/00.

@de.li<MM dd yy> prints 06 20 00.

@de.li<ddd MMM dd yy> prints Tue Jun 20 00.

@de.li<dddd MMMM dd yyyy> prints Tuesday June 20 2000.

@de.li<dddd', 'MMMM dd', 'yyyy> prints Tuesday, June 20, 2000.

For a complete list of available options for formatting date fields, please refer to help topic How To Format Date Fields and Date Variables.

Printing Lists

Typically, there are many lists in a database that you may want to print, for example a list of clients or a list of debtors. Lists of records are printed by naming a form, or by naming a list within a form.

Example 1:

You could print the list of all clients using the following codes:

@cl <--- Start looping through the Client list
Client: @cl.na
Address: @cl.ad
<---The Client Name and Address @cl.a1 <--- will be printed once for each
@cl.a2 <--- client in the list.
@cl <--- End of information printed through the Client loop

The above snipped prints a list of all clients in the system, printing their name and address. There are some critical parts to this code.

1. The @cl statement refers to the
Client form. The fact that there are two @cl statements tells the system to scan through the Client list. You can see all the available fields from the Client form by selecting Print from the top menu bar. Then select Settings, Printable Information. The form name is the first set of characters before the dot '.' in the field codes for the form.

2. The @cl.na to @cl.a2
section will be printed once for every client in the list. You could put blank lines after the @cl.a2 field to provide a space after each client name and address.

Example 2

You could print a list of accounts belonging to a given client by using the following report snippet.

@cl.de <--- Start looping through the client's Debtor list
@de.na @de.li <--- Print the Debtor Name and File Number
@cl.de <--- End the loop

The above snippet will list all accounts in the system, printing their name and file number. There are some critical parts to this code.

1. The @cl.de statement refers to the
Debtor list in the Client form. The fact that there are two @cl.de statements tells the system to scan through that list. You must be on a Client form for this to work. You can see all the available fields from the Client form by selecting Print from the top menu bar. Then select Settings, Printable Information. The form name is the first set of characters before the dot '.' in the field codes for the form.

2. The @de.na line will be printed once
for every debtor in the Client's Debtor list. You could put blank lines before and after the @de.na line to provide a space before and after each record.

Conditionals

You can limit your printing to only those records which match certain criteria. To do this, we use a WHERE clause in the start of the Loop statement. You can even use multiple WHERE clauses, with Equals (=), Greater Than (>), Less Than (<), or Range criteria (..).

SYNTAX

The syntax for the WHERE clause is as follows:

Equals
WHERE ({field_code} = {value})

Greater Than
WHERE ({field_code} > {value})

Less Than
WHERE ({field_code} < {value})

Range
WHERE ({field_code} = {value} .. {value})

{field_code}
Is a valid field identifier for a printable field that exists in the form that the report is scanning.

{value}
Can either be an absolute value or a field code.

If a value is specified, please be sure to conform to valid field values. For example, dates must be entered as MM/DD/YY, or the report will complain as it prints.

If a field code is specified, the field must be of the same type as the comparison field. For example, dates can only match dates.

For TEXT fields, the test for matching is only as many characters as you specify in the {value} part.

There MUST be spaces in front of and after the Equals, Greater Than and Less Than signs, as well as the Range specifier. If you don't include the spaces, results are indeterminate.

EQUALS

WHERE ({field_code} = {value})
Example: WHERE (@de.fi = 1046)

The EQUALS statement causes the loop body to be printed once for every record whose field matches the {value} entered.

GREATER THAN

WHERE ({field_code} > {value})
Example: WHERE (@de.ow > 99.99)

The GREATER THAN statement causes the loop body to be printed once for every record whose field is greater than the {value} entered.

LESS THAN

WHERE ({field_code} < {value})
Example: WHERE (@de.li > 11/20/2005)

The LESS THAN statement causes the loop body to be printed once for every record whose field is less than the {value} entered.

RANGE

WHERE ({field_code} = {value} .. {value} )
Example: WHERE (@de.na = AA .. AZ)

The RANGE statement causes the loop body to be printed once for every record whose field is greater than or equal to the first {value} entered and less than or equal to the second {value} entered.

Example 1:

@cl.de WHERE (@de.st = ACT .. BAN)
@de.na @de.fi
@cl.de

The above snippet loops through the current Client's Debtors and lists only those with a Debtor Status between ACT and BAN.

Example 2:

You can have more than one conditional if you need to limit the records in more than one way.

@cl.de WHERE (@de.st = ACT) WHERE (@de.ow > 1000)
@de.na @de.fi
@cl.de

The above snippet loops through the current Client's Debtors and lists only those with a Debtor Status ACT and an Owing greater than $1000.00.

The conditions are additive. In other words, these conditions work together to print all debtors whose Status is ACT and whose Owing is greater than $1000.00.

For more details about using the WHERE clause in reports please see the Help topic How To Filter Data With The Where Clause.

Totals In Reports

The report writer calculates totals when printing a list. To print the total, insert the code for a numeric field that you want totaled at the end of the loop statement as shown in the next examples.

Example 1:

@cl.de WHERE (@de.st = ACT)
@de.na @de.fi @de.ow
@cl.de @de.ow <--- This line totals the Debtor Owing for all records listed.

The above snippet prints all active accounts for the currently selected client and prints the total for all printed debtors at the bottom of the list.

Example 2:

@cl.de WHERE (@de.st = ACT) <--- Scans through accounts.
@de.na @de.fi @de.ow <--- Prints account details.
@de.tr <--- Scans through debtor's transaction history.
@tr.pda @tr.tu @tr.di @tr.ca <--- Prints transaction details.
@de.tr @tr.tu @tr.di @tr.ca <--- Totals payments and commission.
@cl.de @tr.tu @tr.di @tr.ca <--- This line totals all transactions.

The above snippet prints all active accounts for the currently selected client, lists all transactions for each active account and prints the total for all transactions printed.

Browse the existing report and letter definitions that ship with Collect! to see examples of these processes.

Controlling Print Attributes

Printer Control Codes set bold, italics and anything else your printer can handle. The system comes with a very basic set that you can modify, if necessary, to suit your printer. To review the available settings, select Print from the top menu bar and then select Settings, Printer Control Codes.

For more information, please refer to help topic How To Control Print Attributes.

Printing Date And Time

There are several special codes for printing the current date and time and for formatting date and time fields.

@d
Prints the current date as MM/DD/YY

@e
Prints the current date as Month DD, YYYY

@t
Prints the current time as hh:mm:ss.

For more information, please refer to help topic How To Use Date and Time Codes.

Page And Record Numbers

There are several codes for printing page and record numbers.

@n
Prints the number of the current record in a list.

@p
Prints the current page number.

For more information, please refer to help topic How To Number Pages and Records.

Page Formatting

There are several codes for controlling page formatting.

@f
Ejects the form without printing the Footer

@fo
Prints the Footer at this location and ejects the page.

@lnn
Starts printing at line {nn} where {nn} is a valid line number.

@fnn
Ends printing at line {nn} where {nn} is a valid line number and ejects the page.

For more information, please refer to help topics, How To Number Pages and Records and How To Control Where Printing Begins.

Report Headers And Footers

Information may be defined in a Header or Footer to print on each page of a report or letter.

REPORT HEADER

A Header may be defined which prints at the top of every page of a report. Formatting and special codes are the same as defined for the report body. The header will start printing after the number of lines you enter for the top margin of the report.

REPORT FOOTER

A Footer may be defined which will print at the bottom of every page of a report or letter. Formatting and special codes are the same as defined for the report body.

The footer is printed above the number of lines you enter for the bottom margin of the report. For example, if your enter a bottom margin of 3 lines and your footer is 5 lines long, and your page height is 60 lines, then the footer will start printing on line 60 minus 3 minus 5, that is, on line 52.

Report Options

Several default options may be set for the report through the Report Options form. Select the OPTIONS button on the Report Definition form to access this screen.

Report Options Screen

The options you select are loaded before printing and you are able to change them, if needed, before printing your report.

DESTINATION

The output device that you are using for the report may be preset in the Report Options form.

Printer
Send the report directly to the printer. The printer you use depends on the printers you have installed.

Screen
Preview your report on the screen.

File
Print your report to a file that you can edit later using a word processor, or import into a database.

Spreadsheet
Print your report to a CSV file that you can edit later or import into a database.

Browser
Print your report to an HTML file.

Other
Use additional report writer functions for special printing requirements, redirecting your output to a template for instance.

Email
Print this letter directly to the account's email address.

For additional information, press F1 when you are viewing the Report Options screen.

SETTING A PAGE

There are several settings in the Report Options form that control page attributes.

Page Height
Specifies the number of lines to be printed on a page. Typically, this is 60 lines for Laser printers and 66 lines for Dot Matrix.

Page Width
Specifies the number of columns to be printed on a page.

Top Margin
Specifies the number of lines to be left blank at the top of each page.

Left Margin
Specifies the number of columns to be left blank as a left margin. Typically, this would be around 5 for a half-inch, or 10 for an inch margin.

Printer Initialization Codes
Specifies printer control codes to be executed before the report or letter is printed. This includes page orientation and custom fonts. Also, you can ensure that certain default settings are being used for this report.

Printer Termination Codes
Specifies printer control codes to be executed after the report or letter has been printed. Codes entered in the Printer Initialization Codes field must be "turned off" in the Printer Termination Codes fields. Otherwise, the report's formatting may carry into the next letter or report that you print.

For additional information about other settings, press F1 when you are viewing the Report Options screen.

Printer Control Codes

You may wish to print some text in bold, italics, underlined or other styles your printer supports. Printer control codes are specific to each type of printer. Collect! ships with a set of codes which are generic to most common printer types. To view this list, select Print from the top menu bar and then select Settings, Printer Control Codes.

Printer Control Codes

This list of codes may be modified. There are several "unused" codes in each section and you can modify some of the existing codes if you are sure you are not using them in any of your reports.

To enter new printer control codes, you may need to refer to your printer manual for the list of commands your printer supports. These commands are hexadecimal codes for switching the print attribute ON and OFF. Normally, this isn't necessary for printing via Device Context to Laser and Deskjet printers.

You will not see the effects of these attributes if you print to Screen or to File. They are only visible when you actually print to a Printer or Print Preview.

Code
This is an identifier that you insert into your report to use this attribute. When you enter a command, you must start it with the @ symbol. For instance, if you want to print in Bold, you would put an @! at the beginning of the text you want to appear in bold and again at the end, to switch it off.

Name
This column describes the attribute that the Code provides.

On
This is a command string that is sent internally to the printer to execute the attribute. The values in this field are technically correct for the type of printer. If you need to modify any of the values, please refer to qualified technical assistance.

Off
This is a command string that is sent internally to the printer to switch off the attribute. The values in this field are technically correct for the type of printer. If you need to modify any of the values, please refer to qualified technical assistance.

Printer
This is the type of printer that the section of codes is used with. Each common type of printer has its own set of codes.

For additional information, press F1 when you are viewing the list of Printer Control Codes.

Printable Information

You can print the information that you see in any of the fields in the various forms in this program. Each field has a unique identifier. To print the field's information, you insert the appropriate field code into your report.

Printable Field List

All printable fields are listed in the Printable Field list. The fields are named using a short code of 2 or more characters for the form's name, followed by a dot '.' and 2 or more characters for the field's name. For example, Client Name is cl.na for the Client form, Name field. Debtor File Number is de.fi for the Debtor form, File field.

To view this list, select Print from the top menu bar and then select Settings, Printable Information.

To use any of these printable field codes in a report, use the @ symbol followed by the code for the appropriate form and field.

For example:

Client Name is printed by using @cl.na. When the report is printed, @cl.na will be replaced with the name of the current client. Some formatting rules can be applied as described above.

ID
This is the code for the field. Use this in your report with the @ symbol.

Window
This is the name of the form in which the field is found.

Name
This is the name of the field in the form.

Window Index
This is an internal indexing system for forms and fields.

Field Index
This is an internal indexing system for forms and fields.

Rebuild
The REBUILD button is used to refresh the list of field codes. Rebuild your Printable Field List if your reports seem to be omitting field data or printing the wrong fields.

Printing Database Information

Each item that can be printed from your database has a code listed in the Printable Field list described above.

To print a database field, just type the identifier for the field into the report. For example, to print the Client Name, type @cl.na in the body of your report. Fields are printed exactly as shown on the screen unless you use formatting rules explained here.

One powerful feature of the reporting system is that you can print information from different forms in one report. For instance, you may want to print a list of all debtors and print the Client Name for each debtor listed in the report. Since each debtor that you print could conceivably be owned by a different client, we need a way to tell the system how to fetch the appropriate Client information for each debtor listed. If we use @cl.na, the system will grab the name of the current client, not necessarily the client belonging to the given debtor.

To tell the report system to fetch the Debtor's Client record, we need to be more specific. So we use the code @de.cl. This code refers to the Client field in the Debtor form. The report system knows that the Client and Debtor records are related. So, when we use this code, the database engine returns the appropriate Client information to the report system and prints the correct client's Name.

If you build a report and find that the wrong information is being picked up, keep in mind this idea about the relationship between records. You may not have told the report system how to fetch records related to the currently displayed record. Examine the forms that contain the information you want to print so that you can see what information is contained in the record that your report Starts On and what information is in related records.

To review the list of available codes, please select Print from the top menu bar and then select Settings, Printable Information.

A Quick Print to Spreadsheet creates a very useful list of all the Printable Information codes that you can refer to as you define your reports.

Report Styles

We will step through building a few simple reports here to demonstrate the basic principles outlined so far.

STATUS REPORTS

You may want to generate Client Status Reports showing margins covered by certain dates. These examples demonstrate using conditionals to produce a Daily Listings Status Report and using the Find function to produce an Ad Hoc Summary Report.

Daily Listings Status Report

This report will loop through a selected client's debtors listed for a selected date. It will print the debtor financial information for each debtor listed.

Creating a New Report

1. Select Print from the top menu bar and then select Customize Printing, Edit Report Templates.

2. Press CTRL+End to move to the end of the list.

3. Press F3 to create a new report.

4. Type "Client Daily Listing Status" and press ENTER.

5. Select Day for the 'Start On' field and press ENTER.

Building the Report Body

Create the following report body:

Daily Listing Status Report
Client:@nbsp;@nbsp;@nbsp;@nbsp;@nbsp;@nbsp;@cl.na
Report Date:@nbsp;@e
List Date:@nbsp;@nbsp;@nbsp;@da.da
---------------------------------------------------------------------
ACCOUNT@nbsp;@nbsp;@nbsp;DEBTOR@nbsp;@nbsp;@nbsp; LISTED@nbsp;@nbsp;@nbsp;PAID@nbsp;@nbsp;@nbsp;OWING
@cl.de WHERE (@de.li = @da.da)
@de.ac@nbsp;@nbsp;@nbsp;@nbsp;@de.na
@de.pr@nbsp;@nbsp;@nbsp;@de.pa@nbsp;@nbsp;@nbsp;@de.ow
@cl.de @de.pr @de.pa @de.ow
----------------------------------------------------------------------
Note this report does not include interest.
@f

Press F8 to close the report body and F8 again to close and save the report.

Printing the Report

1. Select Browse from the top menu bar and then select Clients. Select the client that you want the report to use and press ENTER.

2. Select Browse again and then select Daysheets. Select the Day you want the report to use and press ENTER.

3. Select Print from the top menu bar and then select Reports and Letters.Press CTRL+End to move to the end of the list. Select the report we created and press ENTER.

4. Select your printing options and PRINT the report.

Ad Hoc Summary Reports

The report shown above loops through debtors listed on a single day. The Ad Hoc Summary report that we will build next will loop through a selected client's debtors listed over a range of dates. It will print the debtor financial information for each debtor listed.

Creating a New Report

1. Select Print from the top menu bar and then select Customize Printing, Edit Report Templates.

2. Press CTRL+End to move to the end of the list.

3. Press F3 to create a new report.

4. Type "Client Listing Status" and press ENTER.

5. Select Client for the 'Start On' field and press ENTER.

Building the Report Body

Create the following report body:

Summary Status Report
Client:@nbsp;@nbsp;@nbsp;@nbsp;@nbsp;@nbsp;@cl.na
Report Date:@nbsp;@e
This report is for a range of dates.
---------------------------------------------------------------------
ACCOUNT@nbsp;@nbsp;@nbsp;DEBTOR@nbsp;@nbsp;@nbsp; LISTED@nbsp;@nbsp;@nbsp;PAID@nbsp;@nbsp;@nbsp;OWING
@cl.de
@de.ac@nbsp;@nbsp;@nbsp;@nbsp;@de.na
@de.pr@nbsp;@nbsp;@nbsp;@de.pa@nbsp;@nbsp;@nbsp;@de.ow
@cl.de @de.pr @de.pa @de.ow
----------------------------------------------------------------------
Note this report does not include interest.
@f

Press F8 to close the report body and F8 again to close and save the report.

Printing the Report

1. Select Browse from the top menu bar and then select Clients. Select the client that you want the report to use and press ENTER.

2. Select Browse again and then select Daysheets. Press F6 and enter a range of dates for the report and press SEARCH. Say YES to tag the Daysheets.

3. Select Print from the top menu bar and then select Reports and Letters.Press CTRL+End to move to the end of the list. Select the report we created and press ENTER.

4. Select your printing options and PRINT the report.

5. Select Edit from the top menu bar and the select Clear Tags. Select Edit again and select Clear Search Criteria.

This is just one way of selecting a date range for a report. Please see the Help topic How To Filter Data With The Where Clause.

CONDITIONAL DEBTOR REPORTS

You can build many flexible and useful reports using the report system. We will briefly demonstrate some of the available commands by building a report to list debtors with a specific contact scheduled. The report uses loops, the conditional WHERE clause and the MAX statement to control report output.

Introduction

Let's assume that you run a letter series on a set of debtors. When you first receive the list, you schedule a multi letter contact plan which contains only Letter events, spaced at intervals. No Review is scheduled and eventually the letter series has been completed. You can routinely run the Orphaned Account Search to show you any active accounts with no 'In Progress' contacts. So we will run this utility to schedule a Review contact. The Description for the contact reads "Forgotten." We want to print a report of these debtors on a client by client basis so that we can send the list to our clients to gain approval for moving these accounts into Phase 2 collections.

Building the Report

1. Select Print from the top menu bar and then select Customize Printing, Edit Report Templates.

2. Press CTRL+End to move to the end of the list.

The Report Definition contains the following:

Report Name: Phase 2 Debtor Report
Start On: Debtor
Report Body: @de
@de.con max = 1 WHERE (@co.de = Forgotten) WHERE (@co.do = No) // All on one line.
@de.ac @de.na @de.ow
@de.con
@de

Press F8 to close the report body and F8 again to close and save the report.

Printing the Report

1. Press ALT+B to open the Browse menu.

2. Press C to select Clients.

3. Select a client from the list and press ENTER to open the Client form.

4. Press F5 to enter the Debtor list within the Client.

5. Select Print from the top menu bar and then select Reports and Letters. Press CTRL+End to move to the end of the list. Select the report we created and press ENTER.

6. Select your printing options and PRINT the report.

Analysis

Now let's analyze what we have done. I'll go through each significant statement in turn.

Start On Debtor The report Start On field specifies that the report is to be listed only when we are on the Debtor form. We could change this with an asterisk [*] and then the report would be available from anywhere.

@de
The report body contains the @de loop start and end statements at the beginning and end of the file. The @de code specifically refers to the Debtor form. This looping command tells the report system to print the Debtor list, from the first debtor to the last. The path (the active debtor list, closed debtor list, client's debtors, or day's debtors) that we are listing by depends on the current state of the system. If we are browsing the Debtor list within a client, then we will report on all debtors belonging to this client. If we are browsing active debtors, then we will report on all active debtors. Note that the @de is also used at the end of the file. If you don't include a terminating loop marker, then the report will give you an error.

@de.con max = 1 WHERE (@co.de = Debtor was neglected) WHERE (@co.do = No) // All on one line.
This is another looping construct with some conditionals attached.

In essence, this is the start of an @de.con loop which lists all contacts belonging to a debtor.

If you check the Printable Information list in the Print Menu, Settings option, you will see that the Debtor form has field codes that actually refer to several hidden lists, these being the Debtor's Transactions, Debtor's Contacts, Debtor's Cosigners and Debtor's Attachments. Notice that the Debtor's Notes are also shown as an available list on the Debtor form.

The @de.con command causes the report system to loop through all contacts belonging to the debtor. Note again the use of the end loop construct to mark the end of the section that will be printed once for every contact record found.

max = 1
This statement tells the report system to scan through at most one contact before going to the next debtor in the list. This is described in How To Use Min and Max in Loops in the online help.

WHERE =
Next, the WHERE clause tells the system to print only when the debtor's Contact Description matches the text 'Debtor was neglected' and the second WHERE clause limits the list to only those debtors whose contacts were not marked as 'Done'.

@de.ac @de.na @de.ow
These commands tell the system to print the debtor's Client Account Number, Debtor Name and Debtor Owing amount.

We have used a bit of a trick here. We know that the desired Debtor record is currently available because we got to the contacts through the Debtor record. Therefore, we can print any of the debtor fields (that is, fields without the F2 list selection) from the Debtor or Debtor Detail forms, knowing that we will get the current debtor information. Even though we have moved to the Contact record in our selection process, we use the contact selection process to determine if we are to print the debtor information at all, and then print all the debtor information we want.

In summary, this report will print the debtor details if and only if the debtor has a contact whose description text matches and which is not done. This report can be run from any Debtor list you are in.

You could also use this report in conjunction with the F6 Find key to produce a more selective report, for example, only those debtors with an account balance greater than $1.00. You may find it interesting that the WHERE clause automatically utilizes the system's internal F6 Find functions. Several sample reports are shipped in the Collect! Demonstration database. You can examine these reports to get familiar with building reports.

An Important Suggestion

Once you have created your letters and reports in the system, we strongly recommend that you take steps to protect your work. The best way to do this is to establish a regular routine of backing up your database. Please refer to Backing Up CV11.

See Also

- User's Guide - Printing Reports - Part 1
- User's Guide - Printing Reports - Part 2
- Report Sample