Differences

This shows you the differences between two versions of the page.

Link to this comparison view

orders_placed_between_report_standard [2019/01/17 09:04] (current)
Line 1: Line 1:
 +{$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