Summarizing a calculated field - Where's the last record?

[syarbrough]

I am trying to create a summary of a field that has a calculation on it. The problem is; as soon as I add the Formula Field to the Report, the report does not show one (the last?) record, which is a problem. Here are the formulas that I am trying to use:

WhilePrintingRecords;
CurrencyVar Runtotal;
if {PayrollWorkersComp.JobClassID} = "8810" then Runtotal:= Runtotal + {PayrollWorkersComp.CompanyAmount} * 0.85287356321839080459770114942529
else
if {PayrollWorkersComp.JobClassID} = "8742" then Runtotal:= Runtotal + {PayrollWorkersComp.CompanyAmount} * 0.76814516129032258064516129032258
else
if {PayrollWorkersComp.JobClassID} = "8393" then Runtotal:= Runtotal + {PayrollWorkersComp.CompanyAmount} * 0.50345423143350604490500863557858
else
if {PayrollWorkersComp.JobClassID} = "8380" then Runtotal:= Runtotal + {PayrollWorkersComp.CompanyAmount} * 0.42869269949066213921901528013582
else
0

or simply:

sum ({@WC})

I am new to CR, so be gentle.

Cheers,

Stan

 

[lbass]

Can't see any reason why you would lose the last record. Are you using more than one table? If you have an equal join and the formula contains a field that is from an otherwise unused table, you would see the effects of the join only at that time. If this is the case, change the join to a left join from the initial table to the table referenced in your formula.

What version of CR are you using? I once had problems with 8.0 when using multiple detail sections with "Underlay following sections" when there were page breaks. But this was a display issue only. If you insert a count on your records, is the count actually changing when you add the formula?

If you use your formula above, you would need a separate display formula for the report footer:

whileprintingrecords;
currencyvar Runtotal;

If you don't need to display the running total as it accumulates, you could instead create a formula like the following:

if {PayrollWorkersComp.JobClassID} = "8810" then {PayrollWorkersComp.CompanyAmount} * 0.85287356321839080459770114942529
else
if {PayrollWorkersComp.JobClassID} = "8742" then{PayrollWorkersComp.CompanyAmount} * 0.76814516129032258064516129032258
else
if {PayrollWorkersComp.JobClassID} = "8393" {PayrollWorkersComp.CompanyAmount} * 0.50345423143350604490500863557858
else
if {PayrollWorkersComp.JobClassID} = "8380" then {PayrollWorkersComp.CompanyAmount} * 0.42869269949066213921901528013582
else 0

Then you could add the formula to the detail section, right click on it and insert a summary at any group levels and/or a grand total. When you can insert a summary, it is best to do that as running totals can slow a report.

-LB

 

[syarbrough]

lbass:

Thanks for the quick reply.

There is a formula in the detail with those calculations, and I created a summary outside the detail section, but I started having this problem. If I remove the Sum(@admin) from the page header, the record shows up. I figured it had to do with the second-pass situation.

I am starting to think that the problem has something to do with the Join method, as you pointed out, but I have tried multiple join scenarios, but none see to eliminate the problem.

I have three tables; REGISTER, which has most of the detail data, COMP, which has most of the summarized and data needing the calculations, and a COMPANY DETAIL table with addresses, phone, etc.

Currently, I have a inner join from both COMP and COMPANY DETAIL to REGISTER, because the records much match the fields in REGISTER.

<======= Company detail
Register(contains detail)
<======= Comp

 

[lbass]

You are joining the tables on some field(s), probably an ID field. If Register contains all the IDs you want to appear on your report, and the other tables contain some of those IDs, then use a left join FROM Register to each of the other tables.

-LB

 

[syarbrough]

lbass:

Thanks a bunch. I have your join set up, but still having the same problem. The thing is; if it were a join problem, then all records would not appear under any circumstance. Instead, this is in the circumstance that I add a field to the detail from the COMP table. Therein is the disconnect. Why would adding a field from a joined table exclude the last (or any) record?

Stan

 

[syarbrough]

Never mind...

I went back and compared the data in the tables, and I have a data issue. Turns out that the missing record did not have an explicit match in the joined table.

DOH.

Like I said, I am new to this CR stuff.

Cheers,

Stan

 

[lbass]

Left joins are used for that very purpose. When the second table doesn't have a match to the field in the first table, if you use a left join FROM the first table to the second table, the field in the first table will still appear on the report--as long as you don't place selection criteria on the second table.

-LB

 

[syarbrough]

Thanks!

I am now rocking!

Cheers,

Stan