Work Order Report
Purpose
This is the primary invoice report.
Information Structure
The report shows, in logical hierarchy, information from the following different sources and the table the information is contained within:
- The Header Record (TransHeader)
- The [top-level] Line Items and Child Line Items[TransDetail].
- The estimated part usage (TransPart) links for each line item.
- The part that is estimated to be used (Part)
- The station (Station) this part is used in.
- The shipments assigned to this Order (Shipment)
- SUBREPORT: The shipment details for each shipment (Function ShipmentItemsTable(ShipmentID int) )
- The line items shipped (TransDetail) in that detail.
- The Company record (Account).
- The Primary Contact record (AccountContact).
- The Salesperson for the Order(Employee).
- The Employee who Entered the Order (Employee).
- The Division Name for the Order (EmployeeGroup).
Tables Accessed
| Purpose | Table | Linkage Information | Filter Information | ||||
| OrderLevel | TransHeader | TransHeaderIDparam | |||||
| LineItemLevel | TransDetail | TransDetail.TransHeaderID TransHeader.ID | |||||
| ShippingAddress | Address | OrderLevel.ShippingAddressID ShippingAddress.ID | |||||
| ShipToCompany | Account | OrderLevel.ShippingCompanyID ShipToCompany.ID | |||||
| ShippingContact | AccountContact | OrderLevel.ShippingContactID ShippingContact.ID | |||||
| CustomerInfo | Account | Left Join Account.ID TransHeader.AccountID | |||||
| ContactInfo | AccountContact | Left JoinAccountContact.ID TransHeader.ContactID | |||||
| Salesperson | Employee as Salesperson | Left Join Salesperson.ID TransHeader.Salesperson1ID | |||||
| EnteredBy | Employee as EnteredBy | Left Join EnteredBy.ID TransHeader.EnteredByID | |||||
| Division | EmployeeGroup as Division | Left Join Division.ID TransHeader.DivisionID |
workorderlinks.jpg_width800_height443
Shipment SubReport
|| Purpose || Table || Linkage Information || Filter Information ||
| Shipment Detail | database View “ShipmentDetail” | ShipmentDetail.TransheaderID Pm-?Transheader.ID |
|||||
| Shipments | Shipments | ShipmentDetail.TransHeaderID Shipments.TransHeaderID | |||||
| TransDetail | TransDetail | ShipmentDetail.LineItemID –> TransDetail.ID | |||||
| Account | Account | Shipments.AccountID –> Account.ID | |||||
| AccountContact | AccountContact | Shipments.ContactID –> AccountContact.ID | |||||
| ShippingMethod | ShippingMethod | Shipments.CarrierID –> ShippingMethod.ID | |||||
| AddressLink | AddressLink | Shipments.ShipToAddressLinkID –> AddressLink.ID | |||||
| Address | Address | AddressLink.AddressID –> Address.ID |
shipmentsublink.jpg_width800_height522
Parts List SubReport
||~ Alias ||~ Table ||~ Link or Filter ||
| Part_Link | TransPart | Part_Link.TransheaderID Pm-?TransDetail.ID | |||
| Part | Part | Part.ID Part_Link.PartID | |||
| GoodsItemPartLink | GoodsItemPartLink | GoodsItemPartLink.ID TransPart.PartLinkID | |||
| PartsStation | Station | PartsStation.ID Part.StationID | |||
| Order Number | TransHeader.OrderNumber | ||||
| Line Item Description | TransDetail.Description | ||||
| Product | TransDetail.GoodsItemCode | ||||
| Quantity | TransDetail.Quantity | ||||
| Line Item Description | TransDetail.HTMLLongFormat | This must be formatted as HTML in Crystal Reports and set to auto-expand | |||
| Line Item Subtotal | TransDetail.SubTotalPrice | Suppressed based on options | |||
| Line Item Number | TransDetail.LineItemNumber | ||||
| Order Created Date | TransHeader.OrderDate | ||||
| Company Name | Account.CompanyName | ||||
| Salesperson | Employee.FirstName + “ ” + Employee.LastName |
Group By
Main Report:
The following groups are static in the
|| Description || Level || Optional || Group ID field || Group Display Field || Notes ||
| Division | 1 | No | TransHeader.DivisionID | Division.DivisionName | |||||||
| Order | 2 | No | OrderLevel.ID | OrderLevel.OrderNumber | |||||||
| LineItem | 3 | No | LineItemLevel.ID as formatted by @LineItemSort |
PartsList Subreport:
grouped by Station.StationName
ShipmentView subreport
grouped first by Shipments.ShipmentNumber and second by TransDetail.LineItemNumber
Sort Options
The data within a group will always been sorted by the non-optional fields below and (if present) the user is given options to sort by the other fields.
|| Description || Sort Text || Level || Optional || Notes ||
| Order | TransHeader.OrderNumber | 1 | No | ||||||
| Line Item | TransDetail.LineItemNumber | 2 | No | ||||||
| Line Item Station | TransDetail.LineItemNumber | 3 | No | This Sort would only seem to apply to the subreport; therefore delete. | |||||
| Part Type | Part.PartType | 4 | No | Not previously done in the subreport! delete????? | |||||
| Part Name | Part.ItemName | 5 | No | Not previously done in the subreport! delete????? |
There are no user defined sort of options in this report.
User 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 || Default || Data Filter || Notes ||
| Show Line Item Totals | True | n/a | Hide or Display the SubTotalPrice on the individual line item. | ||||||
| Show Service Ticket Stub | True | ||||||||
| Print Images | True | If present, show thumbnails for each Line Item | |||||||
| Show Parts Information | True | If true, shows the parts subreport | |||||||
| Show Shipping Information | True | If true, shows the shipment subreport |
Summary Fields
There are no summary fields for this report.
Export
The TransDetail data should export into clean comma-delimited/Excel data.
Test Cases