Morning all -
I'm putting together a summary report of tooling orders, showing the net profit and loss. I'm getting the correct records returned, but crystal is duplicating some records.
I have a companion report that looks at just one report at a time, showing all receivables, payables, and labor logged for whichever customer order is entered as the report parameter. The receivables portion is in the main report, the payables and labor data are subreports. This report works awesome, but I can't seem to modify it to function as a summary report - specifically, the subreports won't pull any records.
So, I created a new report that will show all closed tooling orders. I managed to link all the tables together - at least in sql - I am getting all the correct records returned. Problem is in crystal. For example, if there was one receivable created, but there were three payables(vouchers) associated with that order, Crystal is multiplying the receivable total by three. Same thing conversely - one payable associated and three receivables, it's multiplying the payable amount times three.
You can see in the image that first two columns of $17,900 are correct - they are the amount invoiced and the amount paid. The next column is the amount of payables incurred and the next is vouchers. Crystal is multiplying 17,900*3 since there were three receivable lines. I'm sure this is because I have the receivables data in detail section a, the payables is on details b, and the labor is in detail c.
I just don't know how else to get this done.
(The $0.00 column is labor, which is correct in this example, but the last column that says $-35,800 is the net profit/loss and should be $0.)
Here's another shot with the detail sections showing:
Any help is greatly appreciated!
Here's the crystal:
And here's the sql:
SELECT RECEIVABLE.INVOICE_ID, RECEIVABLE.CUSTOMER_ID, RECEIVABLE.TYPE, RECEIVABLE.INVOICE_DATE, RECEIVABLE.TOTAL_AMOUNT,
RECEIVABLE.PAID_AMOUNT, RECEIVABLE.LAST_PAID_DATE, RECEIVABLE.CREATE_DATE, RECEIVABLE.STATUS, RECEIVABLE.VAT_BOOK_CODE,
RECEIVABLE_LINE.CUST_ORDER_ID, CUST_ORDER_LINE.PART_ID, CUST_ORDER_LINE.PRICE_NOTE, CUSTOMER.NAME, VENDOR.NAME AS vend_name,
PURCHASE_ORDER.ID, PURCHASE_ORDER.VENDOR_ID, PURCHASE_ORDER.ORDER_DATE, PURCHASE_ORDER.TOTAL_AMT_ORDERED,
PAYABLE_LINE.VOUCHER_ID, PAYABLE_LINE.AMOUNT, uniPoint_Live.dbo.PT_Equip_Maint.Equip_num, uniPoint_Live.dbo.PT_Equip_Maint.Act_labor_hrs,
uniPoint_Live.dbo.PT_Equip_Maint.Act_material_cost, uniPoint_Live.dbo.PT_Equip_Maint.Act_downtime_cost, RECEIVABLE_LINE.LINE_NO
FROM uniPoint_Live.dbo.PT_Equip_Maint RIGHT OUTER JOIN
RECEIVABLE INNER JOIN
RECEIVABLE_LINE ON RECEIVABLE.INVOICE_ID = RECEIVABLE_LINE.INVOICE_ID INNER JOIN
CUST_ORDER_LINE ON RECEIVABLE_LINE.CUST_ORDER_ID = CUST_ORDER_LINE.CUST_ORDER_ID AND
RECEIVABLE_LINE.CUST_ORDER_LINE_NO = CUST_ORDER_LINE.LINE_NO INNER JOIN
CUSTOMER ON RECEIVABLE.CUSTOMER_ID = CUSTOMER.ID ON
uniPoint_Live.dbo.PT_Equip_Maint.Equip_num = CUST_ORDER_LINE.PRICE_NOTE LEFT OUTER JOIN
PURCHASE_ORDER INNER JOIN
VENDOR ON PURCHASE_ORDER.VENDOR_ID = VENDOR.ID INNER JOIN
PAYABLE_LINE ON PURCHASE_ORDER.ID = PAYABLE_LINE.PURC_ORDER_ID ON
CUST_ORDER_LINE.CUST_ORDER_ID = PURCHASE_ORDER.SALES_ORDER_ID
WHERE (CUST_ORDER_LINE.PART_ID LIKE 'tool%') and RECEIVABLE.PAID_AMOUNT<> RECEIVABLE.TOTAL_AMOUNT