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.
"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:
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
fx_RangeEnd_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
((?Salesperson_SalespersonID = -1) or (?Salesperson_SalespersonID = TransHeader.Salesperson1ID))
Use -1 for All
Company as a List
?Company_CustomerID
Integer
-1
?Company_CustomerID = -1) or (?
?Company_CustomerID= GL.AccountID))
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)
Export
The report data should export into clean comma-delimited/Excel data.
Sales Report (Standard)
Purpose
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:
Tables Accessed
GLClassificationType between 4000 and 4999
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
Group By
The following groups are inserted in the report design: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.fx_RangeEnd_Date
?Company_CustomerID= GL.AccountID))
Report Formulas
Summary Fields
The following summary fields are necessary for each group.
Export
The report data should export into clean comma-delimited/Excel data.Test Cases