Crystal Reports Duplication

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.  
Report snippet
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:
 details
Any help is greatly appreciated!


Here's the crystal:
 cr report
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

Open in new window

LVL 1
Tina KSystems SpecialistAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

James0628Commented:
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.
Not really.  It has nothing to do with having 3 detail sections.  It's just that you have multiple detail records.  Any simple CR summary includes every record, so if the same figure is repeated in more than one record, it gets included more than once in the summary.  For example, if you had an invoice header table with one record for each invoice, and a detail table with one record for each line item, and you had an invoice total field in the header table, a summary on that field would include the total once for every line item.

 The simplest solution is probably a running total.  Create a running total on the field in question and set it to be evaluated once per order, or whatever your "unit" is.  If you have a group on that field, you can use "On change of group".  Otherwise, assuming that the report is sorted by that field, you can use "On change of field".  The idea is to get CR to only include the repeated values once per "unit".  And if the total is for a group or some other subset of records, you'll need to use the "reset" option to start a new running total for each set of records.

 One possible problem with using a running total is that you won't have a final total until the last record has been read, so you can't, for example, use a running total to show the total at the start of a group, before the records in that group have been read.


 Also, FWIW, the Joins in your SQL look odd to me.

 You have "RIGHT OUTER JOIN RECEIVABLE INNER JOIN RECEIVABLE_LINE", with no ON in between to specify the columns to use for the RECEIVABLE join.

 Later you have "INNER JOIN CUSTOMER ON RECEIVABLE.CUSTOMER_ID = CUSTOMER.ID ON
 uniPoint_Live.dbo.PT_Equip_Maint.Equip_num = CUST_ORDER_LINE.PRICE_NOTE".  That's two, seemingly unrelated, ON phrases in a row.

 That's followed by "LEFT OUTER JOIN PURCHASE_ORDER INNER JOIN VENDOR".  Two Join's, with no ON for PURCHASE_ORDER.

 It may all make perfect sense and produce the correct results, but it seems like, if nothing else, it could be a lot clearer.

 James
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
mlmccCommented:
DO you have more than one data source?
That is not supported in Crystal unless you use a command.

mlmcc
0
Tina KSystems SpecialistAuthor Commented:
Yes, mlmcc, there are multiple datasources, and I am using a command.  The command sql is shown above.
I've been playing around with the joins, and they have to stay they way they are in order to return the correct records.  
I believe I have tried every possible combination of evaluate and reset on my running totals in crystal as well.  I can get all the running totals to display correctly for one record, but then the one below it is goofed up.  If I change the reset to a different parameter, then record number one doesn't total correctly.
It's super frustrating, but I think I have to just keep playing with it until I stumble upon the proper combination for all four of my running totals.
0
James0628Commented:
I can get all the running totals to display correctly for one record, but then the one below it is goofed up.
What do you mean by "record"?  I'm guessing that you're actually referring to some "set" of records (eg. for an order).  The running total "for one record" would just be the amount in that record, and the running total on the next record would be the total of those two records, or the amount in the second record, depending on your "reset" setting.

 It might help if you could post your actual report (the .rpt file), and some sample data.  If the data doesn't include any "sensitive" information, you could use the "save data with report" option in CR and post the report with the sample data included.

 James
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Crystal Reports

From novice to tech pro — start learning today.

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.