Grand total of summarized formula

[kwirky]

Hi,
I have created a report which contains duplicated data as a result of multiple enties in a journal table. These are created when the user updates the status of a job.
In order to give me totals by group and exclude the duplicate values, I needed to create formulas.

@TotalSheets
Sum ({PaperEntries.TotalSheets}, {MainJobDetails.JobNo})/DistinctCount ({Journals.ID}, {MainJobDetails.JobNo})

This works fine and all the group totals are correct, however the problem is in the GrandTotal. I cannot use these formulas to create a grand total in the 'normal' way and when I simply total the field {PaperEntries.TotalSheets} I get all the various dulicated values.

Sample of data in the report:

Journals.ID PaperEntries.TotalSheets
5334427 5,700
5334427 1,900
5334427 1,875
5338661 5,700
5338661 1,900
5338661 1,875

My formula is adding the first 3 records, totalling 9,475, which is correct. The grand total however is adding all 6 values to give me 18,950.

So I have 1 group {MainJobDetails.JobNo} and in the group header for this, I have my formulas with the group total. My details are suppressed so that you cannot see the duplication.

I am using CR2008

Any help would be appreciated.

 

[lbass]

You can use a variable to collect the result, but the grand total would have to be placed in the report footer:

//{@accum} to be placed in the job group section and suppressed:
whileprintgrecords;
numbervar sumx := sumx + {@TotalSheets};

//{@display} for the report footer:
whileprintgrecords;
numbervar sumx;

-LB

 

[kwirky]

Holy sh%t! It worked!!!

That is simply brilliant.

Thank you so much!!!

 

[Madawc]

You could also save time by letting Crystal generate your totals wherever possible. If you're not already familiar with Crystal's automated totals, see FAQ767-6524.

Madawc Williams (East Anglia, UK). Using Crystal 11.5 with SQL and Windows XP