Round SubTotal 1

[dunkyn]

What is the best way to round a subtotal?

I am not displaying the $.00 (cents) and the subtotals and grandtotal are sometimes off by $1. I want to round the subtotals, so they will always agree with the grand total.

TIA

 

[Madawc]

The following will work in Crystal 8.5
Right click on the subtotal, chose [Format Field] and [Customise]. Set decimals and rounding.

Rounded totals can't agree with a grand total, i.e. 3.33 and 4.44 will round to 3 and 4 and add up to a rounded total of 8 from 7.77. You can truncate, which looks neater even though it is less accurate.

Madawc Williams
East Anglia
Great Britain

 

[dgillz]

Round({YourField},0) will round to the nearest dollar. Then total the formula field rather than the database field, and you will not have this problem.

Software Sales, Training, Implementation and Support for Exact Macola, eSynergy, and Crystal Reports

http://www.trianglepartners.com

dgilsdorf@trianglepartners.com

 

[dunkyn]

I like that approach, but I am trying to get my figures to match totals in another (companion) report from another source.

If I round each amount, the grand total overstated.

I am able to round each subtotal, but Crystal won't let me create a formula to total the subtotal

i.e.

=Sum({@roundsubtotal})

gives me an error message

"the summary running/total field could not be created"

 

[Ngolem]

you must do it manually.

Rounding issues are a constant problem. I once did an oil/gas revenue program where the numbers were in the millions with 2 decimal points and I had the task to be correct to $0.01 when the figures were added 2 different ways...I was out by $0.05 on $10 million once through rounding and that was not good enough for the accountants

The first thing you do not do is use the number rounding for display purposes and I think that applies to the report options....Why??? because that IS only for display...Crystal ignores it when it does the math.

So The problem comes with adding a column of numbers since YOUR math will be hand checked by someone until they trust the report.

You are not displaying the cents ($0.00) BUT [/]not rounding these since it will affect (quite rightly) the sub totals. I would perhaps advise against this since when they check the addition it will be a constant problem defending your addition.

I would show the columns of numbers (Rounded to 2 decimals if there are more decimals involved) as well as the Rounded subtotal....once you demonstrate that YOUR MATH IS CORRECT to your client....then you can still leave the original rounding on but have the displayed rounding changed to zero decimals..although they won't seem add properly now :-(

I would do the SubTotals AND The GrandTotal as manual sums

**********
@Initialize Grandtotal (Placed suppressed in Report or Group 1 header (if repeated))

WhilePrintingRecords;
If not inrepeatedGroupHeader then
NumberVar Grandtotal := 0;

**********
@Initialize Subtotal (placed suppressed Group header being totaled)

WhilePrintingRecords;
If not inrepeatedGroupHeader then
NumberVar Subtotal := 0;

**********
@CalcSubtotal (suppressed in the detail section)

WhilePrintingRecords;
NumberVar Subtotal ;

SubTotal := SubTotal + round({Table.value},2);

**********
@Display Subtotal/calc Grand Total (placed in footer of sum)

WhilePrintingRecords;
NumberVar Subtotal ;
NumberVar Grandtotal ;

GrandTotal := Grandtotal + round(SubTotal,0);

Round(Subtotal,0); //you could use a string and
//display before and after rounding here

**********
@Display Grand Total (in the report or other footer)

WhilePrintingRecords;
NumberVar Grandtotal ;

Grandtotal ;


This should make things right




Jim Broadbent

 

[dunkyn]

Thank you, Ngolem. You are a genius.

The subtotals look great, but for the grand total, which I have tried in both the group footer and the report footer, I am only picking up the value of the last group subtotal, and not the grand total of all group subtotals.

Any thoughts as to why?

 

[Ngolem]

hmmm...If you are only getting the last subtotal then either the Grand Total is being reset somehow - (check the positioning of the initialization of the GrandTotal)

Or

It is not being added.

I would put an additional display of the Grand total along side the Subtotal to see what was going on

Jim Broadbent