WIP and Built 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)
    • Order Station Information (Station)

    • 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
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 = TransHeader.StationID

Salesperson
Employee
Left Join Employee.ID = TransHeader.Salesperson1ID

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

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

Salesperson
3
Yes
TransHeader.Salesperson1ID
Salesperson.FirstName + " " + Salesperson.LastName

Order Station
4
Yes
TransHeader.StationID
Station.StationName


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
Salesperson
Salesperson.FirstName + " " + Salesperson.LastName
3
Yes

Company Name
Account.CompanyName
3
Yes

Due Date
TransDetail.DueDate
3
Yes

Order Station
TransHeader.StationID
3
Yes

Some User choices for Sorting will actually be redundant. For example, If the User has elected to Group on Salesperson then choosing to Sort by Salesperson would accomplish nothing. Therefore the order in which rows are arranged inside the Group depends upon the grouping condition:

If Grouping On
Sort Choice
Sorting Order of the Report
SalesPerson
Salesperson
Company Name, then Due Date, then Station

Company Name
Company Name, then Due Date, then Station

Due Date
Due Date, then Company Name, then Station

Order Station
Station, then Company Name, then Due Date
Station
Salesperson
Salesperson, then Company Name, then Due Date

Company Name
Company Name, then Due Date, then Salesperson

Due Date
Due Date, then Company Name, then Salesperson

Order Station
Company Name, then Salesperson, then Due Date
None
Salesperson
Saleperson, then Station, then Due Date, then Company Name

Company Name
Company Name, then Due Date, then Station, then Salesperson

Due Date
Due Date, then Company Name, then Station, then Salesperson

Order Station
Station, then Company Name, then Due Date, then Salesperson

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



Group by Salesperson
?GroupBySalesperson
Boolean
False

Determines if saleserson is 3rd group or not
Group by Station
?GroupByStation
Boolean
False

Determines if saleserson is 4th 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)
Show Prices

Boolean
False

This option suppresses the display of the following fields on the report and in any totals:SubTotalPrice

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