Add Unit Price to Order Item Report - Revision history

Amy at 23:08, 11 June 2012

2012-06-11T23:08:22Z

Amy: /* Add Query */

2012-06-11T22:49:05Z

Add Query

Amy: /* Add Query */

2012-06-11T22:46:07Z

Add Query

Amy: /* Add Query */

2012-06-11T22:45:04Z

Add Query

Amy: /* Add Query */

2012-06-11T22:44:31Z

Add Query

Amy: Created page with " == Duplicate the Standard Order Ticket - Order Item report ==
1. Go to '''Reports''' module > '''Customize reports'''
2. Search the standard reports for the '''Standard..."

2012-06-11T22:40:56Z

Created page with " == Duplicate the Standard Order Ticket - Order Item report == 1. Go to '''Reports''' module > '''Customize reports''' 2. Search the standard reports for the '''Standard..."

New page

== 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.
 [[File:ReportFilenameField.png]]

== Add Query ==
 1. Click the '''Advanced''' tab.
 2. Right click in the SQL Statement box and choose '''Select All'''
 [[File:SQLStatementSelectAll.png]]
 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.
 [[File: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)
 [[File:AddALabel.png]]
 3. Click the '''DBText''' tool (image below)
 [[File: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)
 [[File:SelectUnitPrice.png]]
 6. Click '''File''' >''' Save'''.

← Older revision		Revision as of 23:08, 11 June 2012
Line 1:		Line 1:
−

	== Duplicate the Standard Order Ticket - Order Item report ==		== Duplicate the Standard Order Ticket - Order Item report ==

@@ Line 21: / Line 21: @@
 <br>3. Delete the selected text to clear the SQL Statement box.
 <br>4. Copy the query in the linked doc: [[File:AddUnitPriceToOrderItemReport.doc‎]]
-<br>5. Paste into the SQL Statement box.
+<br>5. Paste the query into the SQL Statement box.
 <br>6.. Click '''Test SQL'''. You should get a message that SQL is Valid. Click '''Save''' at the top.

@@ Line 20: / Line 20: @@
 <br>[[File:SQLStatementSelectAll.png]]
 <br>3. Delete the selected text to clear the SQL Statement box.
-<br>4. Copy the query in the linked doc: AddUnitPriceToOrderItemReport.doc
+<br>4. Copy the query in the linked doc: [[File:AddUnitPriceToOrderItemReport.doc‎]]
 <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.

@@ Line 20: / Line 20: @@
 <br>[[File:SQLStatementSelectAll.png]]
 <br>3. Delete the selected text to clear the SQL Statement box.
-<br>4. Copy the query in the linked doc: [[File:AddUnitPriceToOrderItemReport.doc]]
+<br>4. Copy the query in the linked doc: AddUnitPriceToOrderItemReport.doc
 <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.

@@ Line 20: / Line 20: @@
 <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>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.
 == Make Room for Unit Price ==

Add Unit Price to Order Item Report - Revision history

Amy at 23:08, 11 June 2012

Amy: /* Add Query */

Amy: /* Add Query */

Amy: /* Add Query */

Amy: /* Add Query */

Amy: Created page with " == Duplicate the Standard Order Ticket - Order Item report == 1. Go to '''Reports''' module > '''Customize reports''' 2. Search the standard reports for the '''Standard..."

Amy: Created page with " == Duplicate the Standard Order Ticket - Order Item report ==
1. Go to '''Reports''' module > '''Customize reports'''
2. Search the standard reports for the '''Standard..."