SUMMARY: Everything there is to know about using the Clients & Profits
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.
Next click on the Design Button
the tool bar. From the Design window you will create the
Decide what fields you want to appear in the subtotal level of your
printed report. Hold down the Option key (for PC's, hold down the CTRL
key) on your keyboard and drag a copy of the fields from the Record Level
to Subtotal Level 1.
TIP: Where a range of numbers represents
a text message, the text can be displayed instead of the
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
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
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
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
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.