Differences
This shows you the differences between two versions of the page.
sales_report_standard [2019/01/17 09:04] |
sales_report_standard [2019/01/17 09:04] (current) |
||
---|---|---|---|
Line 1: | Line 1: | ||
+ | {$page} | ||
+ | |||
+ | |||
+ | |||
+ | Purpose | ||
+ | |||
+ | |||
+ | |||
+ | This report shows Ledger activity occuring during a specific time frame. The report reflects both new sales as well as adjustments to previous sales. All sales numbers are pulled from the GL view which filters the ledger by OffBalanceSheet | ||
+ | |||
+ | |||
+ | |||
+ | Information Structure | ||
+ | |||
+ | |||
+ | |||
+ | Because the report also shows adjustments to orders, the data must be pulled primarily 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 General Ledger (GL view of the Ledger) | ||
+ | * The order record (TransHeader). | ||
+ | * The Salesperson for the order (Employee). | ||
+ | * The Company record (Account). | ||
+ | * The Division Name for the Orders (EmployeGroup). | ||
+ | |||
+ | |||
+ | |||
+ | Tables Accessed | ||
+ | |||
+ | |||
+ | |||
+ | ||~ **Name(Alias)** ||~ **Table/ | ||
+ | || GL || GL (a View of Ledger) || || EntryDateTime between //? | ||
+ | || Order_Info || TransHeader || **Inner Join** TransHeader.ID | ||
+ | || Customer_Info || Account || **Left Join** Customer_Info.ID | ||
+ | || Salesperson || Employee || **Left Join** Salesperson.ID | ||
+ | || Division || EmployeeGroup || **Left Join** Division.ID | ||
+ | || Store || Store || || (Store.ID > 0) || | ||
+ | Notes: The table " | ||
+ | || Order Number || TransHeader.OrderNumber || || | ||
+ | || Sales Person || Salesperson.FirstName + " " + Salesperson.LastName || | ||
+ | || Company || Account.CompanyName || || | ||
+ | || Order Description || TransHeader.Description || || | ||
+ | || Sale Date || GL.EntryDateTime || || | ||
+ | || Sale Amount || **Sum(GL.Amount)** || || | ||
+ | || Rows || Count of EntryDateTime rows in a group || "Is Adj" is not needed as a column as it is a mandatory group. Adding this column to reflect the number of rows from GL that are summed in a group may be a useful bit of info! || | ||
+ | || Current Order Status || TransHeader.OrderStatus || || | ||
+ | || Current Order Total || TransHeader.TotalPrice || || | ||
+ | || Current Balance Due || TransHeader.BalanceDue || || | ||
+ | Group By \\ The following groups are inserted in the report design: \\ ||~ **Description** ||~ **Level** ||~ **Optional** ||~ **Group ID field** ||~ **Group Display Field** ||~ **Notes** || | ||
+ | || Division || 1 || No || GL.DivisionID || Division.DivisionName || || | ||
+ | || Salesperson || 2 || Yes || TransHeader.Salesperson1ID || Salesperson.FirstName + " " + Salesperson.LastName || || | ||
+ | || Sale/Adj || 3 || No || @IsAdj || "Order Marked Sale" or " | ||
+ | || EntryDateTime || 4 || Yes || GL.EntryDateTime || " | ||
+ | || Customer || 5 || Yes || Customer_Info.CustomerName || Customer_Info.CustomerName || || | ||
+ | || Order# || 6 || Yes || Order_Info.ID || | ||
+ | Sort Options | ||
+ | || Sales Date Range || fx_RangeStart_Date \\ fx_RangeEnd_Date || DateTime Range || | ||
+ | || Date Range || ?Date || | ||
+ | || Show Zero Dollar Orders || t.b.d. || Boolean || False || Having Sum(GL.Amount) | ||
+ | || Group by Salesperson || ? | ||
+ | || Sorting || ? | ||
+ | || Salesperson as a List || ? | ||
+ | || Company as a List || ? | ||
+ | || Export || ?Export || Boolean || | ||
+ | Report Formulas | ||
+ | || @AmtLbl || || String || creates a column header for Amount in Group 3 || | ||
+ | || @BalDue || Crystal || local currency value || display in report column || | ||
+ | || @CompSumGLamt || || local currency value || Sums the values of @GLAmount for Group 5 || | ||
+ | || @ContactName || || {Contact_Info.FirstName} + " " + {Contact_Info.LastName} || Concatenation || | ||
+ | || @Continued || || string || adds " | ||
+ | || @CrystalPath || || string || Holds the Special Field "File Path and Name" for passing as a parameter to header subreport and display at the bottom of the main report. || | ||
+ | || @CrystalTitle || || string || Holds the Special Field " | ||
+ | || @DescStr || || string || trims {Order_Info.Description} to display only 35 characters || | ||
+ | || @Edate || || Date || Used to define the Minimum date from ?Date || | ||
+ | || @EDSumGLamt || || local currency value || Sums the values of @GLAmount for Group 4 || | ||
+ | || @GLAmount || Crystal || local currency value || Flips the sign of GL.amount and adds local currency symbol || | ||
+ | || @Group2Condition || || null or salesperson.ID || The return value is dependant on ? | ||
+ | || @Group2Display || || {Salesperson.FirstName} + " " + {Salesperson.LastName} OR an empty string || The return value is dependant on ? | ||
+ | || @IsAdj || || Boolean || determines if Transheader.SaleDate is IN @Sdate TO @Edate || | ||
+ | || @Sdate || || Date || Used to define the Maximum date from ?Date || | ||
+ | || @SumGLAmt || || local currency value || Sums the values of @GLAmount for Group 6 || | ||
+ | || @Tprice || || local currency value || || | ||
+ | Summary Fields | ||
+ | || Number of New Orders || Count(GL.TransHeaderID) where Order Sale Date between Report Date Range || | ||
+ | || Number of Adjusted Orders || Count(GL.TransHeaderID) where Order Sale Date //NOT// between Report Date Range || | ||
+ | || Total Amount || Sum(GL.Amount) || | ||
+ | Export | ||
+ | |||
+ | |||
+ | |||
+ | The report data should export into clean comma-delimited/ | ||
+ | |||
+ | |||
+ | |||
+ | Test Cases | ||