Sales Report (Standard)

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:

  • 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
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))
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)

Export

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

Test Cases