How To Build Reports Or Letters

This topic covers the basic steps necessary to begin to create your own reports or letters in Collect!.

Requirements

Setup Company Details
Review help on Report Definition and Report Body
Examine sample reports and letters for guidelines
Have a definite plan for your report layout and the data you want to pull from database fields
Study help topics related to your specific needs
Know what form your report or letter will "start on"

Creating Reports And Letters In Collect!

To get the most out of Collect!'s report writer, it is necessary to be aware of the kinds of information you can include in your design. You will use several of these elements to create effective, useful reports or letters. Basic elements are easy to use to create standard reports or letters. Review the samples included in the pre-defined reports.

Useful Note You may find that one of Collect!'s pre-designed reports or letters provides the information that you need. If so, you can use this report or change it slightly to suit your requirements.

Creating reports and letters begins with an understanding of the basic codes used in the report writer and knowledge of the steps taken to pull information from your database records.

Once you are familiar with the basics, you can extend your use of the report writer to build sophisticated reports, designed in your own manner and capable of displaying your data as you need it, including calculations, totals, and information from various records and forms. The possibilities are quite limitless! For this reason, it is necessary to learn the basics and proceed from there.

Start With Pre-Defined Reports Or Letters

Return to the pre-defined reports and samples provided with Collect! whenever you start to design a new report or letter.

Pull down the Print menu and choose Customize Printing, Edit Report Templates. This will display the Report Definition list with all the reports and letters that are within Collect.

Useful Note Take time to examine several of these reports and letters to get an idea of the way reports are designed.

Use the up and down arrows on your keyboard to navigate through this list.

Copying A Report Or Letter

When you find a pre-defined report or letter that you would like to use, make a copy of this report. To do this, make sure the correct report is highlighted.

Pull down the Edit menu and choose Copy, or use the Ctrl-C key combination.
Next, Pull down the Edit menu and choose Paste, or use Ctrl-V, to paste an identical copy of the report into the Report Definition list.
Click on one of these copies to open the Report Definition form for this report.
Click in the Name field and rename your copy of this report.

Now you can experiment freely with changes. If you are not satisfied with the results, the original report can be used to restore the design.

Report Definition Form

Click Here to View this Form.

Click Here to Close this Form.

The Report Definition list contains all reports that are present in your system. You can build your own reports to print any information that you have stored in Collect!. Many sample reports are available that you may modify to suit your requirements.

When you select a report from the Report Definition list, the Report Definition form will display your chosen report's design and codes. Select a report to open the Report Definition form.

Report Definition

The Report Definition form is used to create and edit reports. This is the user interface for Collect!'s report writer. The Report Definition form has several sections outlined below.

Name

This is the exact name of the report. Choose a name that clearly identifies your report. This name will help you when you need to choose a report in one of Collect!'s other forms or pick lists, such as when scheduling a Letter Contact.

If you change the Name of the report, be sure to update any references to it in your contact plans or letter contacts!

Start On

This is the form which must be currently displayed when you start printing the report. Select the Start On arrow to display a list of choices called "Report can be printed from." Your choice is a very important part of your report design. It determines where in Collect! your report will start to pull its data. For instance, if your report starts on "Debtor," then you have to be on a Debtor or viewing the list of Debtors to print the report.

Useful Note If you are NOT on the right form, you will not be able to access your Report when you try to view Reports and Letters.

Select Print from the top menu bar and then select Reports and Letters. You will only see reports that can be printed from the form you are currently viewing. For instance, if you are on the Debtor form and you select this menu option, you will see a Print Report list of reports. When those reports were defined, the Start On field was set to Debtor. Reports that "Start On" Client will not be in the Print Report list at this time.

However, if you are on the Client form and you pull down the Print menu and choose Reports and Letters, the list of reports that is displayed will not have the same reports in it as the list above. Instead, it will show the reports that "Start On" Client and not ones that "Start On" Debtor.

Some reports are defined to "Start On" ANYWHERE. They will show up in the Print Report list no matter what form you are on.

Useful Note The Report Definition list displays the name of the "Start On" form for each listed report.

Some reports are defined to "Start On" INTERNAL. This is a special case for a few built-in reports that may be printed from the Print menu, Daily, Monthly, Analysis and Trust Reports.

Useful Note Any of the internal reports may be replaced with your own customized version. To make Collect! run your own custom reports via the Print menu, Daily, Monthly, Analysis and Trust reports, change the Start On to something other than INTERNAL, depending on the particulars of your report, and place your own report codes in the Report Body of the report.

Last Run Date

When a Report is run, the date will be written to this field to track the last time it was run.

Last Run Time

When a Report is run, the time will be written to this field to track the last time it was run.

Header

Some reports may have column headings and other text that you want to print at the top of each page. These may be defined in the Header section.

Please refer to How To Use Headers And Footers for more information.

Footer

Some reports may have text that you want to appear at the bottom of each page of the report. This may be defined in the Footer section.

Please refer to How To Use Headers And Footers for more information.

Report Body

This is the section where you actually create or edit your report template. It may contain text, printable information codes for database fields and symbols for formatting fonts and margins. It may also contain commands for printing your company logo, printing boxes and lines and other graphics.

If you are modifying or viewing an existing report, this "window" will show the actual code in this report. A new report will show nothing in the Report Body until you click into it. Then the text editor will present you with a blank screen where you can begin to design your report.

Function Keys

F1 Help............. Opens up this Help topic
F2 Pick............. Pick from database fields and other items
F5 Print............ Print all your notes
F7 Delete........... Delete the current line
F8 Save and Exit.... Save the report and exit

Text Editor

Use this window to create or edit reports. Press F2 to see a list of common types of codes you can use in your report. Press F8 to save any changes you make in this window. Please refer to How to Build Reports or Letters for complete information on creating reports and letters.

Word Wrapping in the Report Writer

When you type in the Collect! report body the font is a common courier 10cpi character set. There are many benefits to this, lists are easier to line up for example. When you print the report, it will print in the FONT that you have chosen, thus the difference in looks. That is why the layout of the report body is very basic and manual. You must control the line lengths, with the particular font you are using. Therefore if you change the font, move the margins or edit paragraphs you will have to take care of the line length.

If you are using a non-fixed length font in your template, that's fine but it means that you will be able to fit more than the standard 80 characters on a line. As you are typing, when you come within 2 characters of the right window edge, Collect! will word wrap the line for you automatically. This may not be what you desire.

Useful Note You can look in the status bar at the bottom of the report body to see the exact line (row) and column (character) that the cursor is on at the moment. This may help guide you when correcting your lines.

Correct Line Length in Your Report

Type your text into the editor.
Go to the end of any line that prints too short and press the DELETE key. This will bring up the line from below up to the line you are on.
You should then go to the word you think should be at the end of that line and press ENTER to have the text to the right of your cursor drop to the line below.
Repeat this process till all lines are far enough to the right.

Useful Note You see there is no right margin. It's just word wrapping at 80 characters when you type. This is because if you used a fixed length font, that's where your page would end.

As mentioned above, you can keep track of how many characters to the right you are, by paying attention to the bottom left corner of the report writer. This is your cursor's position within the report body. Non-fixed length fonts can be up to 132 characters to the line.

Options

Select this button to display the Report Options form where you may enter additional settings for the report or ensure that Collect!'s defaults are restored.

This is where you set a font, page orientation, printing output destination, Letter Service letter number, and other options such as security access levels and default margins. Also, the Report Options are used for special printing requirements like printing to a third party word processor letter or mailing label template.

Press F1 when you have opened the Report Options form to see details of the possible settings.

Print

This button allows you to test print your report right from the Report Definition that you are designing or viewing. You may be prompted to choose a record or set of records to include in the report when you print it.

Select this button to print a test report sample, ONLY. Do not attempt to print an actual report from this form!

Useful Note Be aware that some reports cannot be adequately tested from the Report Definition form and MUST printed from the form that the report Start's On.

To do this, finish viewing or designing your report.

Exit to the Main Menu and choose the form that your report is meant to Start On. Select Print from the top menu bar and then select Reports and Letters from the drop-down choices.

Your report should display in the Report Definition list and you can select it and print it.

Help

Press this button for help on the Report Definition form and links to many topics related to reports and printing.

Cancel

Select this button to ignore any changes you may have made and return to the previous screen

OK

Select this button to save any changes you may have made and return to the previous screen.

Delete

This button is visible only on the list of all Report Definitions. Select this to delete the highlighted item from the database.

Edit

This button is visible only on the list of all Report Definitions. Select this to open and modify the highlighted item.

New

Select this to open a new blank Report Definition form.

Select this button to scroll down one page in the list of Report Definitions.

Select this button to scroll up one page in the list of Report Definitions.

Moving Reports In The Report Definition List

As you develop your own reports, you may want to reorder the reports as they appear in the list so that the reports that you use most often are at the top when the list opens. To do this, you can copy and paste reports in the list.

Select Print from the top menu bar and then select Edit Reports from the drop-down choices.
When the Report Definition list appears, scroll to the report you wish to move. Make sure the report is highlighted in black.
Select the Edit menu from the top menu bar and then select Copy from the drop-down choices. Collect! will prompt you that the report has been copied to the Clipboard. Select OK.
Scroll to the place where you want the report to be moved to. Collect! will paste the report beneath the report that you select. So go up one more.
Select the Edit menu from the top menu bar and then select Paste from the drop-down choices. Collect! will paste the report into the new location.
Once the report has been pasted in its new location, be sure to delete the original from the old location. You want to avoid having more than one report with the same name.

Delete Reports From The List

You can press DELETE when you have a report selected. This will delete it from the list of reports.

If you need to delete more than one report, here are the steps to select multiple reports for deletion.

Select Print from the top menu bar and then select Customize Printing, Edit Report Templates. When you are looking at the list of reports, use your Spacebar to highlight the ones you want to delete. Be very careful that you only select the ones you want!
When you are finished selecting, select Edit from the top menu bar and then select View Tags. Confirm that these are the ones you want to delete.
Press the DELETE key on your keyboard and say YES to ALL.

Summary

Please refer to How To topics and the Help Index for additional information on Report Design and Printing procedures.

Collect! ships with many sample reports that you can use or modify. Pull down the Print menu and choose Customize Printing, Edit Report Templates to view the Report Definition list of all reports in Collect!.

Also, additional reports are available in the Collect! report folder. See Report Sample to view these reports.

Reports may also be downloaded from our web site at www.collect.org

Comtech Systems Inc. is available on a contractual basis for designing your custom reports.

Report Options Form

Click Here to View this Form.

Click Here to Close this Form.

The Report Options form stores settings that apply to a particular report. This allows you to specify different settings for individual reports depending on your requirements. You can choose a destination, a filename, user rights, set the margins and page height, give your report a number and switch on NCOA check when using letter service.

Report Options

Additional options are available when you select Other from the Destination field pick list. For instance, you can create special reports to print out of MS Word or Word Pad using mail merge.

Useful Note You should use this form to enter any custom settings that you want to apply to your whole report. At the very least, you should ensure that Collect!'s default settings are restored, by entering RESET codes in the Printer Initialization Codes and Printer Termination Codes fields.

Useful Note When importing or exporting reports, settings in the Report Options are imported or exported as well.

Destination

Choose an output device from the pick list. Click the arrow to display the choices. This setting should be filled in, especially if you intend to print this report in your daily batch letters.

Printer - Send the report or letter directly to a printer.

Screen - Preview and edit your report on the screen before printing.

File - Send your report to a file which you can later edit using a word processor, or import into a spreadsheet or database. You can enter a filename in the field labeled Filename or enter a filename when the report is printed.

Spreadsheet - Print quickly and easily to your spreadsheet program.

Browser - Format the report as an HTML page and view it in the browser.

Other - Use with CSV files or Tab delimited reports, or in combination with RTF reports. A filename is entered in the field labeled Filename. A field labeled Redirect To becomes visible with this selection and you can enter a filename or a web site to redirect your report output to.

Email - Send this letter directly to the account's Email address. When you select "Email" as your Destination and additional field becomes visible, a check box labeled "Use SMS." Please refer to Help topic, SMS Email Setup for more information.

Sub-Report

If this report is called via another report with @REPORT or @SUBREPORT, and you do not want the report to be run directly, you can check this box to hide it from the Report list when users click the Print button.

File Name

Enter the File Name here if you intend to send the report to a file. By default, files are placed in the root folder of your Collect! application. You can specify a drive and the name of an existing folder, if you wish to be more specific.

Examples:

To save the file in the Collect folder, enter FILENAME.EXT, for example, myreport.txt.

To save the file on drive F:\ enter F:\FILENAME.EXT, for example, F:\myreport.txt.

To save the file in F:\ALLREPORTS enter F:\ALLREPORTS\FILENAME.EXT, for example, F:\Allreports\myreport.txt. In this case, the folder ALLREPORTS must already exist or you will get an error message.

To use this field with *.rtf or *.csv files, enter the path and name of the file. For example, LETTER1.RTF to open the RTF file or MAILMERGE.CSV to print to a CSV file.

Useful Note When you choose 'File' from the Destination pick list and enter a complete path in the File Name field, the Print Output Options dialog will automatically display this information when you choose to print the report. You will see that 'File' will be marked as the Destination and the path you entered as the File Name with be displayed in the Print Output Options 'Path' field.

File Name With Date/Time Stamp

If you enter simply the extension, .csv or .rtf (without the asterisk!) in the File Name field, Collect! will assign a file name to the report when you print it. Collect adds an operator, date, time and increment stamp. For example, OWN-20080305-115656-1.csv.

File Name Setting for OpID/Date/Time Stamp

By default, files are stored in the Collect\bin\global\masterdb\letters folder. For example, Collect\bin\global\masterdb\letters\OWN-20080305-115656-1.csv.

You can also enter your own location. For example, C:\myfolder\.csv will output your files to C:\myfolder. For example, C:\myfolder\OWN-20080305-115656-1.csv.

File Name With Use SMS

When you have switched ON "Use SMS" with an Email destination, you can enter an SMS Domain Name in the File Name field. Please refer to Help topic, SMS Email Setup for more information.

Browse

Select the BROWSE button to navigate to a folder of your choice for saving this report to FILE. Enter a name for your report and a valid extension, such as CSV or TXT. Collect! will display the path. When the report is printed, it will be saved to the folder you selected.

Useful Note By default, reports are saved to your letters folder so you do not have to select a folder here. This is just a convenience for users who need more flexibility for storing report files.

Letter Number

This is used to identify letter templates being sent to a letter service provider. The number you select in this field will match the setup letter number your provider has created to match that specific letter. Each letter you plan on sending to the mailing house needs to have a unique value. This number is printed in the debtor's information sent to the Letter Service and is used by them to print each letter correctly. Generally, samples of the report and report body codes must be sent to your service provider of choice as part of your setup process with them.

This field support alphanumeric characters.

Useful Note When you print your letter batch to a service bureau, the system will only output to text file those letters that have a letter number.

NCOA Check

This switch is used for Letter Service. When it is switched ON with a check mark, a special code is included in the Letter Service output file. This tells the Letter Service to perform a National Change of Address check on this Debtor.

Useful Note When printing your Letter Service batch, switch ON 'Print to unverified addresses' in Batch Letter Options to force Collect! to print to all debtors in the batch, even those with "bad" addresses, which means the 'Address OK' switch is OFF in the Debtor form.

Please refer to How To Use Letter Service for more details.

Don't Prompt On File Overwrite

Switch this ON with a check mark to stop Collect! from showing you a prompt if you are printing to file and the file already exists. When this switch is ON, Collect! will automatically overwrite the existing file that has the same name as the file you are printing.

Exclude From Text Scanner

Switch this ON with a check mark to exclude this report or letter from the Text Scanner.

The Text Scanner is a utility under the Customize Printing menu that allows you to tag Report Definitions, then search for a string of text. All reports and letters that do not contain the text will be tagged.

Save To Letters Folder

This box applies to scenarios where files would be saved to either the Debtor or Client attachment folders.

Switch this ON with a check mark to force any files created by this report to always save in the Letters folder located at Collect\bin\global\{dbname}\letters.

User Level

This can be used to limit the Users allowed to print certain reports. You pick from the list of User levels and choose one. Anyone with that level or better will be able to print that letter or report.

For example, if you enter a 10 here, only Users at level 10 can print this report. If you enter a 20 here, all Users at levels 10 and 20 are allowed to print the report. Enter a 60 here, and all Users from levels 10 to level 60 are allowed to print the report.

Useful Note What actually happens is that when the User picks the Print command, only those reports that they are allowed to print appear in the list of available reports. Thus, the User is never aware of the reports they don't have access to.

Min 1

The MIN and MAX fields allow you to set up to 3 ranges of Users per report. A User level that falls within any of the selected ranges will have access to the report. Otherwise, they won't be able to run the report.