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 1)
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 2) Use -1 for All
Salesperson ?Salesperson_EmployeeID Integer -1 3) Use -1 for All
Company ?Account_AccountID Integer -1 4) 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 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

1) , 4)
?Account_AccountID -1) or (?Account_AccountID TransHeader.AccountID
2)
?Division_DivisionID -1) or (?Division_DivisionID TransHeader.DivisionID
3)
?Salesperson_EmployeeID -1) or (?Salesperson_EmployeeID TransHeader.Salesperson1ID