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)
- Payment Terms (PaymentTerms)
- Contact Information (AccountContact)
- Order Salesperson (Employee)
- Order Division (EmployeeGroup)
Tables Accessed
~ Purpose | ~ Table | ~ Linkage Information | ~ Filter Information | ||||
Order Level | TransHeader | TransHeader.StatusID 3 | |||||
Customer Info | Account | Left Join Account.ID TransHeader.AccountID | |||||
Payment Terms | PaymentTerms | Left Join PaymentTerms.ID Account.PaymentTermsID | |||||
Contact Info | AccountContact | Left Join AccountContact.ID TransHeader.ContactID | |||||
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 | 1) | Use -1 for All | ||||||
Salesperson | ?Salesperson_EmployeeID | Integer | -1 | 2) | Use -1 for All | ||||||
Report Title | ?ReportTitle | String | used in header |
Display and Group Fields
The filters selected must be displayed at the top of the report.
Group Header #1
||~ Category ||~ Item ||~ Field ||~ Notes ||
Group Header #1 | Division Name | Division: Division.DivisionName | ||||||
Detail | Company | Account.CompanyName | ||||||
Acct. Contact | AccountContact.FirstName + “ ” + AccountContact.LastName | |||||||
Credit Balance | Account.CreditBalance | |||||||
Payment Terms | PaymentTerms.TermsName | |||||||
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) | |||||||
Drill Down | Order Number | TransHeader.OrderNumber | ||||||
Sale Date | TransHeader.SaleDate | display in short date format (no time) | ||||||
Description | TransHeader.Description | |||||||
Total Price | TransHeader.TotalPrice | |||||||
Balance Due | TransHeader.BalanceDue | |||||||
Salesperson | Employee.FirstName + “ ” + Employee.LastName | |||||||
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 ) | |||||||
Number of Current | Count( TransHeader.ID ) for current orders | |||||||
Number of 0-30 | Count( TransHeader.ID ) | |||||||
Number of 31-60 | Count( TransHeader.ID ) | |||||||
Number of 61-90 | Count( TransHeader.ID ) | |||||||
Number of 91+ | Count( TransHeader.ID ) | |||||||
Number of Balance | Count( TransHeader.ID ) |
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