Add Unit Price to Order Item Report

Duplicate the Standard Order Ticket - Order Item report

1. Go to Reports module > Customize reports
2. Search the standard reports for the Standard Order Ticket - Order Item report.
3. Click Duplicate Report.
4. Enter a new short name for the report.
5. Click Save
6. Click Open Report Designer
7. Click File > Save As
8. In the File Name field, enter a name for the report and click Save.
9. Close the Report Designer
10. Click the box to the right of the filename field and select the file you saved in step 8 above.

Add Query

1. Click the Advanced tab.
2. Right click in the SQL Statement box and choose Select All

3. Delete the selected text to clear the SQL Statement box.
4. Copy the query below and paste into the SQL Statement box.
5. Click Test SQL. You should get a message that SQL is Valid. Click Save at the top.

SELECT

               UID, InvoiceUID, OrderNumber, ServPerf, ServReq

INTO #TempOrders FROM SERVICE WHERE

               (:RecordUID is null or :RecordUID = InvoiceUID)

AND InvoiceTypeCode =7 UNION ALL --Install SELECT

               UID, InvoiceUID, orderNumber, ServPerf, ServReq

FROM Install WHERE

               (:RecordUID is null or :RecordUID = InvoiceUID) AND

InvoiceTypeCode =7 UNION ALL --Invoice that is not a type code 7 which handled above SELECT

               UID, UID as InvoiceUID, InvoiceNumber as Ordernumber, ServPerf, ServReq

FROM Invoice WHERE

               (:RecordUID is null or :RecordUID = UID)
               and InvoiceTypeCode <> 7;

--Fill in Report information --generic reference SELECT TOS.InvoiceUID,TOS.OrderNumber, TOS.ServPerf as ServicePerformed, TOS.ServReq as ServiceRequested, OD.*, I.*,(I.Payments + I.PrepaidUsed) as PayCredit, 0.00 as UnitPrice, null as itemnum FROM OrderDetail OD LEFT JOIN #TempOrders TOS ON OD.OrderUID = TOS.UID FULL JOIN Invoice I ON TOS.InvoiceUID = I.UID WHERE

   (:RecordUID is null or I.UID = :RecordUID or TOS.InvoiceUID =:RecordUID )and
   ( OD.PrintHide = 'False' or OD.PrintHide is null) and (OD.ItemTypeCode != 1  and OD.ItemTypeCode != 2 and OD.ItemTypeCode != 3)
 

UNION ALL SELECT TOS.InvoiceUID,TOS.OrderNumber, TOS.ServPerf as ServicePerformed, TOS.ServReq as ServiceRequested, OD.*, I.*,(I.Payments + I.PrepaidUsed) as PayCredit, ot.LaborRate as unitprice, null as itemnum FROM OrderDetail OD LEFT JOIN #TempOrders TOS ON OD.OrderUID = TOS.UID FULL JOIN Invoice I ON TOS.InvoiceUID = I.UID join OrderTask OT on OD.DetailUID = OT.UID WHERE

   (:RecordUID is null or I.UID = :RecordUID or TOS.InvoiceUID =:RecordUID )and
   ( OD.PrintHide = 'False' or OD.PrintHide is null) and (OD.ItemTypeCode = 1)

UNION ALL SELECT TOS.InvoiceUID,TOS.OrderNumber, TOS.ServPerf as ServicePerformed, TOS.ServReq as ServiceRequested, OD.*, I.*,(I.Payments + I.PrepaidUsed) as PayCredit, ot.Retail as unitprice, null as itemnum FROM OrderDetail OD LEFT JOIN #TempOrders TOS ON OD.OrderUID = TOS.UID FULL JOIN Invoice I ON TOS.InvoiceUID = I.UID join OrderServices OT on OD.DetailUID = OT.UID WHERE

   (:RecordUID is null or I.UID = :RecordUID or TOS.InvoiceUID =:RecordUID )and
   ( OD.PrintHide = 'False' or OD.PrintHide is null) and (OD.ItemTypeCode = 2)

UNION ALL SELECT TOS.InvoiceUID,TOS.OrderNumber, TOS.ServPerf as ServicePerformed, TOS.ServReq as ServiceRequested, OD.*, I.*,(I.Payments + I.PrepaidUsed) as PayCredit, ot.Retail as unitprice, ot.ItemNum FROM OrderDetail OD LEFT JOIN #TempOrders TOS ON OD.OrderUID = TOS.UID FULL JOIN Invoice I ON TOS.InvoiceUID = I.UID join OrderMat OT on OD.DetailUID = OT.UID WHERE

   (:RecordUID is null or I.UID = :RecordUID or TOS.InvoiceUID =:RecordUID )and
   ( OD.PrintHide = 'False' or OD.PrintHide is null) and (OD.ItemTypeCode = 3)

Order by I.UID , OrderNumber, OD.Sequence

Make Room for Unit Price

1. Click Open Report Designer
2. Locate the Name, Description, Qty and Total column headers. Move them around to make room for the Unit Price label you are going to add. Move the data corresponding to the label to keep alignment with the label (image below). If you aren't using the Group column, you can delete that to make more room.

Add Unit Price to the Report

1. Click the Text tool. (image below)
2. Click your cursor to where you want the Unit Price column label and edit the text to say "Unit Price" (image below)

3. Click the DBText tool (image below)

4. Click your cursor where you want the Unit Price to show (under the "Unit Price" text you just added)
5. Select UnitPrice from the query drop down in upper left (image below)

6. Click File > Save.