Many to One - summing one detail only

[Jcfx]

Hello, Using crystal 11 on a sql db.

I have to compare workorder to invoice details side by side by line type. ie Parts, Labor, Fees, Discounts. This information is stored in the same table defined by ordertype, so I had to alias table. Order_Invoice linked to Order_Work on Order_invoice_refid to Order_Work_id.

Every line type has a one to one relationship except for labor in the details no matter if it is a workorder or a invoice. The bugger is that for the labor, there can be multiple labor lines (one for each personnel)on a workorder but only one labor line on the invoice. ie in the workorder you would have three labor lines of 1 hour each. In the invoice one labor line of 3 hours.

Because of this, when I try to do a side by side comparison, I end up having duplicate details. I cannot sum/group by linetype (which would make it easier) because the client wants to see every part detail and every discount. I also am required to format this for excel export only.

Right now this is what I have:

Line WODesc WOqty WOCost WoTotal InvDes Invqty InvPrice InvTot
Part Light 3 .75 2.25 Light 3 4.25 12.75
Part Light 1 .10 .10 Light 1 .25 .25
Labor PM 1 25.00 25.00 PM 3 100.00 300.00
Labor PM 1 25.00 25.00 PM 3 100.00 300.00
Labor PM 1 25.00 25.00 PM 3 100.00 300.00


Then the part lines duplicate again for every additional labor line (

I need the result to be:

Part Light 3 .75 2.25 Light 3 4.25 12.75
Part Light 1 .10 .10 Light 1 .25 .25
Labor PM 3 25.00 75.00 PM 3 100.00 300.00

so that the customer can do a one on one profit/loss comparison in excel.
I need to sum/group the labor line type in the workorder, so it matches the invoice but the other line types must remain in detail. How can I do this?

Thank you



Julie
CRXI CE10 / RS2005 Sql DB

 

[IanWaterman]

Go to

File -> Report Options

Check - Select Distinct Records

Ian

 

[Jcfx]

I have already done this. I'll attach a sample so that you can see what I have already. I am concentrating on the detail level. On the left you see the workorder information as you can see I have multiple labor lines for one labor line on the invoice level. I MUST keep the part detail. I cannot sum/group the labor and plop it into a group header and compare, because I'll then lose the individual part information. Unless I am missing something.

Julie
CRXI CE10 / RS2005 Sql DB

 

[Jcfx]

bah, for the link please replace the '+' with spaces.

shop productivity report.pdf. Thanks

Julie
CRXI CE10 / RS2005 Sql DB

 

[Jcfx]

As Stated earlier, Select Distinct is already checked. With the report sample provided, are there any other possible solutions?

Thank you

Julie
CRXI CE10 / RS2005 Sql DB

 

[kray4660]

I looked at the sample, and did not see any duplication?!? Is there an unique ID associated with each detail line. If so, you can group by that ID, suppress the GH and detail lines, and put the 'details' in the GF.

I hope this helps.

 

[lbass]

Create a formula like this:

//{@Laborgrp}:
if {table.line} = "Labor" then
{table.line} else
""

Insert a group on this and place a copy of the detail fields into the group header. Then go into the section expert->group header->suppress->x+2 and enter:

{@Laborgrp} = ""

Also suppress the group footer.

If you align fields in the group header and detail section properly, this should export fine.

-LB