Troubleshooting Unstable Report that Doubles Values

I need help troubleshooting a report that is driving me crazy. It reports out values that I sum using formulas like the ones below.

Formula 1
WhilePrintingRecords;
NumberVar CONCreditor;

If {PTLA_VLP_OutcomesCW.CONNoCreditorHarass} = 1
    Then CONCreditor := 1
Else CONCreditor := 0

Open in new window


Formula 2:
WhilePrintingRecords;
NumberVar CONCreditor;
NumberVar CONCreditorCount;

CONCreditorCount := CONCreditorCount + CONCreditor

Open in new window


Formula 3:
WhilePrintingRecords;
NumberVar CONCreditorCount

Open in new window


The report is grouped by FileID. There may or may not be a related Outcomes record. PTLA_VLP_OutcomesCW is a view that is joined by FileID to the Files table with an outer join. I need to be able to count the number of files that don't have an Outcomes record.

CONCreditor reset to 0 in the FileID group header. Formulas 1 & 2 are in the FileID group footer. Formula 3 is in the report footer.

The problem is that sometimes the report doubles every displayed value. Generally, if you run the report again, it fixes itself. We'd learned to live with the problem by running this report twice every time we ran it. Then something really strange happened. Just the first page of the report was correct; the 4 pages after it were all doubled.

I cannot figure out what causes this doubling to happen. Any ideas where I should look?
chenegarAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

mlmccCommented:
Try this

Change formula 2 to

EvaluateAFter({@Formula 1});
NumberVar CONCreditor;
NumberVar CONCreditorCount;

CONCreditorCount := CONCreditorCount + CONCreditor

Open in new window


mlmcc
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
James0628Commented:
This may not have anything to do with your count problem (using EvaluateAfter, as mlmcc suggested, will probably help with that), but FWIW ...

 You said "There may or may not be a related Outcomes record".  If you're referring to PTLA_VLP_OutcomesCW, then that presumably means that CONNoCreditorHarass could be null (when there is no record).  If that's the case, then you need to handle that.  One way would be to test the field for nulls in the formula:

If not IsNull ({PTLA_VLP_OutcomesCW.CONNoCreditorHarass}) and
 {PTLA_VLP_OutcomesCW.CONNoCreditorHarass} = 1


 James
0
chenegarAuthor Commented:
I don't know when I'll know for sure that your suggestions will solve the problem (because it occurs so sporadically), but using EvaluateAfter and checking for nulls makes sense to me. Thanks!
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Crystal Reports

From novice to tech pro — start learning today.