Rounding Issues on Summary Lines 1

[TimothyP]

I am trying to recreate a Budget report in Crystal Reports 8.5.

Researching this site, I can see there are various issues with CR regarding totaling and summarizing numbers. Unfortunately, I have not been able to find a solution that works for me.

My previous post thread767-1178783 gives the details of my SQL database. The only correction I have to my sample data is that there are two decimal places for every number. For example 75,000.40, and 4,000.67.

I can round my detail lines fine. However, when I create a summary field or formula totaling the detail lines, the rounding is ignored.

Does CR 8.5 have a work-around for this?

======================
Crystal Reports 8.5
Database: SQL Server

 

[lbass]

If you want the summaries to add the rounded figures, instead of rounding the results, then change the formulas (from your other thread) to:

//{@Net Pay}:
if {table.fund} in [1200,1300] then round({table.salary})

//{@Pension}:
if {table.fund} =1400 then round({table.salary})

//{@Healtlh}:
if {table.fund} =1500 then round({table.salary})

Then any inserted summaries will be based on the rounded values.

-LB

 

[TimothyP]

Thanks LB!

Unfortunately, the Budget report I'm trying to recreate does not do the rounding until it adds up all of the detail columns for each employe number. Sorry I did not mention this earlier.

For example,

Emp
No Name Salary Fund
1 Smith 75,000.62 1200
1 Smith 4,000.41 1300
1 Smith 2,000.56 1400
1 Smith 3,000.78 1500

All salary detail lines are added

75,000.62
4,000.41
2,000.56
3,000.78
==========
84,002.37

then 84,002.37 is rounded to 84,002.00.

Any ideas?

======================
Crystal Reports 8.5
Database: SQL Server

 

[lbass]

Replace the salary field with a formula:

round({table.salary})

...and then insert the summary on this formula.

-LB

 

[TimothyP]

Then I get...

75,000.62 rounded to 75,001
4,000.41 rounded to 4,000
2,000.56 rounded to 2,001
3,000.78 rounded to 3,001

which totals to 84,003.


I want to do this...

75,000.62
4,000.41
2,000.56
3,000.78
==========
84,002.37 Round here to get 84,002 and summarize by this whole number.

======================
Crystal Reports 8.5
Database: SQL Server

 

[TimothyP]

I may not be explaining my problem very well.

Let me try again.

My table looks something like this...

Emp
No Name Salary Fund
1 Smith 75,000.60 1200
1 Smith 4,000.70 1300
1 Smith 2,000.75 1400
2 Harris 80,000.35 1200
2 Harris 2,000.45 1300
2 Harris 3,000.80 1400

I want my report to look like this...

Emp
No Name Salary
1 Smith 81,002
2 Harris 85,002
Salary Total 166,004

Emp No 1’s salary is (75,000.60 + 4,000.70 + 2,000.75) = 81,002.05 rounded
Emp No 2’s salary is (80,000.35 + 2,000.45 + 3000.80) = 85,001.60 rounded


======================
Crystal Reports 8.5
Database: SQL Server

 

[lbass]

Perhaps we're missing the obvious here. Have you right- clicked on the summary field and used the icon to decrease the decimals? But I'm guessing your example might not capture what the issue is.

If you mean you want the rounding to occur per employee summary before being totaled, then you would need to use a variable. Let's say you want to total this at a department group level:

//{@reset} to be placed in a group header based on an department group:
whileprintingrecords;
numbervar depttot := 0;

//{@accum} to be placed in the employee group header or footer:
whileprintingrecords;
numbervar depttot := depttot +
round(sum({table.salary},{table.employee}));

//{@displ} to be placed in the department group footer:
whileprintingrecords;
numbervar depttot;

-LB

 

[TimothyP]

Yes, I have right-clicked on the summary field and used the icon to decrease the decimals. My detail lines look good. It's my department subtotals that I can't seem to round properly.

I'm getting error message ("A number is required here") after the =: when I try to create the following formula.

whileprintingrecords;
numbervar depttot := depttot +
round(sum({table.salary},{table.employee}));

======================
Crystal Reports 8.5
Database: SQL Server

 

[lbass]

Try changing the variable to currencyvar instead of numbervar.

-LB

 

[TimothyP]

Changing the variable to currencyvar instead of numbervar worked. This set of formulas works perfectly for my row totals!

Thanks for the dedication!

Now I want to include totals for each fund. In other words, I want to include column totals for each subfund.

How can I use a similar formula to only accumulate for certain fund types? Sorta like my original question to only include certain fund types for each detail line?

This was your original solution that worked great for the detail lines.

//{@Net Pay}:
if {table.fund} in [1200,1300] then {table.salary}

//{@Pension}:
if {table.fund} =1400 then {table.salary}

//{@Healtlh}:
if {table.fund} =1500 then {table.salary}


Now I would like to somehow add the "if {table.fund} in [1200,1300]" logic to determine my column subtotals.

======================
Crystal Reports 8.5
Database: SQL Server

 

[lbass]

For each fund type, create variables as in my last post. You can use a combined reset formula and a combined accumulation formula, but you need separate display formulas for each subfund:

//{@reset} to be placed in a group header based on an department group:
whileprintingrecords;
currencyvar depttot := 0;
currencyvar netpay := 0;
currencyvar pension := 0;//etc.

//{@accum}:
whileprintingrecords;
currencyvar depttot := depttot + round(sum({table.salary},{table.employee}));
currencyvar netpay := netpay + round(sum({@netpay},{table.employee}));
currencyvar pension := pension + round(sum({@pension},{table.employee}));//etc.

//{@displaynetpay}:
whileprintingrecords;
currencyvar netpay;

//{@displaypension}:
whileprintingrecords;
currencyvar pension;

-LB

 

[TimothyP]

That's it!!!

Thank you very much for your help.

Tim

======================
Crystal Reports 8.5
Database: SQL Server