Orders Placed Between Report (Standard)


Purpose

This report shows a list of orders that were placed, marked built, marked sale, closed, or voided between a specific time.

It also shows adjustments to orders at those stages (or later stages).


Information Structure

Because the report also shows adjustments to orders, the data must be pulled 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 Ledger
    • The order record (TransHeader).
      • The Company record (Account).
      • The Primary Contact record (AccountContact).
      • The Salesperson for the estimate (Employee).

    • The Division Name for the Orders (EmployeGroup).

Tables Accessed

Purpose
Table
Linkage Information
Filter Information
Ledger (Financial Data)
Ledger alias GLData

StatusID = param and StatusDate between param and param
Order_Info
TransHeader
Inner Join TransHeader.ID = GLData.TransHeaderID

Customer_Info
Account
Left Join Account.ID = TransHeader.AccountID

Contact_Info
AccountContact
Left Join AccountContact.ID = TransHeader.ContactID

Salesperson
Employee as Salesperson
Left Join Salesperson.ID = TransHeaders.Salesperson1ID

Division
EmployeeGroup as Division
Left Join Division.ID = GLData.DivisionID

Store
Store
Left Join StoreID = GLData.storeid


Notes:
  • The Inner join of TransHeader table with Ledger typically results in multiple rows from Ledger for each row from Transheadder.

Report Parameters

Parameter Name
Parameter Type
Default Value
Purpose
? ReportStatusID
Number
1
Used in Record Selection formula; 1 = WIP
2= Built
3 = Sale
4 = Closed
9 = Voided
? StartDate
Date
7/1/2011
Used in Record Selection formula
?EndDate
Date
7/14/2011
Used in Record Selection formula
?Groupsalesperson
Boolean
False
True value suppresses Group 2 headers and footers
?DateSorted
Boolean
False

?ReportTitle
String

not currently being used
?ExportToExcel
Boolean
False
True value suppresses report elements not desired in Excel

Report Formulas

Formula Name
Syntax
Return value
Purpose
@BalDue
Crystal
local currency value
display in report column
@GLamount
Crystal
local currency value
determine GLData.Amount dependant on ?ReportStatusID
and GLData.GLAccountID
@Group2Condition
Crystal
Number
determines Group 2 visibility and condition dependant on
?Groupsalesperson
@Group2Display
Crystal
String
determines Group 2 visibility and content dependant on?Groupsalesperson
@IsAdj
Crystal
Boolean
determines if @ReportDate is IN ?StartDate TO ?EndDate
and used for column display and for group 4
@ReportDate
Basic
field value
dependant on ?ReportStatusID
1 = Order_Info.OrderCreatedDate
2 = Order_Info.BuiltDate
3 = Order_Info.SaleDate
4 = Order_Info.ClosedDate
9 = Order_Infor.VoidedDate
@Sorted
Crystal

When ?DateSorted is True formula returns @ReportDate
@StatusDate
Basic
String
returns the appropriate column header to display for @ReportDate
@SumGLamt
Crystal
local currencyvar
displays the Sum of @GLamount for the Order_Info.ID
with the proper currency symbol
@Tprice
Crystal
local currencyvar
display the value of Order_Info.TotalPrice with the proper symbol


Columns Displayed in Group 5 footer:


Item
Field
Notes
Order #
TransHeader.OrderNumber

Company Name
Account.CompanyName

Description
TransHeader.Description

@StatusDate
@ReportDate
Column header displays as Created Date, Built Date, Sale Date, Closed Date, or Voided Date based on the StatusID of the report
Report Amount
@SumGLamt

Adj
@IsAdj

Current Order Status
TransHeader.StatusText

Current Order Total
@Tprice

Current Balance Due
@BalDue


Group By

The following groups are inserted in the report design:
Description
Level
Optional
Group ID field
Group Display Field
Notes
Division
1
No
GLData.DivisionID
Division.DivisionName

Salesperson
2
Yes
@Group2condition
@Group2Display

@ReportDate
3
Yes
@Sorted
@StatusDate

@IsAdj
4
No
@NewOrder
None
Used to facilitate counting new and modified orders
Order
5
No
Order_Info.ID



Sort Options

The data within a group will always be sorted by the grouping condition:
The user's parameter selection for ?DateSort determines whether or not the data is sorted by the date field identified by @ReportDate and the user's parameter selection for ?Groupsalesperson determines whether or not the data is grouped and summarized by Salesperson.

Data Filtering

The data generated for the report is determined by Crystal Record Selection Formula and dependant on ?ReportStatusID:
case ?ReportStatusID = 1 the following conditions are applied
{GlData.EntryDateTime} IN {?StartDate} TO {?EndDate}
and ({GlData.GLAccountID} IN 11 TO 12 OR {GlData.GLClassificationType} IN 4000 TO 4999)
case ?ReportStatusID = 2 the following conditions are applied
{GlData.EntryDateTime} IN {?StartDate} TO {?EndDate}
and ({GlData.GLAccountID} = 12 OR {GlData.GLClassificationType} IN 4000 TO 4999)
case ?ReportStatusID = 3 the following conditions are applied
{GlData.EntryDateTime} IN {?StartDate} TO {?EndDate}
and {GlData.GLClassificationType} IN 4000 TO 4999
case ?ReportStatusID = 4 the following conditions are applied
{Order_Info.ClosedDate} IN {?StartDate} TO {?EndDate}
and {GlData.GLClassificationType} IN 4000 TO 4999
case ?ReportStatusID = 9 the following conditions are applied
{Order_Info.VoidedDate} IN {?StartDate} TO {?EndDate}


Summary Fields


Summary fields are necessary for group1, group2 and report footer.
This is accomplished by the use of

Running Total Fields:

#G1ModOrdCnt returns the DistinctCount of Order_Info.ID where @IsAdj is True for each Group 1 condition;
#G2ModOrdCnt returns the DistinctCount of Order_Info.ID where @IsAdj is True for each Group 2 condition;
#G1NewOrdCnt returns the DistinctCount of Order_Info.ID where @IsAdj is False for each Group 1 condition;
#G2NewOrdCnt returns the DistinctCount of Order_Info.ID where @IsAdj is False for each Group 2 condition;
#ModOrdCnt returns the DistinctCount of Order_Info.ID where @IsAdj is True for all rows;
#NewOrdCnt returns the DistinctCount of Order_Info.ID where @IsAdj is False for all rows;


Export

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

Test Cases