Solved

Crystal Reports--Eliminating Rounding in a Formula

Posted on 2014-02-25
15
6,615 Views
Last Modified: 2014-02-27
Hello:

I have the following formula in Crystal Reports:

if {IV00101.USCATVLS_5} = "" then
0
else
(Cdbl({IV00101.USCATVLS_5})*1.0)/2.2046

This results in .13.  I need it to result in .127 (i.e. no rounding).  

How do I accomplish this?

Thanks!

TBSupport
0
Comment
Question by:TBSupport
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 6
  • 5
  • 4
15 Comments
 
LVL 100

Expert Comment

by:mlmcc
ID: 39887864
Are you just displaying this on the report?

Have you formatted it to show more than 1 decimal?
Right click the field
Click FORMAT FIELD
Click the NUMBER tab
Choose the format you want or click customize
Set decimals to 2 and rounding to .01

mlmcc
0
 
LVL 35

Expert Comment

by:James0628
ID: 39888396
mlmcc is probably correct and it's just the formatting, although you want 3 decimals instead of 2.

 FWIW, you could eliminate the test for "" if you use Val, instead of CDbl.  Val converts any leading numeric characters in a string to a number.  For example, Val ("123abc456") would give you 123.  For an empty string (""), you'd get 0.  So, your formula could be just one line:

(Val({IV00101.USCATVLS_5})*1.0)/2.2046


 James
0
 
LVL 1

Author Comment

by:TBSupport
ID: 39888541
It's not a matter of formatting.  I need the result mathematically to be .127, as well as to display .127.  And, I'm afraid that the "Val" formula resulted in the same figure (.13).

TBSupport
0
Free Tool: Path Explorer

An intuitive utility to help find the CSS path to UI elements on a webpage. These paths are used frequently in a variety of front-end development and QA automation tasks.

One of a set of tools we're offering as a way of saying thank you for being a part of the community.

 
LVL 35

Expert Comment

by:James0628
ID: 39888601
CR doesn't normally round calculations to 2 decimal places.  I'm sure there's a limit to how many decimal places it will calculate, but it's much more than that.  For example, the following formula gives me 0.1270 when I format the field to show 4 decimal places, and there are more decimals there, if I let it show them.

(Cdbl(".28")*1.0)/2.2046

 If you try that formula, what result do you get?

 Just to make sure, if you're using the "Customize" option in the field format, make sure you set both "Decimals" and "Rounding".

 James
0
 
LVL 1

Author Comment

by:TBSupport
ID: 39890539
I don't understand what you mean by the "Customize option".

I'm just trying to find a way, through modifying the formula, to get Crystal to not round to the second decimal place.  

TBSupport
0
 
LVL 100

Expert Comment

by:mlmcc
ID: 39890736
To the best of my knowledge, Crystal does NOT round values except for display.  It is not being rounded in the calculation


Have you tried

if {IV00101.USCATVLS_5} = "" then
0
else
(Cdbl({IV00101.USCATVLS_5})*1.000)/2.2046

or

if {IV00101.USCATVLS_5} = "" then
0
else
Cdbl({IV00101.USCATVLS_5})/2.2046


mlmcc
0
 
LVL 35

Accepted Solution

by:
James0628 earned 500 total points
ID: 39890758
The thing is, that formula should not be doing any rounding in the first place.  IOW, I don't think there's anything wrong with the formula.  CR has functions like Round, to round the result of a calculation in a formula.  Since you're not using one of those functions in that formula, the calculation is presumably not being rounded, so the most likely thing is that your formatting for the field is rounding the result.  For example, if I use the formula in my last post and set the field to use 10 decimal places, I get 0.1270071668 .

 If you go to the field format and select the Number tab, there is a Customize button.  If you click on that, you can set the "Decimals" (number of digits displayed to the right of the decimal point) and "Rounding" (significant digits to the right of the decimal point).  What I was saying in my last post was that you need to set both of those.  For example, if you set Decimals to 1.000 (3 places), but leave Rounding set to 0.01 (2 places), .127 would be displayed as 0.130, because the field is set to show 3 decimal places, but it's also set to round to 2.

 There are some predefined formatting options with 4 decimal places.  You might try one of those first, like -1123.0000 .  If the other decimal places show up, then you can use Customize and change both the Decimals and Rounding settings to 3 decimal places.

 James
0
 
LVL 1

Author Comment

by:TBSupport
ID: 39891653
When I go to the field format, there is no "Number" tab.
0
 
LVL 35

Expert Comment

by:James0628
ID: 39891680
You're looking at the field format for which field?

 You included a formula in your first post.  We (I think I can speak for mlmcc on this) have been assuming that you created a formula in the report that contains only the lines in the formula in your first post, and then you put that formula on the report, and the value that was displayed on the report appeared to be rounded.

 But if there is no Number tab in the field format window, then that field is not numeric, but result of the formula that you posted _is_ numeric.  So either you're looking at the format for a different field, or there is more to your formula than what you posted here.  If you're looking at a different field, how is it related to the formula that you posted?  If there is more to your formula, can we see the rest of it?

 James
0
 
LVL 100

Expert Comment

by:mlmcc
ID: 39891852
Agree.  If you don't see the number tab then the field is not numeric.  Your formula probably has either a ToText or CStr that converts the number to text.  Generally you can control the conversion with a format string or number of decimal places.

Perhaps the formula is being used in another formula for display.

mlmcc
0
 
LVL 1

Author Comment

by:TBSupport
ID: 39893057
I really don't think that it has any bearing.  But, the formula that I posted is called "Unit Weight" and is embedded in the following formula on the report:

"Unit Weight: " & {@Unit Weight} & " "&{@pounds and kilos}

Again, though, it doesn't matter.  There should be someway to have the Unit Weight formula be ".127" instead of ".13".  I can't believe that something that should be very simple is turning into something hard.

TBSupport
0
 
LVL 100

Expert Comment

by:mlmcc
ID: 39893100
The problem is the formula is turning the number into a string and using the default formatting which is probably set to 2 decimals


"Unit Weight: " & CStr({@Unit Weight},3) & " "&  CStr({@pounds and kilos},3)

The 3 is the number of decimals to use

You can also use a format string


"Unit Weight: " & CStr({@Unit Weight},"0.000" & " " & CStr({@pounds and kilos},"0.000")

mlmcc
0
 
LVL 35

Expert Comment

by:James0628
ID: 39893505
Like mlmcc said, the problem is that you're converting the formula result to a string and letting CR use the default format, and it's defaulting to 2 decimal places.  The formula that does the calculation is not rounding the result to 2 decimal places.  It's the conversion to a string that's doing that.  Changing the format that's used to create the string should take care of it.

 James
0
 
LVL 1

Author Comment

by:TBSupport
ID: 39893529
Hello:

I figured this out.  I took the formula that I posted out of the formula that it was originally placed in.

Then, I took the formula that I posted, right-clicked on it, chose "Format Field", choose the Number tab, and then clicked the "Customize" button.

Finally, I chose three decimal places and three places for rounding.

That did it.

Thanks, for your help!

TBSupport
0
 
LVL 35

Expert Comment

by:James0628
ID: 39893848
Glad we were finally able to get it sorted out.


 mlmcc,

 Are you OK with the point distribution?  There was a lot of discussion while we were trying to figure out what was going on.

 James
0

Featured Post

Announcing the Most Valuable Experts of 2016

MVEs are more concerned with the satisfaction of those they help than with the considerable points they can earn. They are the types of people you feel privileged to call colleagues. Join us in honoring this amazing group of Experts.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Hot fix for .Net Crystal Reports 10.2.3600.0 to fix problems with sub reports running on 64 bit operating systems ISSUE: Reports which contain subreports fail with error "Missing Parameter Value" DEPLOYMENT SERVER OS: Windows 2008 with 64 bi…
There have always been a lot of questions related to when Crystal Reports evaluates report components (such as formulas, summaries, cross-tabs, charts, to name a few examples). Crystal Reports uses a two-pass reporting process to provide greater …
A short tutorial showing how to set up an email signature in Outlook on the Web (previously known as OWA). For free email signatures designs, visit https://www.mail-signatures.com/articles/signature-templates/?sts=6651 If you want to manage em…

710 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question