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: 08 Nov 2012 at 1:40am
Cn you show sample data and what you want it to do? I am not really able to follow the logic of your last request as I cannot see your data.
IP IP Logged
esther31
Groupie
Groupie


Joined: 19 Dec 2011
Online Status: Offline
Posts: 50
Quote esther31 Replybullet Posted: 08 Nov 2012 at 1:50am

sorry for causing so much confusion! i will list out the relevant fields and then what i am trying to achieve!

FDU_AMNT.FDU = Fee amount generated
FDU_CORD.FDU - Credit or Debit? (C or D)
SFU_CFEE.SFU = Expected Fee on Customer Record
 
The fee amount is always a positive number whether credit or debit and a customer may have multiple debits or credits or no credits. I want to sum FDU_AMNT.FDU where the FDU.CORD.FDU is C, sum them again where the FDU_CORD.FDU is D and then take away the total for C from the total for D but leave a positive number despite the figure being a debit. I then want to compare this total to SFU_CFEE.SFU and then only show records where these two figures don't match.
 
Thank you again for all your support, i am completely stumped!
 
Please let me know if you need any further information
IP IP Logged
DBlank
Moderator
Moderator


Joined: 19 Dec 2008
Online Status: Offline
Posts: 9053
Quote DBlank Replybullet Posted: 08 Nov 2012 at 3:53am
so i assume that the expecgted fee is the same on all of the rows for the group
you should be able to use the abs() function to foece the value to be positive so try this
 
maximum({@number_FEE},{group field}) = abs(sum({other_formula},{group field}))
 
IP IP Logged
esther31
Groupie
Groupie


Joined: 19 Dec 2011
Online Status: Offline
Posts: 50
Quote esther31 Replybullet Posted: 11 Nov 2012 at 9:15pm
Hello
 
This doesn't seem to work. I have setup a group of customer number, say 12345678 i have then placed the FDU_AMNT in the detail and the FDU_CORD. Customer 12345678 may have a debit record of 500, a credit record of 300 and another debit of 200. I want to be able to sum the debits giving me 700 at the group footer, sum the credits giving me 300 at the group footer then sum the debit minus credit giving me 400. I then want to compare this figure with SFU_CFEE and only display the records where this doesn't match.
 
thanks
IP IP Logged
esther31
Groupie
Groupie


Joined: 19 Dec 2011
Online Status: Offline
Posts: 50
Quote esther31 Replybullet Posted: 13 Nov 2012 at 3:16am
Sorry, can anyone offer any guidance?
 
Thanks
IP IP Logged
DBlank
Moderator
Moderator


Joined: 19 Dec 2008
Online Status: Offline
Posts: 9053
Quote DBlank Replybullet Posted: 13 Nov 2012 at 3:41am
use the
if {SRS_FDU.FDU_CORD}='C' then {SRS_FDU.FDU_AMNT} else {SRS_FDU.FDU_AMNT} *(-1) 
(lets call it "Pos&Neg" for this example) formula field to sum, not the FDU_AMT field. .
 
IP IP Logged
esther31
Groupie
Groupie


Joined: 19 Dec 2011
Online Status: Offline
Posts: 50
Quote esther31 Replybullet Posted: 13 Nov 2012 at 3:51am
i've created this:
 
maximum({SRS_SFE.SFE_CFEE},{SRS_SFE.SFE_STUC}) = abs(sum({@Sum of CorD},{SRS_SFE.SFE_STUC}))
 
It's saying a string is required?
 
 
IP IP Logged
DBlank
Moderator
Moderator


Joined: 19 Dec 2008
Online Status: Offline
Posts: 9053
Quote DBlank Replybullet Posted: 13 Nov 2012 at 3:59am
what did it highlight?
IP IP Logged
esther31
Groupie
Groupie


Joined: 19 Dec 2011
Online Status: Offline
Posts: 50
Quote esther31 Replybullet Posted: 13 Nov 2012 at 4:00am
abs(sum({@Sum of CorD},{SRS_SFE.SFE_STUC}))
this bit, thanks
IP IP Logged
DBlank
Moderator
Moderator


Joined: 19 Dec 2008
Online Status: Offline
Posts: 9053
Quote DBlank Replybullet Posted: 13 Nov 2012 at 4:20am
it appears that the SRS_SFE.SFE_STUC is a string and not a number
convert it to a number first
call the formula "NumberCheck" (or whatever you want)
tonumber({SRS_SFE.SFE_STUC})
 
now us it in the other formula
maximum({@NumberCheck},{SRS_SFE.SFE_STUC}) = abs(sum({@Sum of CorD},{SRS_SFE.SFE_STUC}))
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.