Differences
This shows you the differences between two versions of the page.
— |
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 | ||
+ | || Order_Info || TransHeader || **Inner Join** TransHeader.ID | ||
+ | || Customer_Info || Account || **Left Join** Account.ID | ||
+ | || Contact_Info || AccountContact || **Left Join** AccountContact.ID | ||
+ | || Salesperson || Employee as Salesperson || **Left Join** Salesperson.ID | ||
+ | || Division || EmployeeGroup as Division || **Left Join** Division.ID | ||
+ | || Store || Store || Left Join StoreID | ||
+ | Notes: \\ * The Inner join of TransHeader table with Ledger typically results in multiple rows from Ledger for each row from Transheadder. \\ **Report Parameters** | ||
+ | || ? 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 || | ||
+ | || ? | ||
+ | || ?DateSorted || Boolean || False || || | ||
+ | || ? | ||
+ | || ? | ||
+ | Report Formulas | ||
+ | || @BalDue || Crystal || local currency value || display in report column || | ||
+ | || @GLamount || Crystal || local currency value || determine GLData.Amount dependant on ? | ||
+ | || @Group2Condition || Crystal || Number || determines Group 2 visibility and condition dependant on \\ ? | ||
+ | || @Group2Display || Crystal || String || determines Group 2 visibility and content dependant on? | ||
+ | || @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 ? | ||
+ | || @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 | ||
+ | || @Tprice || Crystal || local currencyvar || display the value of Order_Info.TotalPrice with the proper symbol || | ||
+ | Columns Displayed in Group 5 footer: | ||
+ | || 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 ? | ||
+ | |||
+ | |||
+ | |||
+ | Data Filtering | ||
+ | |||
+ | |||
+ | |||
+ | The data generated for the report is determined by Crystal Record Selection Formula and dependant on ? | ||
+ | |||
+ | |||
+ | |||
+ | case ? | ||
+ | |||
+ | |||
+ | |||
+ | {GlData.EntryDateTime} IN {? | ||
+ | |||
+ | |||
+ | |||
+ | and ({GlData.GLAccountID} IN 11 TO 12 OR {GlData.GLClassificationType} IN 4000 TO 4999) | ||
+ | |||
+ | |||
+ | |||
+ | case ? | ||
+ | |||
+ | |||
+ | |||
+ | {GlData.EntryDateTime} IN {? | ||
+ | |||
+ | |||
+ | |||
+ | and ({GlData.GLAccountID} | ||
+ | |||
+ | |||
+ | |||
+ | case ? | ||
+ | |||
+ | |||
+ | |||
+ | {GlData.EntryDateTime} IN {? | ||
+ | |||
+ | |||
+ | |||
+ | and {GlData.GLClassificationType} IN 4000 TO 4999 | ||
+ | |||
+ | |||
+ | |||
+ | case ? | ||
+ | |||
+ | |||
+ | |||
+ | {Order_Info.ClosedDate} IN {? | ||
+ | |||
+ | |||
+ | |||
+ | and {GlData.GLClassificationType} IN 4000 TO 4999 | ||
+ | |||
+ | |||
+ | |||
+ | case ? | ||
+ | |||
+ | |||
+ | |||
+ | {Order_Info.VoidedDate} IN {? | ||
+ | |||
+ | |||
+ | |||
+ | Summary Fields | ||
+ | |||
+ | |||
+ | |||
+ | Summary fields are necessary for group1, group2 and report footer. | ||
+ | |||
+ | |||
+ | |||
+ | This is accomplished by the use of | ||
+ | |||
+ | |||
+ | |||
+ | Running Total Fields: | ||
+ | |||
+ | |||
+ | |||
+ | # | ||
+ | |||
+ | |||
+ | |||
+ | # | ||
+ | |||
+ | |||
+ | |||
+ | # | ||
+ | |||
+ | |||
+ | |||
+ | # | ||
+ | |||
+ | |||
+ | |||
+ | #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/ | ||
+ | |||
+ | |||
+ | |||
+ | Test Cases | ||