Purpose

The report is used for Accounts Receivable management. It summarizes all the balance outstanding for each company.

Information Structure

The report shows, in logical hierarchy, information from the following different sources and the table the information is contained within:

  • Order (TransHeader)
    • Company (TransHeader)
      • A/P Contact Information (AccountContact)
      • Payment Terms (PaymentTerms)

    • Order Salesperson (Employee)
    • Order Division (EmployeeGroup)

Note that, unlike the A/R summary, the Contact Information displayed here is the Company's contact information and not the contact information for the particular order.

Tables Accessed

Purpose
Table
Linkage Information
Filter Information
Order Level
TransHeader

TransHeader.StatusID = 3
Company Info
Account
Left Join Account.ID = TransHeader.AccountID
((?Account_AccountID = -1) or (?Account_AccountID = TransHeader.AccountID))
Payment Terms
PaymentTerms
Left Join PaymentTerms.ID = Account.PaymentTermsID

Contact Info
AccountContact
Left Join AccountContact.ID = Account.AccountingContactID

Salesperson
Employee
Left Join Employee.ID = TransHeader.Salesperson1ID
{?Salesperson_EmployeeID} = -1 or {?Salesperson_EmployeeID} = TransHeader.SalespersonID
Division
EmployeeGroup
Left Join EmployeeGroup.ID = TransHeader.DivisionID
{?Division_DivisionID} = -1 or {?Division_DivisionID} = TransHeader.DivisionID

Parameters and Filter Options

The following options need to be implemented to filter the data and/or provide information to reconfigure the report display.

Description
Parameter Name
Type
Default
Data Filter
Notes
Division
?Division_DivisionID
Integer
-1
((?Division_DivisionID = -1) or (?Division_DivisionID = TransHeader.DivisionID))
Use -1 for All
Salesperson
?Salesperson_EmployeeID
Integer
-1
((?Salesperson_EmployeeID = -1) or (?Salesperson_EmployeeID = TransHeader.Salesperson1ID))
Use -1 for All
Company
?Account_AccountID
Integer
-1
((?Account_AccountID = -1) or (?Account_AccountID = TransHeader.AccountID))
Use -1 for All
Report Title
?ReportTitle
String


used in header

If any of these filters are NOT set to -1, then the filter information should be displayed in the right header of the first page, as in:
    • Salesperson: Jim Smith
    • Division: North Plant
    • Company: Smith Refineries

If the filter is set to -1, do not show the filter line in the header.

Display and Group Fields


Group Header #1
Category
Item
Field
Notes
Group Header #1
Division Name
Division: Division.DivisionName

Group Header #2
Company
Account.CompanyName
A/P Contact
AccountContact.FirstName + " " + AccountContact.LastName

Credit Balance
Account.CreditBalance

Phone
AccountContact.PrimaryNumber

Payment Terms
PaymentTerms.TermsName

Detail
Order Number
TransHeader.OrderNumber

Sale Date
TransHeader.SaleDate
display in short date format (no time)
Age
Date() - cast(TransHeader.SaleDate as Date)
number of days since Sale
Description
TransHeader.Description

Total Price
TransHeader.TotalPrice

Current
Sum(TransHeader.BalanceDue)
where PaymentTerms.GracePeriod >= CurrentAge

0-30
Sum(TransHeader.BalanceDue)
where PaymentTerms.GracePeriod < CurrentAge
and CurrentAge <= 30

31-60
Sum(TransHeader.BalanceDue)
where PaymentTerms.GracePeriod < CurrentAge
and CurrentAge between 31 and 60

61-90
Sum(TransHeader.BalanceDue)
where PaymentTerms.GracePeriod < CurrentAge
and CurrentAge between 61 and 90

91+
Sum(TransHeader.BalanceDue)
where PaymentTerms.GracePeriod < CurrentAge
and CurrentAge >= 91

Balance Due
Sum(TransHeader.BalanceDue)

Group Footer #2
Total Credits
Sum(Credit Balance Column)

Total Current
Sum( Current Column )

Total 0-30
Sum( 0-30 Column )

Total 31-60
Sum( 31-60 Column )

Total 61-90
Sum( 61-90 Column )

Total 91+
Sum( 91+ Column )

Total Balance
Sum( Balance Due Column )

Group Footer #1
Total Credits
Sum(Credit Balance Column)

Total Current
Sum( Current Column )

Total 0-30
Sum( 0-30 Column )

Total 31-60
Sum( 31-60 Column )

Total 61-90
Sum( 61-90 Column )

Total 91+
Sum( 91+ Column )

Total Balance
Sum( Balance Due Column )


Group By

The following groups are inserted in the report design:
Description
Level
Optional
Group ID field
Group Display Field
Notes
Division
1
No
TransHeader.DivisionID
Division.DivisionName

Company
2
No
TransHeader.AccountID
Account.Companyname


Sort Options

The data within a group will always be sorted by these fields:

Description
Sort Text
Level
Optional
Notes
Division
Division.DivisionName
1
No

Company Name
Account.CompanyName
2
No


There are no user sort options for this report.

Export

The report data should export into clean comma-delimited/Excel data.


Test Cases