How to summarize data from 3 tables on one report

[phartman]

I am running Crystal v9
Oracle database
I have 3 tables
The job table will have one record and contain the selling price.
The labor ticket table can contain many records, one for each person/task/day, etc.
The material table can contain many records, one for each piece of material used to repair the item.
I would like to do a report that shows
Job # Selling Price Labor Cost(plus markup %) #hours charged to job Material Cost(plus markup %)
I can do formula to properly calculate the markup on the labor and the material, and I can do a running total that accurately totals both of these if I show one or the other on the report. But if I try to show both labor cost and material the records are duplicated and my totals are too high.
How do I accomplish this?

 

[kchaudhry]

How are these 3 tables linked? What is the field you are linking these tables on? What type of link are you using for these tables?

Please provide this information.

Kchaudhry

 

[phartman]

The tables are linked by the job# that is in all three tables with an inner join link.

 

[jwestmore]

Your problem here is a cartesian product

If you join all 3 tables on job id and you have multiple records in the two source tables, every record gets joined with every other record.

For example:
Let's say you have a job that required two people and two materiel types all linked by job id. You would end up with the following recordset:

job 1, person 1, materiel 1
job 1, person 2, materiel 1
job 1, person 1, materiel 2
job 1, person 2, materiel 2

The method to fix a problem like this is to use Crystal sub reports. One subreport for your people, and one for your materiel, both linking back to the main report on job id.

You end up with a result like this

main report: job id
subreport 1: person 1
person 2
subreport 2: materiel 1
materiel 2

You can place the subreports in your detail line side-by-side and you end up with a nice result.

Hope this helps.
Jon

 

[synapsevampire]

You have options other than subreports, which have poor performance.

If you have an Oracle programmer on staff, ask them to create the SQL or a View/Stored Procedure as the data source.

The SQL would be something like:

Select person, task, day,
(select sum(materialcost) from material where material.jobid = J.jobid) MaterialSum,
(select sum(laborcost) from labor where labor.jobid = J.jobid) LaborSum
from JobTable J
group by
JobID

SQL can be pasted in as the data source in CR 9 or above.

You can also use SQL Expressions to do this.

Subreports will also work, but they aren't efficient and require more coding.

-k

 

[lbass]

You could also refine your running totals to make your calculations accurate. For assistance with this approach, you would need to provide sample data that showed one or two groups worth of data, including detail level data.

-LB

 

[phartman]

Thank you to everyone for your input. I do not have a Oracle programmer on staff (I am the entire IT department), but I think SQL is definitely something I want to learn.
I would like some assistance on refining the running totals. I'll try to provide enough sample data for you, if you need more, just let me know.

I have a MOC table with 1 record per Job/Reference #
Job Ref# Price Part_NO
103124 1 1345.00 JC/680/010/17.75 x 54 P350
103124 2 2222.00 JC/680/020/17.75 x 54 P350
103124 3 3333.00 JC/680/030/17.75 x 54 P350
103125 1 500.00 JN/230/000/12345

The LX Table is the labor transactions. It is linked to the MOC table by JOB and ref#
Job Ref# Hours Wc-Key
103124 1 .50 350
103124 1 1.26 360
103124 1 2.38 210
103124 1 4.54 220
103124 1 7.10 350
103124 1 1.15 370
103124 2 .76 350
103124 2 1.33 287
103125 1 .50 288

The WC-table is linked to the LX table by the WC-key and is used to calculate the labor cost (hours * wc-rate)
WC-key WC-rate
350 75.00
360 85.00
370 110.00
287 55.00
288 65.00
210 90.00
220 95.00

The RQ table has the material issued to the job. it is linked to the MOC table by the job and ref #. WE only want the rq records that have a type "M", "T", "F", or "S".
The type "M" and "S" need to be marked up by 1.15%.
Job Ref # Type Dollars
103124 1 M 953.40
103124 1 M 1906.80
103124 1 M 81.82
103124 1 M 57.50
103124 2 M 120.22
103125 1 M 522.54

I want to be able to enter a job # and get a report with the following information. I would like 1 line per ref #.
The cost on the report would be a total of the labor cost
(Hours * wc-rate) + Material (plus markup)
Ref # Part No Price Cost Hours
1 JC/680/010/17.75 x 54 P350 5345.00 4486.47 16.93
2 JC/680/020/17.75 x 54 P350 200.00 251.76 2.09

Let me know if you need additional information.

 

[lbass]

What I meant earlier by sample of data was a snapshot of the detail level data for one or two groups, for example:

GH1 Job
GH2 Ref #
Detail - Hours WC-Rate Type Dollars //etc.
(detail data here)
GH2
GH1

If this is too complicated, then maybe you can troubleshoot this yourself by looking at the detail section and observing how the data repeats. You would then use the evaluation based on a formula section and/or the reset function to eliminate duplicate data. If for example, you saw a pattern like:
ID type amt rt
1 A 1 1
1 A 2 3
1 A 3 6
1 B 1 1
1 B 2 3
1 B 3 6

And the correct value for the sum of {table.amt} per ID was really "6", not "12", then you would reset the running total (shown above) on change of type, even though you were looking for the value for the ID. In the group footer for ID, the rt would read "6".

-LB