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
((?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
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 <= 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)

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