Show pageOld revisionsBacklinksBack to top This page is read only. You can view the source, but not change it. Ask your administrator if you think this is wrong. CKG Edit {$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