Purpose

This report provides the GL information for an particular Order, Customer, Activity Type, GL Account, or time-period. This information is provided in

Information Structure

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

  • The GL Entries (Ledger).
    • The GL Account (GLAccount)
    • The Order (TransHeader)
    • The Customer (Account)
    • The Activity (Journal)
    • The Division (EmployeeGroup)

Tables Accessed


Purpose
Table
Linkage Information
Filter Information
GL Entries
Ledger

Ledger.OnBalanceSheet = 1 or ?ShowOffBalanceSheet = 1
GL Account
GLAccount
Left Join Ledger.GLAccountID = GLAccount.ID
?GLAccount_GLAccountID = -1 or GLAccount.ID = ?GLAccount_GLAccountID
Order
TransHeader
Left Join Ledger.TransactionID = TransHeader.ID
?Order_OrderID = -1 or TransHeader.ID = ?Order_OrderID
Customer
Account
Left Join Ledger.AccountID = Account.ID
?Customer_CustomerID = -1 or AccountID.ID = ?Customer_CustomerID
Activity
Journal
Left Join Ledger.JournalID = Journal.ID
?ActivityTypeText in ("", "All") or Journal.ActivityTypeText = ?ActivityType
Division
EmployeeGroup
Left Join Ledger.DivisionID = EmployeeGroup.ID
?Division_DivisionID = -1 or ?Division_DivisionID = Ledger.DivisionID

Important Display Fields


The filters selected must be displayed at the top of the report.
  • Division: Division.DivisionName
  • Company: Account.CompanyName
  • Order Number: TransHeader.OrderNumber
  • GL Account: GLAccount.AccountName
  • Showing Off
If the filter is not applied, suppress that section.

Summary Fields

Item
Field
Notes
GL Account
GLAccount.AccountName
*Required Grouping Field
Total
Sum(Ledger.Amount)
not displayed directly
Debit

Total is displayed in the Debit column if Total >= 0
Credit

-(Total) is displayed in the Credit Column if Total =< 0
On B/S
Ledger.OnBalanceSheet
*Required Grouping Field
If OnBalanceSheet = True, Display "" else Display "No"


Detail Fields


The detail rows are suppressed if the ?ShowSummaryOnly parameter is set to true (1). Ideally, even when suppressed, this information would be exposed on drill down.

Item
Field
Notes
Entry Date
Ledger.EntryDateTime

Order
TransHeader.OrderNumber

Company
Account.CompanyName


Activity Type
Journal.ActivityTypeText


GL Account
GLAccount.AccountName

Debit
Ledger.Amount
Suppressed if Ledger.Amount >= 0
Credit
-Ledger.Amount
Supppressed if Ledger.Amount <= 0
On B/S
Ledger.OnBalanceSheet
Suppress if ?ShowOffBalanceSheet = False,
If OnBalanceSheet = True, Display "" else Display "No"
Description
Ledger.Description + "/" + Ledger.Notes + "/" + Journal.Description
Is Taxable
Ledger.IsTaxable
Suppressed when ?ShowAdvancedFields = False
GL Account ID
Ledger.GLAccountID
Suppressed when ?ShowAdvancedFields = False
GL ID
Ledger.ID
Suppressed when ?ShowAdvancedFields = False
Journal ID
Ledger.JournalID
Suppressed when ?ShowAdvancedFields = False
TransHeader ID
Ledger.TransactionID
Suppressed when ?ShowAdvancedFields = False
Account ID
Ledger.AccountID
Suppressed when ?ShowAdvancedFields = False

Group By

The following group by options should be supported:
Description
Level
Optional
Group ID field
Group Display Field
Notes
Division
1
No
Ledger.DivisionID
EmployeeGroup.DivisionName



Sort Options

The result set is always be sorted by these fields:
Description
Sort Text
Level
Optional
Notes
Division
EmployeeGroup.DivisionName
1
No

Company
Account.CompanyName
2
No
Applied when ?Customer_CustomerID = -1
Order Number
Ledger.TransactionID
3
No
Applied when ?Order_OrderID = -1
Entry Date
Ledger.EntryDateTime
4
No
Applied when ?ShowSummaryOnly = false
GL Account Classification
Ledger.GLClassificationType
5
No

GL Account
GLAccount.AccountName
6
No

On Balance Sheet
Ledger.OnBalanceSheet
^7
No


There are no user defined sort options in this report.

Parameter 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
Data Type
Default
Data Filter
Notes
Show Off Balance Sheet Entries
?ShowOffBalanceSheet
Boolean
False
?ShowOffBalanceSheet = True or Ledger.OnBalanceSheet = 1

Filter by Customer
?Customer_CustomerID
Integer
-1
?Customer_CustomerID = -1 or Ledger.AccountID = ?Customer_CustomerID
Use -1 for All
Filter by Order
?Order_OrderID
Integer
-1
?Order_OrderID = -1 or Ledger.TransactionID = ?Order_OrderID
Use -1 for All
Filter by GL Account
?GLAccount_GLAccountID
Integer
-1
?GLAccount_GLAccountID = -1 or Ledger.GLAccountID = ?GLAccount_GLAccountID
Use -1 for All
Filter by Division
?Division_DivisionID
Integer
-1
?Division_DivisionID = -1 or Ledger.DivisionID = ?Division_DivisionID
Use -1 for All
Filter by Journal Activity Type
?ActivityTypeText
string
""
?ActivityTypeText in ("", "All") or Journal.ActivityTypeText = ?ActivityType
List of Values
Show Summary Only
?ShowSummaryOnly
Boolean
False
none

Show Debugging Fields
?ShowAdvancedFields
Boolean
False
none


Summary Fields


The following summary fields are necessary for each group.
Description
Fields
Net Debit
Sum(Ledger.Amount)
Suppressed if Sum(Ledger.Amount) >= 0
Net Credit
-Sum(Ledger.Amount)
Supppressed if Sum(Ledger.Amount) <= 0

Export

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

Backlinks