Summing data from multiple tables 1

[agorjest]

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?

 

[lbass]

If there is only one value per group, then insert a maximum on it, instead of a sum, at the group level. Or you can use a running total that evaluates on change of workorder and resets on change of group.

-LB

 

[agorjest]

lbass,
Thanks for your reply. Let me run another scenario by you:
Again 3 data tables, linked by common field, WO #.
Grouping by GL account # (a field in the Workorder table).
Workorder # 43729 has 3 labor transactions associated with it: one on 7/31/09 for 1 hour, one on 7/29/09 for 3 hours, and one on 7/30/09 for 2 hours. It also has 2 materials transactions associated with it, one for $43 on 7/31/09 and the other for $15 on 7/28/09. Here is how the data is displaying:

Group Name: GL Account # 129752101101
Details:
WO# LbrDate LbrHrs LbrCost Mat.Cost Mat.Trans.Date
43729 7/31/09 1.00 $10.00 $43.00 7/31/09
43729 7/31/09 1.00 $10.00 $15.00 7/28/09
43729 7/29/09 3.00 $30.00 $43.00 7/31/09
43729 7/29/09 3.00 $30.00 $15.00 7/28/09
43729 7/30/09 2.00 $20.00 $43.00 7/31/09
43729 7/30/09 2.00 $20.00 $15.00 7/28/09

It is displaying a record for each of the 3 labor transactions, and additional records duplicating the labor transaction but including the other materials transaction.

I only want to sum the values for the 3 distinct labor transactions and the 2 distinct materials transactions at the group level. I then need to add labor and material costs together and come up with a total cost by group, and then a grand total. Any suggestions?
Thanks again for your help...

 

[lbass]

First there should be some ID field to distinguish the transactions instead of just dates. If you only have dates, sort by the labor date and then by the materials date. Then do a running total sum of lbrcost, evaluate on change of labor date, reset at a specific group level (or never for the grand total). You need a separate running total for each report level. Then for the materials, do a running total sum of material cost, evaluate based on a formula:

{table.labordate} = maximum({table.labordate},{table.workorder})

This will only sum the materials for one of the labor dates. Again, you would reset in one running total on change of workorder, and for the grand dtotal, reset never.

-LB

 

[agorjest]

lbass,
I did find unique identifying fields in both the labor and materials transactions tables. The fields are named labtrans.rowstamp and mattrans.rowstamp. Here is how they display for each record:

Group Name: GL Account # 129752101101
Details:
WO# LbrDate LbrCost MatCost MatDate LabRowstmp MatRowstmp
43729 7/31/09 $10.00 $43.00 7/31/09 02063A3 0206351
43729 7/31/09 $10.00 $15.00 7/28/09 02063A3 0206353
43729 7/29/09 $30.00 $43.00 7/31/09 0206356 0206351
43729 7/29/09 $30.00 $15.00 7/28/09 0206356 0206353
43729 7/30/09 $20.00 $43.00 7/31/09 0206358 0206351
43729 7/30/09 $20.00 $15.00 7/28/09 0206358 0206353

How would you suggest sorting the data and summing the costs using these rowstamp fields?

 

[lbass]

Use the same method as suggested in my last post. Just sort on labrowstamp and then matrowstamp and use these fields instead of the dates for the running totals.

-LB

 

[agorjest]

That did the trick. Thanks lbass!

 

[agorjest]

lbass,
Another issue that came up with this report:
I have the following record selection formula to filter the records to those where either the labor date or the materials date falls between a start date and end date chosen by the user:
({labtrans.labordate} >= {?Enter Start Date} or {matusetrans.matdate} >= {?Enter Start Date}) and
({labtrans.labordate} <= {?Enter End Date} or {matusetrans.matdate} <= {?Enter End Date})

However,
Say the user chooses a start date of 8/1/09 and end date of 8/31/09. Here is an example of how the data displays:

WO# LbrDate LbrCost MatCost MatDate LabRowstmp MatRowstmp
43729 7/31/09 $10.00 $43.00 8/1/09 02063A3 0206351
43729 8/1/09 $10.00 $15.00 7/28/09 02063A3 0206353
43729 7/29/09 $30.00 $43.00 8/1/09 0206356 0206351
43729 8/1/09 $30.00 $15.00 7/28/09 0206356 0206353
43729 7/30/09 $20.00 $43.00 8/1/09 0206358 0206351
43729 8/1/09 $20.00 $15.00 7/28/09 0206358 0206353

Notice that labor hours and costs are displaying when the Materials Date is in August, even if the labor date is in July, and vice versa. If the labor transaction does not occur in the user-chosen date range, I do not want it to be added to the total labor hours and costs. Same thing with the materials costs if the materials transaction is outside of the date range.

Is there a way to eliminate the unwanted or duplicate records in data selection or table linking, or is my only option some kind of running total formula to exclude the unwanted values from the totals?

Thanks...

 

[lbass]

You could use conditional formulas for each field, like:

if {table.lbrdate} in {?StartDate} to {?EndDate} then {table.lbrcost}

You would do this for each field. Another alternative would be to start with a command as your datasource where you use a Union all statement to bring in both sets of data and limit the dates for each set separately. This should also eliminate the duplicate data. Not sure you want to start from scratch though.

-LB

 

[agorjest]

I went with the conditional fields for this report, thanks for the suggestion LB. Just out of curiosity, is there anything on the crystal side that one could do to filter out duplicate records in situations like this (i.e., data tbale linking or record selection formulas), or is this something where you have to get into SQL programming?

Thanks...

 

[lbass]

If you've linked appropriately, then there isn't much you can do other than filter as above. You can change the display through grouping, but for the purposes of calculation, eliminating dupes could require writing a SQL query (command, view, etc) or using SQL expressions, but this depends upon the specific situation. You can use subreports, too, but then you have to deal with slower speed and with the need to share data with the main report for calculations.

-LB

 

[agorjest]

LB,
I have running totals in place for each group level, as you suggested in the threads above, to add up labor and material costs and weed out the values of duplicate records. I also have summary formulas for the total cost for each group that simply add #LabCost and #MatCost together.
Now what I want to do is calculate the percentage of the total cost of each GL Account # (Group 2) as it relates to the total cost of the whole site (Group 1). I also want to the same for the grand total. The trick is that I need it to be on the same line as the GL Account #, and that is where I'm stuck. It wouldn't be a big deal if I could use the basic Insert Summary, but since these group totals are based on running totals that won't work. Any ideas?

GH1: Site FTW
GF2GL Acct.#) LabCost MatCost TtlCost %Site %Ttl
129752103101 $175.35 $133.38 $308.73 19% 0.3%
129752103102 $620.64 $284.00 $904.64 129752103104 $105.64 $0.00 $105.64
129752103105 $221.49 $0.00 $221.49
129752103107 $0.00 $82.55 $82.55
GF1 FTW Totals $1123.12 $499.93 $1,623.05

GH1: Site MAD
GF2GL Acct.#) LabCost MatCost TtlCost %Site %Ttl
129752101101 $25,250.40 $1,256.35 $26,506.75
129752101102 $7,001.20 $9,162.51 $16,163.71
129752101103 $477.09 $1,362.34 $1,839.43
129752101104 $1,188.46 $14,065.48 $15,253.94
129752101105 $4,057.71 $0.00 $4,057.71
129752101106 $0.00 $4,921.50 $4,921.50
129752101107 $1,810.23 $18,400.00 $20,210.23
129752101108 $0.00 $441.18 $441.18
129752101109 $10,341.45 $0.00 $10,341.45
129752101305 $140.86 $0.00 $140.86
GF1 MAD Totals $50,267.38 $49,609.36 $99,876.74

Gr.1: Site WAV
GF2GL Acct.#) LabCost MatCost TtlCost %Site %Ttl
129752102101 $7,289.55 $30.00 $7,319.55
129752102102 $1,515.24 $785.84 $2,301.08
129752102103 $144.24 $0.00 $144.24
129752102107 $3,555.30 $0.00 $3,555.30
129752102305 $27.95 $0.00 $27.95
GF1 WAV Totals $12,532.28 $815.84 $13,348.12

Grand Ttl: $114,847.91

Thanks!

 

[lbass]

Since the total is based on a running total calculated after the group where you want to reference it, I think you have to insert a subreport that does the running total calculation, set it to a shared variable, which you can then reference in the main report. The sub would have to be linked to the GH#1 field and placed in GH#1, for the group #2 calculations. For the calculations based on the grand total, you probably need another sub in the report header, unlinked.

-LB

 

[agorjest]

LB,
I'm confused about placing the sub-report in GH#1 and the other sub-report in RH. Again, my goal is to have both of these percentages appear on the same line as the group 2 (GL Account #) totals. Also, can you be more specific about the shared variable (i.e., what the variable formula should read, where it should be placed, etc.).
I appreciate your help. Thanks....

 

[lbass]

For the Site %, you need the total per site, which you would get from the sub in GH#1. You would save your current report as a subreport, and then set the group #1 running total to a variable, as in the following which should be placed in the group footer #1 of the subreport. The sub must be linked on the group #1 field:

whileprintingrecords;
shared currencyvar grp1tot := {#grp1rt};

Place a reset formula in GF#1 of the MAIN report:

whileprintingrecords;
shared currencyvar grp1tot := 0;

In GF#2, do your percentage calculation:
whileprintingrecords;
shared currencyvar grp1tot;
#grp2rt%grp1tot

For the grand total, place the sub in the report header, and place a shared variable formula in the subreport footer. Do not link it. Then reference the variable in a main report formula in the group #2 footer.

-LB

 

[agorjest]

I don't have alot of experience with subreports, so forgive me if this is a stupid question.
I "saved as" the report under a different name and I'm inserting it into the main report as a subreport. In the Subreport Links dialog box, I am choosing the Group 1 Total Cost field @Total Cost by Site (which is actually a formula of ({#LabCost}, {Site}) + {#MatCost}, {Site})) as the only field to link to. If I check the box "Select Data in Subreport Based on Field", then I get a Group Selection error in the subreport saying the formula can't be used because it must be evaluated later.

Am I linking the correct field? Do I have to link the parameter fields ({?StartDate}, {EndDate}), or any other record-selection fields from the original report?

Thanks, and again sorry if I'm over-looking something obvious.

 

[lbass]

Link on the Group #1 field (Site), not the group name. For both reports, you should also link the parameters to each other, by using the dropdown in the lower left corner of the linking screen to select {?StartDate} instead of the default {?pm-?StartDate}. Repeat for {?EndDate}. This way you won't have multiple date prompts.

-LB

 

[agorjest]

LB,
Thanks for all your help with the subreport issue.
I'm still working on that, but I noticed today that I'm having a problem with record selection.
I'm not getting any data to return for records that come from the materials transaction table that don't have a labor transaction table record with the same workorder number.
In other words, records like this will show:
WO# LbrDate LbrCost MatCost MatDate LabRowstmp MatRowstmp
43728 7/30/09 $20.00 02063A3
43729 7/31/09 $10.00 $43.00 8/1/09 02063A3 0206351

But records like this won't:
WO# LbrDate LbrCost MatCost MatDate LabRowstmp MatRowstmp
43730 $65.00 8/3/09 0206352

I started off with single parameters for a start date and an end date with the record selection formula:

({labtrans.labordate} >= {?Enter Start Date} or {mattrans.transdate} >= {?Enter Start Date}) and
({labtrans.labordate} <= {?Enter End Date} or {mattrans.transdate} <= {?Enter End Date})

Then I switched to a date range parameter with the formula:
{labtrans.labordate} = {?Date Range} or
{matusetrans.transdate} = {?Date Range}

In both cases, I'm getting only labor records, or labor records and materials records that share the same Workorder #. I know there are workorders with stand-alone materials transactions records in the database that don't have labor records, but they are not making it past record selection.

I have tinkered with linking the 3 tables, but even with full outer join the stand-alone materials records still aren't showing. I'm pretty sure it's the record selection formula that I don't have right. Any ideas?

Thanks again for all your help, you have been a godsend with this complicated report I'm working on.

 

[agorjest]

LB,
I have re-designed this report so that the 3 tables (workorder, labor trans., materials trans.) are merged via a SQL command, and that seems to be working well. But now I'm running into some odd results when trying to calculate the % of cost for a GL account (group #2) as it relates to the total cost of the site (group #1). As you suggested in the previous posts, I have set up a sub-report and linked it to the main report by the group #1 field (@site) and start and end date parameters. Here is an example of how the data for one of the sites looks (details and group #3 Workorder # have been suppressed):

Group #1 Site: FTW
Group #2 GL Account #:
GL Acct# TtlLabor$ TtlMat$ TtlActualCost %Site Ttl
103101 $62.63 $0 $62.63 1%
103102 $389.55 $1411.21 $1800.76 18%
103104 $39.62 $0 $39.62 0%
103105 $110.75 $0 $110.75 1%
Site Ttl: $602.53 $1411.21 $2013.74 -

The 2nd account should be more like 90% of the site total, and the others are slightly off too.
All of the currency figures above are from running totals evaluating on change of each record and resetting at each group level. I have tested all of them and they are calculating correctly at each group level. The sub-report has been inserted in GH1 of the main report and variables were placed as follows:

GF1 of subreport:
whileprintingrecords;
shared currencyvar grp1tot := {#Actual Total Cost Site};

GF1 of main report:
whileprintingrecords;
shared currencyvar grp1tot := 0;

GF2 of main report (%Site Ttl column in above example):
whileprintingrecords;
shared currencyvar grp1tot;
if grp1tot = 0 then 0 else {#Actual Total Cost GL Account } % grp1tot

Any ideas of what I might be doing wrong?

Thanks in advance...

 

[lbass]

If you used a union all statement there should be no need to use subreports. If you added a string field to identify which table the data is coming from, you can then use conditional formulas to restrict amounts to certain tables. I think I would have to see your command to be able to help any further.

-LB