WIP by Line Item Report (Standard)


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.StatusID=1
May be included with Show Built to show both
Include Built

Boolean
True
TransHeader.StatusID=2
May be included with Show WIP to show both
Include Service Tickets

Boolean
True
TransHeader.TransactionType=6
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.IsComplete=1
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.StatusID=1
    • Built Orders are identified as TransHeader.StatusID=2
    • Non-service ticket orders are identified as TransHeader.TransactionType=1
    • Service Tickets are identified as TransHeader.TransactionType=6
  • 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