Active TopicsMemberlistSearchHelpRegisterLogin

Technical Questions
 Crystal Reports Forum : Crystal Reports 9 through 2020 : Technical Questions
Message Icon Topic: Crystal Reports 2008 Post Reply Post New Topic
Page  of 3 Next >>
Author Message
vpriyanka2001
Groupie
Groupie


Joined: 28 May 2009
Online Status: Offline
Posts: 59
Quote vpriyanka2001 Replybullet Topic: Crystal Reports 2008
    Posted: 28 May 2009 at 8:41am
Hi,
 
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
IP IP Logged
DBlank
Moderator
Moderator


Joined: 19 Dec 2008
Online Status: Offline
Posts: 9053
Quote DBlank Replybullet Posted: 28 May 2009 at 9:51am
Not sure I am understanding your problem. Perhaps you can clarify.
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.
IP IP Logged
vpriyanka2001
Groupie
Groupie


Joined: 28 May 2009
Online Status: Offline
Posts: 59
Quote vpriyanka2001 Replybullet Posted: 28 May 2009 at 10:01am
Thanks for replying.
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..
IP IP Logged
DBlank
Moderator
Moderator


Joined: 19 Dec 2008
Online Status: Offline
Posts: 9053
Quote DBlank Replybullet Posted: 28 May 2009 at 10:06am

So you are using the SP directly in your report, correct?

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.
IP IP Logged
vpriyanka2001
Groupie
Groupie


Joined: 28 May 2009
Online Status: Offline
Posts: 59
Quote vpriyanka2001 Replybullet Posted: 29 May 2009 at 9:44am

I was able to fix that, thanks for all your help.

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.
IP IP Logged
DBlank
Moderator
Moderator


Joined: 19 Dec 2008
Online Status: Offline
Posts: 9053
Quote DBlank Replybullet Posted: 29 May 2009 at 9:47am
If {UpLoad_Report_EOM;1.PolicyCount} = 0 then 0 else {UpLoad_Report_EOM;1.PolicyPremium} / {UpLoad_Report_EOM;1.PolicyCount}
IP IP Logged
vpriyanka2001
Groupie
Groupie


Joined: 28 May 2009
Online Status: Offline
Posts: 59
Quote vpriyanka2001 Replybullet Posted: 29 May 2009 at 9:55am
If I do = (then my result gets incorrect) if I do <0 then only it gives me correct average.
IP IP Logged
DBlank
Moderator
Moderator


Joined: 19 Dec 2008
Online Status: Offline
Posts: 9053
Quote DBlank Replybullet Posted: 29 May 2009 at 10:09am
Are you trying to get an average from 2 fields in one DB row or are you trying to get an average of a count of rows and a sum of another field from all of the counted rows?
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?
 
 
IP IP Logged
vpriyanka2001
Groupie
Groupie


Joined: 28 May 2009
Online Status: Offline
Posts: 59
Quote vpriyanka2001 Replybullet Posted: 29 May 2009 at 10:51am
I have 2 field names as Policies Issued Count and Policies Issued Premium and I have to take out average from these 2 fields.
Basically premium / count.
 
My count has values as 0's and 1's.
 
 
IP IP Logged
DBlank
Moderator
Moderator


Joined: 19 Dec 2008
Online Status: Offline
Posts: 9053
Quote DBlank Replybullet Posted: 29 May 2009 at 11:29am
Originally posted by vpriyanka2001

 
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:
Sum({UpLoad_Report_EOM;1.PolicyPremium}) / Sum({UpLoad_Report_EOM;1.PolicyCount})
or at some group level
Sum({UpLoad_Report_EOM;1.PolicyPremium}, groupfieldhere) / Sum({UpLoad_Report_EOM;1.PolicyCount}, groupfieldhere)
 
Is this more on track with what you are trying to do?
IP IP Logged
Page  of 3 Next >>
Post Reply Post New Topic
Printable version Printable version

Forum Jump 		You cannot post new topics in this forum
You cannot reply to topics in this forum
You cannot delete your posts in this forum
You cannot edit your posts in this forum
You cannot create polls in this forum
You cannot vote in polls in this forum



This page was generated in 0.063 seconds.
HOME | FREE EBOOK | PROGRAMMING GUIDE | PURCHASE | REVIEWS | CONTACT ME | FAQ | LINKS | CLICK PLAYS | COACH FORUMS | TROPICAL FISH

© 2004-2024 Brian Bischof. All Rights Reserved.