Editing Add Unit Price to Order Item Report



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


== Add Query  ==
<br>1. Click the '''Advanced''' tab.
<br>2. Right click in the SQL Statement box and choose '''Select All'''
<br>[[File:SQLStatementSelectAll.png]]
<br>3. Delete the selected text to clear the SQL Statement box.
<br>4. Copy the query below and paste into the SQL Statement box.
<br>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 ==
<br>1. '''Click Open Report Designer'''
<br>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.
<br>[[File:MakeRoomForUnitPrice.png]]

== Add Unit Price to the Report ==
<br>1. Click the '''Text''' tool. (image below)
<br>2. Click your cursor to where you want the Unit Price column label and edit the text to say "Unit Price" (image below)
<br>[[File:AddALabel.png]]
<br>3. Click the '''DBText''' tool (image below)
<br>[[File:ClicktheDBTextTool.png]]
<br>4. Click your cursor where you want the Unit Price to show (under the "Unit Price" text you just added)
<br>5. Select '''UnitPrice''' from the query drop down in upper left (image below)
<br>[[File:SelectUnitPrice.png]]
<br>6. Click '''File''' >''' Save'''.