I need some help from crystal gurus, I am creating a crystal report using Stored Procedure.When I run stored procedure and get results for eg  for 1 field  it gets sum of (100) and when I pull this field in detail section and do a sum there I am expecting (100) but it is giving me some other value(eg 70).

 

Why are values not matching in reports and SQL SERVER

In your SP resulting in 100 are you adding differnt colmns together to get a total of these or are you SUM multiple rows to get the 100?

Same issue in Crystal...Are you adding columns or summing rows?

If youa re summing rows and you grouped your data it may be the reason.

Also are you using the select expert in crystal that would omit data that was not being excluded in your original SP... that might account for the difference.

After digging more into the issue, I see when I am running my stored procedure in SQL Server it is giving me record count of 3500 records and when I am calling Stored procedure it is ol=nly returning 2500 records and that's what is making my total SUM go wrong as 1000 records are missing but now the question is why is crystal report not pulling all the records..

Are you joining it to anything via the DB Expert in Crystal? If so the join may be dropping records so you may need a left join.

Are you using any select statement in crystal at all? That also might explain it.

I have another issue for the same report.

I need to take out average and then summarize it.

 

I can summarize it on running totals and one time by mistake the formula above did work and gave me correct result but since I refreshed it istarted giving me error.

 

my Formula for average is:

If {UpLoad_Report_EOM;1.PolicyCount} >0 then  0 else {UpLoad_Report_EOM;1.PolicyPremium} / {UpLoad_Report_EOM;1.PolicyCount} ;

 

error is : Division by zero

 

I tried multiple formula, but same error.

 

1. If {UpLoad_Report_EOM;1.PolicyCount} >=0 Then {UpLoad_Report_EOM;1.PolicyCount} *
({UpLoad_Report_EOM;1.PolicyPremium} / {UpLoad_Report_EOM;1.PolicyCount})

 

2. If {UpLoad_Report_EOM;1.PolicyCount} < 0 Then
     0
Else
    {UpLoad_Report_EOM;1.PolicyPremium} /{UpLoad_Report_EOM;1.PolicyCount}

 

AnY Idea.

If so is this for all records in the report or a sub grouping?

If a sub grouping do you have a group in your report set up that supports displaying the results?

 

 

Basically premium / count.

 

My count has values as 0's and 1's.

 

 

This leads me to believe that you really need a Summary Average.

premium / count is going to be premium / 1 or premium / 0 which is not going to give you an average.

I think you are really trying to get a average as:

or at some group level

 

Is this more on track with what you are trying to do?

Thanks, It worked.

 

Thanks a lot again.

ANy suggestions.

 

Sum({UpLoad_Report_EOM;1.PolicyPremium}, {UpLoad_Report_EOM;1.ProgramCode})
/ Sum({UpLoad_Report_EOM;1.PolicyCount},{UpLoad_Report_EOM;1.ProgramCode})

if Sum({UpLoad_Report_EOM;1.PolicyCount},{UpLoad_Report_EOM;1.ProgramCode}) =0 then 0 else

(Sum({UpLoad_Report_EOM;1.PolicyPremium}, {UpLoad_Report_EOM;1.ProgramCode})
/ Sum({UpLoad_Report_EOM;1.PolicyCount},{UpLoad_Report_EOM;1.ProgramCode}))

 

I have another small question. I am using this as formula in my report.

My report has a group Type(New and Renew).

Formula below is helping me to get average, but I cannot use this formula to do autosum based on Type. Any idea how can I do.

 

Field I am using for Type is : {UpLoad_Report_EOM;1.PolicyType}

"New" & "Renew"

 

"if Sum({UpLoad_Report_EOM;1.PolicyCount},{UpLoad_Report_EOM;1.ProgramCode}) =0 then 0 else
(Sum({UpLoad_Report_EOM;1.PolicyPremium}, {UpLoad_Report_EOM;1.ProgramCode})
/ Sum({UpLoad_Report_EOM;1.PolicyCount},{UpLoad_Report_EOM;1.ProgramCode}

Sum(x,y)

x=field to sum

y=field grouped on

If you want to do the sum at a different group level, use that group level field in the formula instead, probably will be:

 

if Sum({UpLoad_Report_EOM;1.PolicyCount},{UpLoad_Report_EOM;1.PolicyType}) =0 then 0 else
(Sum({UpLoad_Report_EOM;1.PolicyPremium}, {UpLoad_Report_EOM;1.PolicyType})
/ Sum({UpLoad_Report_EOM;1.PolicyCount},{UpLoad_Report_EOM;1.PolicyType})

The formula must be palced on the Group header or footer that it is evaluated for ({UpLoad_Report_EOM;1.PolicyType} group footer or header) in order to get the correct results to display.

1. Policy Type (New, Renew)

2. Program code (Multiple)

Columns I am using are getting Running Totals, based on Program type.

 

Formula below helped me to get the right average but on this formula I cannot do autosum .

I need sum on average based on Program code first and then Policy type.

 

Report is showing all values correct but only SUm on AVerage is giving incorrect values..

 

if Sum({UpLoad_Report_EOM;1.PolicyCount},{UpLoad_Report_EOM;1.ProgramCode}) =0 then 0 else
(Sum({UpLoad_Report_EOM;1.PolicyPremium}, {UpLoad_Report_EOM;1.ProgramCode})
/ Sum({UpLoad_Report_EOM;1.PolicyCount},{UpLoad_Report_EOM;1.ProgramCode}))

.

 

As far as I know Crystal does not allow the summary of a summary function.

Eg. AVERAGE of a SUM or a SUM of an AVERAGE.

Total New Business for Average is Missing.

We got this above average from the formula you gave.

To get the average you need to create a new formula field that will give you the average for the group level and place the formula field in the group footer for that. This should be the same formula that gets your other percentages but replace the group level field in it with whatever the field is that you get the "New Business" result from.

 

You also may want to test out the two sums (that you are using in the division by) as seperate formulas to see if they are summing correctly. Place them on that same footer to see the values per group level.

If they are not correct are there duplicate records that need to be accounted for?

 

Subtotal new Business (red column should have totals for all the above AVG)

Eg AVG total for these should be:(11012)

 

Average what all numbers you see above we got from the formula.

 

if Sum({UpLoad_Report_EOM;1.PolicyCount},{UpLoad_Report_EOM;1.ProgramCode}) =0 then 0 else
(Sum({UpLoad_Report_EOM;1.PolicyPremium}, {UpLoad_Report_EOM;1.ProgramCode})
/ Sum({UpLoad_Report_EOM;1.PolicyCount},{UpLoad_Report_EOM;1.ProgramCode}))

 

I think we need another formula that can sum these up, based on New and Renew Business.

We need to use same formula what is above with adding another group type to it as ({UpLoad_Report_EOM;1.PolicyType})

 

 

 

 

PRGM - CODE	AVG
NEW BUSINESS
ES	0
HMS-MAIN	897
REIC/RESW	311
REXS	822
RMAX	7,364
RMAXF	0
STD	1,617
Sub Total New Business

PRGM - CODE	AVG
RENEW BUSINESS
ES	0
HMS-MAIN	454
REIC/RESW	775
REXS	822
RMAX	456
RMAXF	88
STD	1,617
Sub Total ReNew Business

Sorry I cannot just give you the formula but it is really hard to understand your table and field names and then know how you have them grouped in the report...

Based on your last commment I think it will be:

 

You can also tets each part for your amounts in seperately formulas as I suggested earlier...

and

 

 

to verify these give you the 11012 and 4212 sums you indicated above.

None of these are working, I tried these earlier too.