Hello,
If anyone can help me with this I'd appreciate it:
Using CR 9, creating a report that tracks mechanical service workorders and totals their costs in terms of labor and parts/materials. It will draw data from 3 data tables: Workorders, Labor Transactions, and Materials Transactions. All 3 tables can be linked on a common field (Workorder #) that contains a 5 digit string that serves as the workorder ID number. There can be anywhere from 1 to hundreds of labor transactions for each workorder number, but for that same workorder there may only be 1 or 2 materials transactions. The problem I have is that CR pulls the records for each labor transaction and if there is only 1 materials transaction, that value will be repeated for each record. Here is how the records are displaying:
Group Name: WO # 10009
Details:
WO # Worker LaborDate LbrHours LbrRate LbrCost Mat.cost
10009 J.Smith 8/1/09 2.00 $10.00 $20.00 $100.00
10009 M.Jones 8/2/09 1.00 $10.00 $10.00 $100.00*
Group Name: WO # 10100
Details:
10100 B.Lee 8/4/09 0.50 $12.00 $6.00 $50.00
10100 M.Wilson 8/6/09 3.00 $15.00 $45.00 $50.00*
10100 M.Jones 8/7/09 1.00 $10.00 $10.00 $50.00*
Where you see the asterisks* are where the values are repeating where I don't want them to repeat. There should just be one value for Materials Costs, so when the Materials Costs are summed, it will total $100 for the first group and $50.00 for the 2nd group.
Note, the WO# field is taken from the Workorder table, the Materials Cost field is taken from the Materials Transactions table, and all the other fields are taken from the Labor Transactions table.
I've tried tinkering with linking the tables, changing around groups, and doing variable formulas, with no luck. It is always going to print each labor transaction record (as it should, since each record contains labor hours that need to be summed). But the material transactions are usually a one time transaction, and it's the cost of that one value that needs to be summed for each workorder. But the repeated values in the Materials Cost field are being added to the summed total, so I need to find a way to suppress them or prevent them from displaying and being included in the summed totals. Any ideas?
If anyone can help me with this I'd appreciate it:
Using CR 9, creating a report that tracks mechanical service workorders and totals their costs in terms of labor and parts/materials. It will draw data from 3 data tables: Workorders, Labor Transactions, and Materials Transactions. All 3 tables can be linked on a common field (Workorder #) that contains a 5 digit string that serves as the workorder ID number. There can be anywhere from 1 to hundreds of labor transactions for each workorder number, but for that same workorder there may only be 1 or 2 materials transactions. The problem I have is that CR pulls the records for each labor transaction and if there is only 1 materials transaction, that value will be repeated for each record. Here is how the records are displaying:
Group Name: WO # 10009
Details:
WO # Worker LaborDate LbrHours LbrRate LbrCost Mat.cost
10009 J.Smith 8/1/09 2.00 $10.00 $20.00 $100.00
10009 M.Jones 8/2/09 1.00 $10.00 $10.00 $100.00*
Group Name: WO # 10100
Details:
10100 B.Lee 8/4/09 0.50 $12.00 $6.00 $50.00
10100 M.Wilson 8/6/09 3.00 $15.00 $45.00 $50.00*
10100 M.Jones 8/7/09 1.00 $10.00 $10.00 $50.00*
Where you see the asterisks* are where the values are repeating where I don't want them to repeat. There should just be one value for Materials Costs, so when the Materials Costs are summed, it will total $100 for the first group and $50.00 for the 2nd group.
Note, the WO# field is taken from the Workorder table, the Materials Cost field is taken from the Materials Transactions table, and all the other fields are taken from the Labor Transactions table.
I've tried tinkering with linking the tables, changing around groups, and doing variable formulas, with no luck. It is always going to print each labor transaction record (as it should, since each record contains labor hours that need to be summed). But the material transactions are usually a one time transaction, and it's the cost of that one value that needs to be summed for each workorder. But the repeated values in the Materials Cost field are being added to the summed total, so I need to find a way to suppress them or prevent them from displaying and being included in the summed totals. Any ideas?