Print Page | Close Window

Truncating a Formula Field

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=20332
Printed Date: 03 May 2024 at 9:15pm


Topic: Truncating a Formula Field
Posted By: crystalnewbie33
Subject: Truncating a Formula Field
Date Posted: 08 Jan 2014 at 11:17am
I've looked through a lot of forums and can't quite find the right answer. I've created a formula that performs a calculation. I want the result of the calculation to only keep two decimals. I know you can change the number of decimals shown but I am totaling these calculations at the bottom of the report and I would like to total them without adding the extra decimals. I've tried to truncate the field under the customize, decimals, but keep getting error message of 'Bad Formula Result.' Any help would be greatly appreciated.


Replies:
Posted By: DBlank
Date Posted: 08 Jan 2014 at 12:07pm
round (result,2)

Posted By: crystalnewbie33
Date Posted: 09 Jan 2014 at 3:18am
Thank you for the response. I guess maybe I don't know where to put this formula as I am still getting the 'Bad Formula Result' message. Please explain where to place it if you will. Also, by 'result', do you mean the formula field? I put it in like this:
round ({@field},2)
Thanks again

Posted By: crystalnewbie33
Date Posted: 09 Jan 2014 at 3:20am
One other thought - maybe I have something wrong with my formula field? Do I need to declare the field to be a Number somewhere? I think it is automatically a String value but I'm not sure where (or if I need to) change this.

Posted By: DBlank
Date Posted: 09 Jan 2014 at 4:25am
what is your code for the "formula that performs the calculation"?

Posted By: crystalnewbie33
Date Posted: 09 Jan 2014 at 4:42am
Here is the formula:
IF {animalhist.Price_type} = "CWT" THEN
formula = ({animalhist.Bid_Price}*{animalhist.Weight})/100
ELSE
formula = {animalhist.Bid_Price}*{animalhist.Number_Head}
END if

Posted By: kostya1122
Date Posted: 09 Jan 2014 at 4:50am
try click edit formula and at the top find where it says basic syntax and change it with crystal syntax for
round formula.

Posted By: lockwelle
Date Posted: 09 Jan 2014 at 5:09am
I am guessing the DBlank would say:
IF {animalhist.Price_type} = "CWT" THEN
formula = ({animalhist.Bid_Price}*{animalhist.Weight})/100
ELSE
formula = {animalhist.Bid_Price}*{animalhist.Number_Head}
END if
formula = round(formula,2)


I don't use the Basic Syntax when coding reports, so this looks odd to me. I have gotten used to the Crystal syntax

Posted By: DBlank
Date Posted: 09 Jan 2014 at 5:09am

I don't use Basic syntax at all.

A Crystal syntax version of this would be :
 
ROUND(IF {animalhist.Price_type} = "CWT" THEN (({animalhist.Bid_Price}*{animalhist.Weight})/100) ELSE ({animalhist.Bid_Price}*{animalhist.Number_Head}),2)

Posted By: DBlank
Date Posted: 09 Jan 2014 at 5:12am
It looks like lockwelle has a good Basic syntax option with : 
 
IF {animalhist.Price_type} = "CWT" THEN
formula = ({animalhist.Bid_Price}*{animalhist.Weight})/100
ELSE
formula = {animalhist.Bid_Price}*{animalhist.Number_Head}
END if
formula = round(formula,2)

Posted By: crystalnewbie33
Date Posted: 09 Jan 2014 at 5:18am
I'm sorry. I realize my questions are very basic. Exactly where to I put the round formula? How do I put it in the formula field along with my formula? It says that it does not appear to be a part of the formula.

Posted By: crystalnewbie33
Date Posted: 09 Jan 2014 at 5:21am
Thank you all so much. The basic syntax option posted by lockwelle did indeed work. Thanks!!

Posted By: crystalnewbie33
Date Posted: 09 Jan 2014 at 5:32am
I tried the Crystal syntax version as well and it worked great. Also, performed truncate in the same way. Thanks again.

Posted By: DBlank
Date Posted: 09 Jan 2014 at 5:56am
I saw your other post on "bankers rounding" so a word of caution here.
please be aware that the options given in this post are applying the rounding before any sum is taking place which can give you a different result than doing the sum first and then the rounding on that sum.

Posted By: crystalnewbie33
Date Posted: 09 Jan 2014 at 6:12am
Thank you for the heads up, I will keep that in mind. In this situation, to match results I am getting from the MySql rounding, applying the rounding before summing works best. You have been a huge help to me, thanks again.


Print Page | Close Window