Crystal Reports Subtracting amount credit/debit

Technical Questions
Crystal Reports Forum : Crystal Reports 9 through 2022 : Technical Questions

Topic: Subtracting amount credit/debit
	<< Prev Page of 4 Next >>

Author	Message
DBlank Moderator Joined: 19 Dec 2008 Online Status: Offline Posts: 9053	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 Logged

esther31 Groupie Joined: 19 Dec 2011 Online Status: Offline Posts: 50	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 Logged

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

DBlank Moderator Joined: 19 Dec 2008 Online Status: Offline Posts: 9053	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 maximum({@number_FEE},{group}) 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 Logged

esther31 Groupie Joined: 19 Dec 2011 Online Status: Offline Posts: 50	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 Logged

DBlank Moderator Joined: 19 Dec 2008 Online Status: Offline Posts: 9053	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 Logged

esther31 Groupie Joined: 19 Dec 2011 Online Status: Offline Posts: 50	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 Logged

DBlank Moderator Joined: 19 Dec 2008 Online Status: Offline Posts: 9053	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 Logged

esther31 Groupie Joined: 19 Dec 2011 Online Status: Offline Posts: 50	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 Logged

esther31 Groupie Joined: 19 Dec 2011 Online Status: Offline Posts: 50	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 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.016 seconds.