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