Differences

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

Link to this comparison view

wip_by_line_item_report_standard [2019/01/17 09:04] (current)
Line 1: Line 1:
 +{$page} ​
 +
 +
 +
 +Purpose ​
 +
 +
 +
 +The is one of the primary reports used for production management. The organization (detail) of this report is by Line Item; it is used when line items are routed independently and the entire order is not routed together.
 +
 +
 +
 +Information Structure ​
 +
 +
 +
 +The report shows, in logical hierarchy, information from the following different sources and the table the information is contained within:
 +  * Order Information (TransHeader)
 +    * Line Item Information (TransDetail) **Detail Record**
 +      * Station Information (Station)
 +      * Assigned To (Employee)
 +    * Company Information (Account)
 +    * Contact Information (AccountContact)
 +    * Order Salesperson (Employee)
 +    * Order Division (EmployeeGroup)
 +
 +
 +
 +Tables Accessed ​
 +
 +
 +
 +||~ **Purpose** ||~ **Table** ||~ **Linkage Information** ||~ **Filter Information** ||
 +|| Order Level || TransHeader ||  || TransHeader.StatusID ​ //?​StatusID// ​ ||
 +|| Line Item Level || TransDetail || TransDetail.TransHeaderID ​ TransHeader.ID ||   ||
 +|| Customer Info || Account || **Left Join** Account.ID ​ TransHeader.AccountID ||   ||
 +|| Contact Info || AccountContact || **Left Join** AccountContact.ID ​ TransHeader.ContactID ||   ||
 +|| Station Names || Station || **Left Join** Station.ID ​ TransDetail.StationID ||   ||
 +|| Salesperson || Employee || **Left Join** Employee.ID ​ TransHeader.Salesperson1ID ||   ||
 +|| Assigned To || Employee || **Left Join** Employee.ID ​ TransDetail.AssignedToID ||   ||
 +|| Division || EmployeeGroup || **Left Join** EmployeeGroup.ID ​ TransHeader.DivisionID ||   ||
 +Important Display Fields ​ \\ ||~ **Item** ||~ **Field** ||~ **Notes** ||
 +|| Order Number || TransHeader.OrderNumber ||   ||
 +|| Company Name || Account.CompanyName || Join Account.ID ​ TransHeader.AccountID ||
 +|| Company Name || Account.CompanyName || Join AccountContact.ID ​ TransHeader.ContactID ||
 +|| Contact Phone ||   ​|| ​  ||
 +|| Order Status || TransHeader.StatusText || Used for Grouping when displayed ||
 +|| Order Due Date || TransHeader.DueDate ||   ||
 +|| Line Item Description || TransDetail.Description ||   ||
 +|| Product || TransDetail.ItemName ||   ||
 +|| Quantity || TransDetail.Quantity ||   ||
 +|| Line Item Station || Station.StationName || JoinStation.ID ​ TransDetail.StationID ||
 +|| Line Item Subtotal || TransDetail.SubTotalPrice || Price before Tax-Optionally Suppressed ||
 +|| Line Item Number || TransDetail.LineItemNumber ||   ||
 +|| Order Created Date || TransHeader.CreatedDate ||   ||
 +|| Assigned To || Employee.FirstName + " " + Employee.LastName || Join Employee.ID ​ TransDetail.AssignedToID ||
 +Group By  \\ The following groups are inserted in the report design: \\ ||~ **Description** ||~ **Level** ||~ **Optional** ||~ **Group ID field** ||~ **Group Display Field** ||~ **Notes** ||
 +|| Division || 1 || No || TransHeader.DivisionID || Division.DivisionName ||   ||
 +|| Order Status || 2 || No || TransHeader.StatusID || TransHeader.StatusText ||   ||
 +|| Line Item Station || 3 (default) || Yes || Station.StationID || Station.StationName || ||
 +|| Salesperson || 3 || Yes || TransHeader.Salesperson1ID || Salesperson.FirstName + " " + Salesperson.LastName ||   ||
 +Sort Options ​ \\ The data within a group will always be sorted by these fields if they in the group by: \\ ||~ Description ||~ Sort Text ||~ Level ||~ Optional ||~ Notes ||
 +|| Division || Division.DivisionName || 1 || No ||   ||
 +|| Order Status || TransHeader.StatusText || 2  || No || ||
 +The user may optionally select to sort by their fields: \\ ||~ Description ||~ Sort Text ||~ Level ||~ Optional ||~ Notes ||
 +|| Line Item Station || Station.StationName || 3 || Yes || ||
 +|| Salesperson || Salesperson.FirstName + " " + Salesperson.LastName || 3 || Yes ||   ||
 +|| Company Name || Account.CompanyName || 3 || Yes  ||   ||
 +|| Due Date || TransDetail.DueDate || 3 || Yes  ||   ||
 +|| Order Number || TransHeader.ID || 3  || Yes  || Order number is in TransHeader.OrderNumber,​ but the ID follows the same sequence ||
 +|| Line Item Number || TransDetail.LineItemNumber || 4 || Yes  || ||
 +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 || ReportDateRange || DateTime Range ||  ||   || ||
 +|| Show Zero Dollar Orders || t.b.d. || Boolean || False || Having Sum(GL.Amount) ​ 0 ||   ||
 +|| Group by Salesperson || ?​GroupBySalesperson || Boolean || False || || Determines if saleserson is 2nd group or not ||
 +|| Salesperson || ?​Salesperson_EmployeeID || Integer || -1 || ((?​Salesperson_EmployeeID ​ -1) or (?​Salesperson_EmployeeID ​ TransHeader.Salesperson1ID)) || Use -1 for All ||
 +|| Company || ?​Company_AccountID || Integer || -1 || ((?​Salesperson_EmployeeID ​ -1) or (?​Salesperson_EmployeeID ​ GL.AccountID)) || Use -1 for All ||
 +|| Report Title || ?​ReportTitle || String ||   || || used in header ||
 +|| Report Path || ?ReportPath || String ||  || || show on bottom of report ||
 +|| Include Wip ||   || Boolean || True || TransHeader.StatusID1 || May be included with Show Built to show both ||
 +|| Include Built ||   || Boolean || True || TransHeader.StatusID2 || May be included with Show WIP to show both ||
 +|| Include Service Tickets ||   || Boolean || True || TransHeader.TransactionType6 || Always show TransactionType 1(regular orders) ||
 +|| Salesperson ||   || All (-1) || Integer|| TransDetail.StationID || -1 used to indicate all (no filtering done) ||
 +|| Show Prices ||   || Boolean || False ||   || This option suppresses the display of the following fields on the report and in any totals:​SubTotalPrice ||
 +|| Include Child Line Items ||   || Boolean || False || TransDetail.VariationID ​ TransDetail.ParentID || Used to include child line items. Child items have different parent and variation IDs. ||
 +|| Show Completed ||   || Boolean || False || TransDetail.IsComplete1 || Show or don't show completed line items ||
 +Notes: \\ * All data for orders in WIP and/or Built orders are pulled. Service tickets are a separate subset of orders that may or may not be included. \\ ** WIP Orders are identified as **TransHeader.StatusID1** \\ ** Built Orders are identified as **TransHeader.StatusID2** \\ ** Non-service ticket orders are identified as **TransHeader.TransactionType1** \\ ** Service Tickets are identified as **TransHeader.TransactionType6** \\ * Top-Level Line Items are the first level of line items in an order. They may have sub-line items, called Child Line Items. Child Line Items are identified by noting that their parent is not the Variation. \\ Summary Fields ​ \\ The following summary fields are necessary for each group. \\ ||~ Description ||~ Fields ||~ Notes ||
 +|| Number of Line Items || Count(DistinctTransDetail.ID) || ||
 +|| Sub Total || Sum(TransDetail.SubTotalPrice) || Total Price Before Tax - Optionally Suppressed ||
 +Export ​
 +
 +
 +
 +The report data should export into clean comma-delimited/​Excel data.
 +
 +
 +
 +Test Cases