Running Total Formals changing the value of another Running Total Form 1

[jorgenm]

Hi Group
This is my first time using Tek-Tips.
I am using Crystal 8.5 using the tables on ODBC. The report I have created has 3 fields of running total formulas. Each running total formula should stand alone. I have criteria in each formula. The problem I am having is when I insert the last Running total formula in the report, it changes the values of the other running total fields. What am I doing wrong? Why does this happen
Examples:
the way I want it to work
2003 Actuals 2004 budget 2005 budget
761,000 800,000 850,000

The results I am getting
2003 budget 2004 budget 2005 budget
9,343,200,000 3,434,000,000 850,000
Any help you can give would be appreciated.
Thanks

 

[Madawc]

Try posting your criteria. Somehow you must be changing something, but without details it is hard to figure what.

Madawc Williams
East Anglia, Great Britain

 

[lbass]

Are you using the running total editor? What is your table structure? If the third formula is activating an additional table, it could be causing duplicate records that are being counted in your earlier running totals.

The best way to get help will be for you to post some detail level data that includes one or two sets of group level results so that we can see how you need to reset the running totals to get the correct results.

-LB

 

[jorgenm]

Thanks Madawc and lbass for responding. Here are more of details of my report
The relationships are:
tblESXACCTR left outer join to ESGBUDGD fields bud_year, acct_id and acct_type
tblESXACCTR left outer join to ESGABALD fields acct_year, acct_id and acct_type
tblESXACCTR left outer join to ESBDTPD fields bud_year, and acct_id
tblESXACCTR left outer join to ESGACTTR fields acct_year, and acct_id
tblESXACCTR left outer join to ESBUDPD fields bud_year, and acct_id

The 3 running totals formulas are
(1) FY02-03 Actuals
Initialzing (hidden)
WhilePrintingRecords;
numberVar RunningTotal;
RunningTotal := 0

Evaulating (hidden)
WhilePrintingRecords;
numberVar RunningTotal;
If {ESGABALD.ACCT_YEAR}=2003
and {ESGABALD.PERIOD} in 1 to 12
and {ESGABALD.ACCT_TYPE} = "E"
Then RunningTotal := RunningTotal + {@DebitsLessCredits}
else RunningTotal := RunningTotal

Displaying
WhilePrintingRecords;
numberVar RunningTotal;
RunningTotal;

{@DebitsLessCredits} formula
{ESGABALD.DEBITS} - {ESGABALD.CREDITS}

(2) FY03-04 Adjusted Budget
Initialzing (hidden)
WhilePrintingRecords;
numberVar MJRunningTotal;
MJRunningTotal := 0

Evaulating (hidden)
WhilePrintingRecords;
numberVar MJRunningTotal;
IF {ESGBUDGD.BUD_YEAR} = 2004 and{ESGBUDGD.ACCT_TYPE} = "E"
then MJRunningTotal:= MJRunningTotal + {@cybudget}
else MJRunningTotal:= MJRunningTotal

Displaying
WhilePrintingRecords;
numberVar MJRunningTotal;
MJRunningTotal;

{@cybudget}formula
{ESGBUDGD.BUD_AMOUNT} + {ESGBUDGD.BUD_ADJ}

(3) FY03-04 YTD Expenditures
Initialzing (hidden)
WhilePrintingRecords;
numberVar RunningTotalytd;
RunningTotalytd := 0

Evaulating (hidden)
WhilePrintingRecords;
numberVar RunningTotalytd;
if {ESGABALD.ACCT_YEAR}=2004
and {ESGABALD.PERIOD} in 1 to 12
and {ESGABALD.ACCT_TYPE} = "E"
Then RunningTotalytd := RunningTotalytd + {@DebitsLessCredits}
else RunningTotalytd := RunningTotalytd

{@DebitsLessCredits} formula
{ESGABALD.DEBITS} - {ESGABALD.CREDITS}

When I do not included the (2) FY03-04 Adjusted Budget running total in the report my totals for (1) FY02-03 Actuals and (3) FY03-04 YTD Expenditures calculate correctly. If I include the (2) FY03-04 Adjusted Budget running total in the report it recalucates the other 2 running totals incorrectly.
Example of results:
without the FY03-04 Adjusted Budget running total included in report
FY02-03 Actuals = 76203.15
FY03-04 Adjusted Budget = 0.00
FY03-04 YTD Expenditures = 586305.14
This is correct amounts with the excetion of FY 03-04 Adjusted Budget.

with the FY03-04 Adjusted Budget running total included in report
FY02-03 Actuals = 9134437.80
FY03-04 Adjusted Budget = 7424550.00
FY03-04 YTD Expenditures = 5276746.26
These amounts are incorrect. It is as if I am missing a criteria or that is is duplicating something.

Any help you can give me would be appreciated.
Thanks
Monica Jorgenson

 

[lbass]

You either need to post actual detail records so that we can SEE how values are repeating, or you could troubleshoot this yourself by displaying the detail section and observing where your running totals are adding duplicate values, and then adding criteria to the formulas to eliminate duplicate counts/sums.

-LB

 

[jorgenm]

Thanks Ibass for your response. I tried looking at the detail. What I noticed was that the running totals was multipling the answer by the number of periods. For example 761203.15 * 12=9134437.80. How do I get the formula not to do this? I thought that the Initialzing would stop this. Any suggestions?
Thanks

 

[lbass]

It's a little hard to tell since you are using complex manual running totals, but you might try creating an inner group on {ESGABALD.PERIOD} and then placing your reset formula(s) there. You really need to observe the pattern of results in the detail section though to be sure. Here's the theory:

If you are getting repeats due to table linking 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
Total = 6

2 A 5 5
2 A 7 12
2 A 4 16
2 B 5 5
2 B 7 12
2 B 4 16
Total = 16

...then to get the correct totals using a running total you would reset on change of type, even though the values would repeat within the ID group, as I've shown to the right in the example above. In this example, you could group on {table.type} and insert a reset in the GH for type to get the correct value in the GF for ID.

-LB