Difference between revisions of "Add Unit Price to Order Item Report"

From High5Wiki
Jump to navigation Jump to search
(Created page with " == 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...")
 
Line 20: Line 20:
 
<br>[[File:SQLStatementSelectAll.png]]
 
<br>[[File:SQLStatementSelectAll.png]]
 
<br>3. Delete the selected text to clear the SQL Statement box.
 
<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>4. Copy the query in the linked doc: [[File:AddUnitPriceToOrderItemReport.doc]]
<br>5. Click '''Test SQL'''. You should get a message that SQL is Valid. Click '''Save''' at the top.
+
<br>5. Paste into the SQL Statement box.
 
+
<br>6.. 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 ==
 
== Make Room for Unit Price ==

Revision as of 22:44, 11 June 2012


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.
ReportFilenameField.png


Add Query


1. Click the Advanced tab.
2. Right click in the SQL Statement box and choose Select All
SQLStatementSelectAll.png
3. Delete the selected text to clear the SQL Statement box.
4. Copy the query in the linked doc: File:AddUnitPriceToOrderItemReport.doc
5. Paste into the SQL Statement box.
6.. Click Test SQL. You should get a message that SQL is Valid. Click Save at the top.

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.
MakeRoomForUnitPrice.png

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)
AddALabel.png
3. Click the DBText tool (image below)
ClicktheDBTextTool.png
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)
SelectUnitPrice.png
6. Click File > Save.