Technical Questions
 Crystal Reports Forum : Crystal Reports 9 through 2020 : Technical Questions
Message Icon Topic: Subtracting amount credit/debit Post Reply Post New Topic
<< Prev Page  of 4 Next >>
Author Message
DBlank
Moderator
Moderator


Joined: 19 Dec 2008
Online Status: Offline
Posts: 9053
Quote DBlank Replybullet Posted: 31 Oct 2012 at 3:38am
so you want to remove groups that have a sum = to a particular field?
what is the field type of SFE_CFEE?
IP IP Logged
esther31
Groupie
Groupie


Joined: 19 Dec 2011
Online Status: Offline
Posts: 50
Quote esther31 Replybullet Posted: 01 Nov 2012 at 11:49pm
Hello
 
Yes that's correct, the SFE_CFEE is a string which i'm guessing is where it's failing.
 
Thanks
IP IP Logged
esther31
Groupie
Groupie


Joined: 19 Dec 2011
Online Status: Offline
Posts: 50
Quote esther31 Replybullet Posted: 04 Nov 2012 at 9:15pm
Do i need to convert the number into text?
IP IP Logged
DBlank
Moderator
Moderator


Joined: 19 Dec 2008
Online Status: Offline
Posts: 9053
Quote DBlank Replybullet Posted: 05 Nov 2012 at 3:55am
convert the text to a numeric field
//number_FEE
val({SRS_SFE.SFE_CFEE})
 
I assume this value is the same for all rows in the group
you can then insert a summary on this formula field at the group level
 
now in the select criteria you change it to the group select criteria and use the summary conditions here to excldue the whole group frmt he report
maximum({@number_FEE},{group field})=sum({other_formula},{group field})
 
IP IP Logged
esther31
Groupie
Groupie


Joined: 19 Dec 2011
Online Status: Offline
Posts: 50
Quote esther31 Replybullet Posted: 06 Nov 2012 at 4:04am
Hello
 
Sorry i'm back again!
 
This doesn't seem to be working quite as i would expect it to, I have listed an example below, is anybody able to help me identify where it is going wrong?
 
customer has one credit type{SRS_FDU.FDU_CORD} record of 1,000.00 summing correctly with this forumla :if {SRS_FDU.FDU_CORD}='C' then {SRS_FDU.FDU_AMNT}
 
customer has one debit type{SRS_FDU.FDU_CORD} record of 9,000.00 summing correctly with this formula: if {SRS_FDU.FDU_CORD}='D' then {SRS_FDU.FDU_AMNT}
 
this formula does not seem to be displaying as i would expect:
if {SRS_FDU.FDU_CORD}='C' then {SRS_FDU.FDU_AMNT} else {SRS_FDU.FDU_AMNT} *(-1) -  i would expect to see -8,000.00 but is showing me -9,000.00
 
if i do a sum of this sum then the correct amount is displayed but i am unable to use this to compare with SFE_CFEE as above.
 
any advice?
 
thanks
 
IP IP Logged
DBlank
Moderator
Moderator


Joined: 19 Dec 2008
Online Status: Offline
Posts: 9053
Quote DBlank Replybullet Posted: 06 Nov 2012 at 4:12am
it is showing exactly what it is expected to show.
All the fomrula is doing is leaving credits as a positive value and making debits a negative value. So when you go to sum this formula field it will give you the total of all credits - the total of all debits.
if you need to see it change row by row use a Running Total.
right click on Running Total
select new
name='Total" (or whatever)
field to summarize= the formula field above (if {SRS_FDU.FDU_CORD}='C' then {SRS_FDU.FDU_AMNT} else {SRS_FDU.FDU_AMNT} *(-1) )
summary type=sum
evaluate= for each record
reset=never if you want it to keep showing changes for every row or on change of a group if you want to start fresh on a new grouping
 
place it in the detail row to see row by row changes
place it in a footer to see the total only


Edited by DBlank - 06 Nov 2012 at 5:15am
IP IP Logged
esther31
Groupie
Groupie


Joined: 19 Dec 2011
Online Status: Offline
Posts: 50
Quote esther31 Replybullet Posted: 06 Nov 2012 at 4:50am
Thanks for the prompt reply, makes sense to me now.
 
My running total is now displaying the figure as a - but the field i need to compare it to doesn't do this, is there a way to strip this off for the comparison?
 
Thanks
 
IP IP Logged
DBlank
Moderator
Moderator


Joined: 19 Dec 2008
Online Status: Offline
Posts: 9053
Quote DBlank Replybullet Posted: 06 Nov 2012 at 5:22am
is it it corerctly showing as a negative value (meaning there were more debits than credits? If so just add teh 2 values together. If they = 0 then they are 'the same number' (one positive and one negative)
 
maximum({@number_FEE},{group field}) + sum({other_formula},{group field}) = 0
 
IP IP Logged
esther31
Groupie
Groupie


Joined: 19 Dec 2011
Online Status: Offline
Posts: 50
Quote esther31 Replybullet Posted: 06 Nov 2012 at 5:38am
not all students will have a credit but i need to compare the total amount without the debit with the SRS_SFE.SFE_CFEE field
IP IP Logged
esther31
Groupie
Groupie


Joined: 19 Dec 2011
Online Status: Offline
Posts: 50
Quote esther31 Replybullet Posted: 08 Nov 2012 at 12:50am
Can anybody help with this please? Everytime i feel like i'm a step closer I seem to undo what i've already done!
IP IP Logged
<< Prev Page  of 4 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.031 seconds.