Reports

Accounts Receivable report:
Q:  What report is best to use for a list of outstanding Account Receivable?  Can I run this report for the Accounts Receivable on the last day of last month.
A:  The Statement report is best. The report can be run by choosing Accounts from the top AI menu and Statements from the submenu.  Check the “print to screen” box and the “show credit balances” box if you wish to see clients who have both charge and credit balances.  This report can be confined to clients who have balances in one particular register and can include invoice information if the “show report detail” box is checked.  It cannot recapture an Accounts Receivable balance for some past date.  If the accountant needs a report of who owes money and how much at the end of a month or year, the report must be run at that date and time.

Adding a column to my report:
Q:  I need to add another column of information to my report?
A:  Open the Report Generator; select the report that needs the addition. With the report information visible on the screen, note the bottom section of the window contains a table.  Each row on the table defines a column on the printed report. Click the green plus sign found on the right side above this table. This will add a row to the bottom of this table section.  The effect is to add a column to the printed report.  If the addition can be the last column on the report, click in the section labeled FIELD in the row that was just added to the table. Click the drop-down arrow that appears and choose the item that needs to be added to the report.  Next enter a width for the column and a title.  If this item is to be inserted as a first or middle column in the report, the table field, width, title and band selections will have to be moved down one row to allow for the added item to be inserted in the correct position.  If the needed item is not in the drop-down list or if the resulting report is unacceptable, call AI tech support for assistance.

Accounts Receivable by doctor:
Q:  I need to print a report of Account Receivable attributed to each of my doctors, which report do I use?
A:  Choose the report named ACC REC BY with the description, Accounts Receivable by Doctor.  To obtain the figures for each doctor, the number in the filter, (COF_ITDOC=1), must be changed to the correct doctor’s user number.

Accounts Receivable for weeks back:
Q:  I need a figure for our total accounts receivable as of 2 weeks ago, what report do I run?
A:  There is no report that gives an accounts receivable figure for a past date.  Since AR changes with each payment or unpaid invoice, any AR report will give a total as of the current date/time.  You can calculate a figure for 2 weeks prior by obtaining the current AR figure, running the historical summary report for the 2 week period and subtracting the total of the change column.  You will not obtain a list of who owed money but the dollar amount can be calculated in this manner.  It is recommended that the Statement Report be run at the end of each month and saved for this historical information.

Error 3109 in report generator:
Q:  Received an error while running a report. Table master error 3109, unsupported data type in function parameter in a record filter expression. What does this mean?
A:  An incorrect or strange character has been added to the data field description in the filter.  Usually the filter will be automatically cleared with the message.  If it has not, clear the filter and add it again. 

Change column on summary report:
Q:  What is the Change column on the Summary report?
A:  The total in this change column represents the change in the receivables during that period selected for the report.  This would be the amount of charges less the amount of payments.  If it were positive, it would mean you had more charges than payments or an increase in receivables.  If it were negative then you had more payments than charges and had experienced a decrease in Accounts Receivable.  This figure can be used to calculate a AR total for a prior date by subtracting it from the current AR.  It can also be used to verify that all payments and charges have been correctly posted.  Last month’s AR + Change = Current AR.

Building report using client and patient tables:
Q:  How can I get a list of clients associated with a particular doctor?  When I build a report using the client table, there is no filter for doctor.
A:  There is a tie between the client and patient tables so use the patient table as the Master and the client as the link.  You can filter on current doctor in the patient table and use the link table to access client information.

Creating a group mailmerge letter:
Q:  How can I do a group mailing to a selection of clients.
A:  Create the basic letter as a macro using system variables for client name and other fields which vary with each letter. Save the macro to the Label Types folder.  In Custom Reports create a report as a type Labels/Documents and select your macro from the associated label drop down list.  Set up paper size and margins on the paper tab.  Set the label size on the label tab. It should equal the paper size less the margins. Set columns to 1.  Set the Master table as Clients and create a filter using a status1, status2 or status3 = string. In the match box type the status found in the status field of your chosen group of clients, click Add and Ok.  Click RUN to preview or print the letter.

Determining the number of active patients:
Q:  How do I find out how many active patients we have?
A:  Use the Patient report in custom reports.  Add the filter for active clients, (Active=True).  This will appear as the following in the filter window: (PET_ISACTV='T').  Add a column to the report, choose the field Count and change all the column bands to footer 1.  When the report is run, it will give you a count of all your active clients in the count column.

Figures do not match on reports:
Q:  I run two reports using the same master table.  The two reports confirm that my figures are correct when the total sales figures match.  Where do I look for problems when the figures do not match?
A:  Make sure you are using the same filter information in both reports.  Check for a filter for doctor number or register number that might be different.  Check to make sure the date types are identical.  One report may be filtered on invoice date while the other is filtered on register or item date.  The results can be different even though the date range may be the same.

Filter on field not listed:
Q:  I need to filter a report on a field that is not listed in the filter selection.  Can this be done?
A:  Usually you can filter on any field in a table even if it is not listed in the list of filter selections.  The actual table name must be obtained and used in the correct syntax.  The name and syntax can be researched by AIS technical support.  Call for help with this problem.

Hospital list with both, fax and phone numbers:
Q:  How can I get both the main line phone number and the fax phone number on my hospital list?
A:  In the Report Generator find the Hospital report.  Click the green plus sign to add columns to the report.  Click on Link1 tab and select Phone Numbers table.  Click on Link2 tab and again select the Phone Numbers table.  Set the filter in Link1 table for Category = number and enter the link number of the main line from the picklist phone field names.  Set the Filter in the Link2 table the same but use the  link number of the fax from the picklist phone field names. The standard link numbers are 2 for main line and 3 for fax. The filter will appear as follow:
(PHN_CAT=2) for Link1 tab and (PHN_CAT=3) for Link2 tab.
Adjust the column widths or change the orientation to landscape to allow the additional columns to print. Save and Run the report.

Hospital report with phone numbers:
Q:  I need a report that will list the hospital name address and their phone and fax numbers.
A:  Find the report named HOSPITALS and add two columns to the report.  Select the table Phone Numbers for both Link 1 and Link 2.  In Link 1 add a filter for the phone field Main Line. Check picklist number 114 to determine what link number is associated with Main Line and Fax. Use the filter expression (PHN_CAT=2) in both link tables, replacing the number in one with the Main Line link number and the second link table with the one for the fax link number.  Select L:1 and L:2 for the two added columns and use the field name PHONE NUMBER in both columns.  Title the columns Main Phone and Fax Phone and set the Width to 1.00 and the Band to Detail.  When you run the report click on the tab for both link tables to activate the filters.

Caution:  Each of the hospitals must have a main line phone and a fax phone or at least the category name listed in the screen or they will not be listed on this report.

Label printer printing blank pages:
Q:  When I try to print a label the printer spits out lots of blank labels.
A:  This problem is consistently due to incorrect formatting for the label.  Go to the Report Generator and find that report.  Click on the Paper tab.  Note the height and width measurements. Subtract the margins.  Check the resulting figure with the height and width on the label tab.  The measurements set for height and width on the label tab should be equal to or less than that calculated figure.  If they are more the result will be multiple blank pages.  Adjust either the margin or the height and width measurement on the Label tab to correct the problem.  If this is a Dymo printer, which prints in a landscape mode, the calculation should be made as follows:

Dymo Printer     Eltron Printer
Paper Tab       Margins        Label Tab  Paper Tab       Margins        Label Tab
Height    -  (Left + Right)    <= Width  Height    -  (Left + Right)    <= Height
Width     -  (Top + Bottom) <= Height  Width     -  (Top + Bottom) <= Width

Production report has no totals:
Q:  My Production Report no longer has totals for the categories or totals at the bottom.
A:  In the second column on the table grid at the bottom of the report screen are a series of Sum boxes. Make sure there is a checkmark in the SUM box on the row which contains the field name Item Value (rel).  Also check to make sure the master table is indexed by Item/Category.  On the Group tab make sure break 1 is set for the row number that contains the field name Code and Break 2 is set for the row number that contains the field name Category.  The Grand Total box seen on this Groups tab should contain a checkmark also.

Referral count for a date range:
Q:  My referral report is not accurate.  It list a number of referrals and gives a total referral figure but when the list is counted, it does not add up to the total listed.
A:  The number of referrals is the total referrals for the hospital but the list is filtered for a date range.  To get the referrals for the date range without having to count them, add a column to the report, select Count as the field, add a title, Count and set the band to footer 1.  This will count the listed referrals and display the number at the bottom of the group.

Report of cancelled invoices:
Q:  How do I find out how many cancelled invoices have been created for the past month, for whom and by which user?
A:  Use the INVOICEREV report in custom reports.  Add the filter for active clients, (Code=60).  This will appear as the following in the filter window: (COF_ITNUM=60).  Also add a filter for the desired date range, Invoice Date =>selected beginning date AND Invoice Date =<selected ending date. Add a column to the report, choose the field User and add the title User.  When the report is run, it will present a list of all cancelled invoices, listed the client and patient, and the user for that item on the invoice.

Selected client address labels:
Q:  Is it possible to create address labels for a selected group of clients who have been in at least once in the past year?
A:  Choose the report named Labels 3/1 for Avery 5160 labels.  Change the master table to Patients and index on number. (Selecting the Patient table allows access to client information.) 
Create a filter for Total Visits => 1 AND Last Visit Date => mo/day/yr. 
The filter will appear as follows: 
(PET_TVISIT>=1)
AND (PET_LVISIT>CTOD('01-01-2001'))
Click Run to preview or print the labels.

Sorting a report by name, not number:
Q:  How do I get a report to sort by name alphabetically instead of number?
A:  Find the report in the Report Generator, click on the tab M:xxxxx.  Change the index field from number to name.

Wildcards in the report generator:
Q:  Can I use a wildcard in a custom report filter.
A:  You can use the filter relation ($) which will find a specified word or string of characters contained within a field.  The function is essentially the same as using wildcards.  The syntax for the filter is ('STOCK'$UPPER(STK_DESC)).  In an inventory report, this filter will find any item that has the word STOCK in the item description.  $ is the relational operator and (STK_DESC) is the field name.  The word UPPER converts all letters to uppercase so they will match.

The information in these documents are intended solely for use by Animal Intelligence Software, Inc. clients. 
Any other use is strictly prohibited.