SUM conditional Field
Printed From: Crystal Reports Book
Category: Crystal Reports for Visual Studio 2005 and Newer
Forum Name: Writing Code
Forum Discription: .NET programming API, report integration
URL: http://www.crystalreportsbook.com/forum/forum_posts.asp?TID=953
Printed Date: 28 Apr 2024 at 3:56am
Topic: SUM conditional Field
Posted By: leedo
Subject: SUM conditional Field
Date 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?!!!  Please used VB Syntax if possible. Thanks |
Replies:
Posted By: jkwrpc
Date 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.
http://www.CustomReportWriters.net - www.CustomReportWriters.net
|
Posted By: leedo
Date 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. |
Posted By: jkwrpc
Date 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.
http://www.CustomReportWriters.net - www.CustomReportWriters.net
|
Posted By: leedo
Date 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 |