{$page}
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