Differences

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

Link to this comparison view

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  0.
 +
 +
 +
 +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/View** ||~ **Linkage Information** ||~ **Filter Information** ||
 +|| GL || GL (a View of Ledger) ||   || EntryDateTime between //?StartDate// and //?EndDate// and \\ GLClassificationType between 4000 and 4999 ||
 +|| Order_Info || TransHeader || **Inner Join** TransHeader.ID  GL.TransactionID ||   ||
 +|| Customer_Info || Account || **Left Join** Customer_Info.ID  GL.AccountID ||   ||
 +|| Salesperson || Employee || **Left Join** Salesperson.ID  TransHeaders.Salesperson1ID ||   ||
 +|| Division || EmployeeGroup || **Left Join** Division.ID  GL.DivisionID ||   ||
 +|| Store || Store ||   || (Store.ID > 0) ||
 +Notes: The table "Store" is used only to reflect the Store.tagline at the bottom of the report and the value of that field is also available from the Command used as a data source for the sub report that is displayed as the Report Header. \\ *  \\ Important Display Fields  \\ ||~ **Item** ||~ **Field** ||~ **Notes** ||
 +|| 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 "Adjustment" ||   ||
 +|| EntryDateTime || 4 || Yes || GL.EntryDateTime || "EntryDateTime" + GL.EntryDateTime ||   ||
 +|| Customer || 5 || Yes || Customer_Info.CustomerName || Customer_Info.CustomerName ||   ||
 +|| Order# || 6 || Yes || Order_Info.ID ||   ||   ||
 +Sort Options  \\ Data in the report is Sorted exclusively by grouping. The first group (Division) is mandatory and the data will therefore first be sorted by Division.ID. The second group (salesperson) is optional dependant upon ?Groupsalesperson. The group condition for group 2 is derived from the formula @Group2condition which returns a value of 0 when ?Groupsalesperson is false. In that case, no sorting occurs within group2. The third group (Sale/Adj) is mandatory and all of the rows within each Division of group 1 will therefore be sorted into two groups. The fourth group (EntryDateTime) is optional but only in the sense that headers and footers for the group are displayed only in the case when ?OptionalSort has a value of "EntryDate". The group remains active and data in the group is sorted by EntryDateTime even when the value of ?OptionalSort is set to something else. The fifth group (Customer) is also optional but in the same sense as the fourth group. The sixth group (Order) is also optional and differs from groups 4 and 5 in that group headers are not needed for group 6 because the Order number will appear as the left most column display in the group footer. \\ Parameters and Filter Options  \\ The following options need to be implemented to filter the data and/or provide information to reconfigure the report display. \\ ||~ **Description** ||~ **Parameter Name** ||~ Type ||~ **Default** ||~ **Data Filter** ||~ **Notes** ||
 +|| Sales Date Range || fx_RangeStart_Date \\ fx_RangeEnd_Date || DateTime Range ||   ||   || tells Control to return ?Date as a Range ||
 +|| Date Range || ?Date ||   ||   || GL.EntryDateTime IN Mimum(?Date) TO Maximum(?Date) || Has a Min and Max value ||
 +|| Show Zero Dollar Orders || t.b.d. || Boolean || False || Having Sum(GL.Amount)  0 || Used as a suppress condition in group footers ||
 +|| Group by Salesperson || ?GroupBySalesperson || Boolean || False ||   || Determines if saleserson is 2nd group or not ||
 +|| Sorting || ?OptionalSort || String ||   || "EntryDate", "OrderNumber", "Customer", or "None" || Determines behavior of Groups 4, 5 & 6 ||
 +|| Salesperson as a List || ?Salesperson_SalespersonID || Integer || -1 || ((?Salesperson_SalespersonID  -1) or (?Salesperson_SalespersonID  TransHeader.Salesperson1ID)) || Use -1 for All ||
 +|| Company as a List || ?Company_CustomerID || Integer || -1 || ?Company_CustomerID  -1) or (? \\ ?Company_CustomerID GL.AccountID)) || Use -1 for All ||
 +|| Export || ?Export || Boolean ||   ||   || Used to format for export ||
 +Report Formulas  \\ ||~ Formula Name ||~ Syntax ||~ Return value ||~ Purpose ||
 +|| @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 "Continued" to group headers when the group breaks to a new page ||
 +|| @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 "Report Title" for passing as a parameter to header subreport. The value of the Special Field may be edited in the report File->Summary Info.Title ||
 +|| @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 ?GroupSalesperson ||
 +|| @Group2Display ||   || {Salesperson.FirstName} + " " + {Salesperson.LastName} OR an empty string || The return value is dependant on ?GroupSalesperson ||
 +|| @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  \\ The following summary fields are necessary for each group. \\ ||~ **Description** ||~ 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/Excel data.
 +
 +
 +
 +Test Cases