This report shows Ledger activity occuring during a specific time frame. The report reflects both new sales as well as adjustments to previous sales. All sales numbers are pulled from the GL view which filters the ledger by OffBalanceSheet 0.

Information Structure

Because the report also shows adjustments to orders, the data must be pulled primarily from the Ledger and not from the TransHeader table.

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

  • The General Ledger (GL view of the Ledger)
    • The order record (TransHeader).
      • The Salesperson for the order (Employee).
    • The Company record (Account).
    • The Division Name for the Orders (EmployeGroup).

Tables Accessed

~ Name(Alias) ~ Table/View ~ Linkage Information ~ Filter Information
GL GL (a View of Ledger) EntryDateTime between ?StartDate and ?EndDate and
GLClassificationType between 4000 and 4999
Order_Info TransHeader Inner Join TransHeader.ID GL.TransactionID
Customer_Info Account Left Join Customer_Info.ID GL.AccountID
Salesperson Employee Left Join Salesperson.ID TransHeaders.Salesperson1ID
Division EmployeeGroup Left Join Division.ID GL.DivisionID
Store Store (Store.ID > 0)

Notes: The table “Store” is used only to reflect the Store.tagline at the bottom of the report and the value of that field is also available from the Command used as a data source for the sub report that is displayed as the Report Header.
Important Display Fields
||~ Item ||~ Field ||~ Notes ||

Order Number TransHeader.OrderNumber
Sales Person Salesperson.FirstName + “ ” + Salesperson.LastName
Company Account.CompanyName
Order Description TransHeader.Description
Sale Date GL.EntryDateTime
Sale Amount Sum(GL.Amount)
Rows Count of EntryDateTime rows in a group “Is Adj” is not needed as a column as it is a mandatory group. Adding this column to reflect the number of rows from GL that are summed in a group may be a useful bit of info!
Current Order Status TransHeader.OrderStatus
Current Order Total TransHeader.TotalPrice
Current Balance Due TransHeader.BalanceDue

Group By
The following groups are inserted in the report design:
||~ Description ||~ Level ||~ Optional ||~ Group ID field ||~ Group Display Field ||~ Notes ||

Division 1 No GL.DivisionID Division.DivisionName
Salesperson 2 Yes TransHeader.Salesperson1ID Salesperson.FirstName + “ ” + Salesperson.LastName
Sale/Adj 3 No @IsAdj “Order Marked Sale” or “Adjustment”
EntryDateTime 4 Yes GL.EntryDateTime “EntryDateTime” + GL.EntryDateTime
Customer 5 Yes Customer_Info.CustomerName Customer_Info.CustomerName
Order# 6 Yes Order_Info.ID

Sort Options
Data in the report is Sorted exclusively by grouping. The first group (Division) is mandatory and the data will therefore first be sorted by Division.ID. The second group (salesperson) is optional dependant upon ?Groupsalesperson. The group condition for group 2 is derived from the formula @Group2condition which returns a value of 0 when ?Groupsalesperson is false. In that case, no sorting occurs within group2. The third group (Sale/Adj) is mandatory and all of the rows within each Division of group 1 will therefore be sorted into two groups. The fourth group (EntryDateTime) is optional but only in the sense that headers and footers for the group are displayed only in the case when ?OptionalSort has a value of “EntryDate”. The group remains active and data in the group is sorted by EntryDateTime even when the value of ?OptionalSort is set to something else. The fifth group (Customer) is also optional but in the same sense as the fourth group. The sixth group (Order) is also optional and differs from groups 4 and 5 in that group headers are not needed for group 6 because the Order number will appear as the left most column display in the group footer.
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 ||

Sales Date Range fx_RangeStart_Date
DateTime Range tells Control to return ?Date as a Range
Date Range ?Date GL.EntryDateTime IN Mimum(?Date) TO Maximum(?Date) Has a Min and Max value
Show Zero Dollar Orders t.b.d. Boolean False Having Sum(GL.Amount) 0 Used as a suppress condition in group footers
Group by Salesperson ?GroupBySalesperson Boolean False Determines if saleserson is 2nd group or not
Sorting ?OptionalSort String “EntryDate”, “OrderNumber”, “Customer”, or “None” Determines behavior of Groups 4, 5 & 6
Salesperson as a List ?Salesperson_SalespersonID Integer -1 1) Use -1 for All
Company as a List ?Company_CustomerID Integer -1 ?Company_CustomerID -1) or (?
?Company_CustomerID GL.AccountID))
Use -1 for All
Export ?Export Boolean Used to format for export

Report Formulas
||~ Formula Name ||~ Syntax ||~ Return value ||~ Purpose ||

@AmtLbl String creates a column header for Amount in Group 3
@BalDue Crystal local currency value display in report column
@CompSumGLamt local currency value Sums the values of @GLAmount for Group 5
@ContactName {Contact_Info.FirstName} + “ ” + {Contact_Info.LastName} Concatenation
@Continued string adds “Continued” to group headers when the group breaks to a new page
@CrystalPath string Holds the Special Field “File Path and Name” for passing as a parameter to header subreport and display at the bottom of the main report.
@CrystalTitle string Holds the Special Field “Report Title” for passing as a parameter to header subreport. The value of the Special Field may be edited in the report File→Summary Info.Title
@DescStr string trims {Order_Info.Description} to display only 35 characters
@Edate Date Used to define the Minimum date from ?Date
@EDSumGLamt local currency value Sums the values of @GLAmount for Group 4
@GLAmount Crystal local currency value Flips the sign of GL.amount and adds local currency symbol
@Group2Condition null or salesperson.ID The return value is dependant on ?GroupSalesperson
@Group2Display {Salesperson.FirstName} + “ ” + {Salesperson.LastName} OR an empty string The return value is dependant on ?GroupSalesperson
@IsAdj Boolean determines if Transheader.SaleDate is IN @Sdate TO @Edate
@Sdate Date Used to define the Maximum date from ?Date
@SumGLAmt local currency value Sums the values of @GLAmount for Group 6
@Tprice local currency value

Summary Fields
The following summary fields are necessary for each group.
||~ Description ||~ Fields ||

Number of New Orders Count(GL.TransHeaderID) where Order Sale Date between Report Date Range
Number of Adjusted Orders Count(GL.TransHeaderID) where Order Sale Date NOT between Report Date Range
Total Amount Sum(GL.Amount)


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

Test Cases

?Salesperson_SalespersonID -1) or (?Salesperson_SalespersonID TransHeader.Salesperson1ID