Print Page | Close Window

Sum

Printed From: Crystal Reports Book
Category: Crystal Reports 9 through 2020
Forum Name: Technical Questions
Forum Discription: Formulas, charting data, Crystal syntax, etc.
URL: http://www.crystalreportsbook.com/forum/forum_posts.asp?TID=330
Printed Date: 29 Apr 2024 at 9:09pm


Topic: Sum
Posted By: mdscom
Subject: Sum
Date Posted: 15 Mar 2007 at 12:45pm

basic questions I guess....

I'm trying to sum an amount field that is recorded as text.  So it needs to be converted into Currency, but I only want it to sum if a different field is equal to 1.  So here's what I've got and the formula editor won't take it.
 
 
${ml_pic10md.AMOUNT}
Sum ({ml_pic10md.AMOUNT},{ml_pic10md.FDISP} = "1")
 
 
What am I missing?  Something tells me I'll be visiting these forums a lot in the coming weeks...


Replies:
Posted By: BrianBischof
Date Posted: 15 Mar 2007 at 1:05pm
Well, you really need to read the free online chapters about Crystal Reports formulas and the programming language. That will help get you going in the right direction.

I would create a global variable for tracking the sum. This will be a manual running total.

Global NumberVar TotalAmount;
IF {ml_pic1md.FDISP}="1" Then
    TotalAmount := Total Amount + {ml_pic10d.AMOUT};



-------------
Please support the forum! Tell others by linking to it on your blog or website:<a href="http://www.crystalreportsbook.com/forum/">Crystal Reports Forum</a>

Posted By: mdscom
Date Posted: 15 Mar 2007 at 1:14pm
Ok, so by doing that it says:
 
"A number, currency amount, boolean, date, time, date-time, or string is expected here."
 
And it highlights the word "Total"
 
 
 
 
I'll be frank, I really know nothing.  I've got the complete reference to XI by George Peck, but no personal knowledge really in how to do this.  If it was MS access I could wouldn't have any issues writing the string, but since I can't directly import SQL lines from there I'm going to get stuck a lot

Posted By: BrianBischof
Date Posted: 15 Mar 2007 at 1:33pm
It's highlighting the TotalAmount variable? Then the report field must be a currency data type. Change the variable declaration to 
Global CurrencyVar TotalAmount;

That should make them the same data types.


-------------
Please support the forum! Tell others by linking to it on your blog or website:<a href="http://www.crystalreportsbook.com/forum/">Crystal Reports Forum</a>

Posted By: mdscom
Date Posted: 15 Mar 2007 at 1:36pm
Nope, did the same thing.
 
Maybe some more information will help.  It's an amount field, that contains text characters, not numeric characters.  So it has to be converted to Currency or numerical values before it can be summed. 
 
How can I convert it to a Currency AND include the summation in the same formula?

Posted By: BrianBischof
Date Posted: 15 Mar 2007 at 2:28pm
It has text characters AND numbers? What are some sample values?

-------------
Please support the forum! Tell others by linking to it on your blog or website:<a href="http://www.crystalreportsbook.com/forum/">Crystal Reports Forum</a>

Posted By: mdscom
Date Posted: 15 Mar 2007 at 2:35pm
No, it's just numbers, but they're in text format rather than in number.
 
For example, in MS Access if you enter a table and select design veiw, the normal setting is that each field is a text field but you can still enter numbers.
 
That's the case with these and we don't want to change the field structure we just want the report to change it when the report is run.
 

Posted By: BrianBischof
Date Posted: 15 Mar 2007 at 2:48pm
ToNumber(field) converts a string to a number data type


-------------
Please support the forum! Tell others by linking to it on your blog or website:<a href="http://www.crystalreportsbook.com/forum/">Crystal Reports Forum</a>

Posted By: wrightr
Date Posted: 31 Mar 2007 at 7:59pm
Should the Total Amount be TotalAmount to match the first TotalAmount?

-------------
New user - Crystal Reports 9

Posted By: BrianBischof
Date Posted: 01 Apr 2007 at 10:25am
Yes, that was a typo on my part. All instances of "TotalAmount" should be identical.

-------------
Please support the forum! Tell others by linking to it on your blog or website:<a href="http://www.crystalreportsbook.com/forum/">Crystal Reports Forum</a>

Posted By: ryaf
Date Posted: 29 Nov 2009 at 5:55pm
how to convert string to number Confused


Print Page | Close Window