SUMMARY: Everything there is to know about using the Clients & Profits report writer.

The built-in Clients & Profits report writer lets users create custom job lists, client summaries, accounting reports, and more without rekeying jobs, clients, costs, and other data.

This tech note provides tips and troubleshooting guidance for custom report writing. THE CLIENTS & PROFITS HELPDESK PROVIDES ONLY LIMITED TECHNICAL SUPPORT FOR THE REPORT WRITER. Custom report programming can be very technical -- as well as time-consuming and error-prone. It should be performed only by experienced computer users who are familiar with relational databases.

TIP: Custom reports can be created on a Macintosh then printed on a PC, and vice versa.

All the custom report needs is a DOS-compatible file name, something like REPORT.AHR. The .AHR stands for "ad hoc report," which is what the custom reports are technically called. Copying from a Macintosh is easy: Insert a blank high-density disk, then let the Mac format it as a DOS-compatible disk. Then drag the custom report to it like you normally would. The Windows version of Clients & Profits will recognize this report when it is copied to the PC.

TIP: Information can be printed from two different files using a lookup field.

For example: You want to create a job list report that shows the client name and address, but the client information is not in the JOB file, it is only in the CLIENT file. The JOB file and the CLIENT file can be linked together, by setting a query that makes their two common fields equal to each other. For example, set CLIENT_NUM = JOB_CLIENT_NUM on the custom report. Then turn off the Automatic Connections option from the Query menu.

TIP: A custom report can show totals only, instead of showing record-by-record detail.

For example: You need the time report to show totals by task, not each time entry for each task. First, create a custom report using fields from the COSTS file, such as COST_VENDOR, COST_DATE, COST_HOURS, COST_NET_AMT. Enter a query to select some records, then print the report to check your work. Once the report seems to show the correct information, choose Parameters from the Reports menu then click the Print Totals Only option. When the report is reprinted, only the values in the sub-total and total sections will print. You won't see each cost record.

TIP: How to add subtotals to a custom report:

1 Select a report template that supports sub-totals: List w/1 subtotal, List w/2 subtotals, or List w/3 subtotals.

2 Add all the fields and calculations to your report.

3 From the Fields/Query window, highlight the field where you want the subtotal. In this example, a subtotal will be created for each job number.

4 With that field highlighted, select the option to Sort First from the Sort menu, then select the option to Subtotal from the Sort menu.

TIP: Where a range of numbers represents a text message, the text can be displayed instead of the number.

A calculation can be created to extract the text. For example, where the field COST.COST_KIND is 0= A/P, 1=Time, 2=Expenses, 3=Transfers, 4=Misc, 5=Internal Charges; to display the text corresponding to each number instead of the number, set up a calculation. Enter the name of the calculation as "KIND", set the type to be Character, enter the calculation as pick(COSTS.COST_KIND,'A/P','Time','Expenses','Transfers','Misc','Internal Charges').

Q. Can reports be copied from Clients & Profits X into the custom report writer?

No. If you want a report similar to one in the program, you will need to create the report from scratch in custom reports. However, there are sample custom reports that can be downloaded here.

These reports can be used as-is or customized as needed.

Q. Can a custom report replace the A/R invoice, estimate or check forms printed from Clients & Profits?

The custom report writer is designed to provide lists and summaries, not forms. It most likely won't have the flexibility you'd need to format an estimate, invoice, or check form. You'll need to use the built-in forms printed from Clients & Profits instead.

Q. How can I truncate a field on a report so it only shows a limited number of characters?

Highlight the field, click on the Titles button, and then in the Entry box, type in the column's title followed by a back-slash and the number of characters you want to limit the field to, then an X. For example to truncate the JOBS.JOB_DESC field to 20 characters, type the title as Job Description\20X..

PROBLEM: The error message, "Unable to create new report because file Report templates not found," means a file is missing.

The Report Templates file (which is called TEMPLATE.REP in Windows) contains basic layouts for several different types of custom reports. This file must be in the same folder or directory as the Clients & Profits Player. If it is in a different location, or not installed (or has been removed), a new custom report can't be created. (However, existing reports can be edited and printed, since the Report Template is only needed for new reports.) A copy of the Report Templates file is included on the Clients & Profits CD.

PROBLEM: I created a report, but when I print it some records are missing that should appear on the report.

Check the queries that you have set on the report one-by-one. Is there a date range setup that is excluding some costs, or a range of production status codes that are excluding some jobs? Find the individual records that are missing from the report in your database, and verify that they meet all the queries you have set on the custom report. If you set up connections between some of the files, then turn off the Automatic Connections option under the Query menu. If you are still unable to find the problem, recreate the report adding one field at a time and printing the report after each field and query is added. This will help isolate the problem.

Q. Where can I get help with programming my own custom reports?

Start with the Clients & Profits User Guide. New custom reports are posted to the Clients & Profits web site, which can be downloaded and customized.