Writing Code
 Crystal Reports Forum : Crystal Reports for Visual Studio 2005 and Newer : Writing Code
Message Icon Topic: SUM conditional Field Post Reply Post New Topic
Author Message
leedo
Newbie
Newbie
Avatar

Joined: 28 Jun 2007
Location: Egypt
Online Status: Offline
Posts: 7
Quote leedo Replybullet Topic: SUM conditional Field
    Posted: 02 Jul 2007 at 6:54am
Hi,

I am a bit lost here and need some help. I have the following fields in my report:

- Called Number<Text>
- Rate<Number>
- Duration <Number>
- Price<Formula> => Rate * Duration

Normally to simply sum the "Price" field I'd write: =>SUM({Cost_report.Calls})

However, I need to create two Summary fields. Each one only sums a subset of the the "Price" column based on a condition in the "CalledNumber" column as follows:

CalledNumber                   Price
------------------                   ------
97184323                          2.5
92343323                          23.00

84755543                          30.5
83343321                          8.0

94532234                          10.5
       

Sum for only numbers that begin with 9==> 36.00
Sum for only numbers that begin with 8==> 38.5

Any ideas?!!!Confused


Please used VB Syntax if possible.

Thanks






IP IP Logged
jkwrpc
Senior Member
Senior Member


Joined: 19 Jun 2007
Location: United States
Online Status: Offline
Posts: 432
Quote jkwrpc Replybullet Posted: 02 Jul 2007 at 9:28am
I have not tested this so the errors are mine,
---------------
If Mid (CStr({calledNumber},0)) = "8" then
formula = ({calledMinutes}* .385
else
formula =  ({calledMinutes}* .36)
-----------------------
 
What it trying to do is test the first digit of the number to see it is an 8.If it is then it is to calculate at one rate if it is not an 8 then it is to calculate at the other rate.
 
There are probably errors (the field names are wrong, but you should be able to sort them out easily enough.
 
Hope it helps.
 
Regards,
 
John W.
IP IP Logged
leedo
Newbie
Newbie
Avatar

Joined: 28 Jun 2007
Location: Egypt
Online Status: Offline
Posts: 7
Quote leedo Replybullet Posted: 03 Jul 2007 at 6:47am
Thanks.. but I figured that I can use LEFT function to do it as well. I still need  to do the conditional summation in one formula field but aparently it cannot be done.
IP IP Logged
jkwrpc
Senior Member
Senior Member


Joined: 19 Jun 2007
Location: United States
Online Status: Offline
Posts: 432
Quote jkwrpc Replybullet Posted: 03 Jul 2007 at 2:04pm

If nothing else works,  you should be able to create a manual running total formula that would accept your conditions.  It would include similar logic to your LEFT, etc.

If you have not done this before, it is really quite simple. Let us know and me or someone else can help. 
 
It seems like you are close, so dont quit now.
 
 
Regards,
 
John W.
IP IP Logged
leedo
Newbie
Newbie
Avatar

Joined: 28 Jun 2007
Location: Egypt
Online Status: Offline
Posts: 7
Quote leedo Replybullet Posted: 07 Jul 2007 at 3:16am
Thanks all for your help. I used the LEFT function and used the two running totals technique.

Thanks
IP IP Logged
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.