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