how to trim trailing zeros in qty field - Crystal Reports 2008

lanier3532
lanier3532 used Ask the Experts™
on
I need to somehow format a Qty field to only show significant digits past the decimal point, trimming any trailing zeros.
(i.e.  4.567 would display 4.567,  4.560 would display 4.56,  4.500 would display 4.5)

thanks!
Comment
Watch Question

Do more with

Expert Office
EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®
Of course if you just remove the trailing 0's, the decimal points won't line up.  Using your examples, you'd have something like:

4.567
 4.56
  4.5

Open in new window


 Assuming that that's not a problem, you can give the following a try:

 Go into the field format, click Customize on the Number tab, click the formula (X+2) button beside Decimals and enter the following formula:

Local NumberVar places;

places := 0;

while Truncate (CurrentFieldValue * 10 ^ places) <> CurrentFieldValue * 10 ^ places do
  places := places + 1;

places


 CurrentFieldValue is a special CR function used to reference the value in the field in a formatting formula.  You could use the actual field name instead.  CurrentFieldValue is particularly handy if you want to use the same format formula for multiple fields.  Then you don't have to change the field name in each formula.

 What that formula does is multiply the field by increasing powers of 10, until the result without decimals (Truncate) matches the result with decimals.  For example, with 4.56, you'd get:

Places   Result
  0       4 <> 4.56  (keep going)
  1      45 <> 45.6  (keep going)
  2      456 = 456  (stop)

Open in new window


 So places ends up set to 2, and you get 2 decimal places in the field format (ie. 4.56).

 You'd want to enter the same formula for the Rounding part of the field format, so that CR will also round the field to the correct number of decimals.

 James
Mike McCrackenSenior Consultant
Most Valuable Expert 2011
Top Expert 2013

Commented:
WHere are you trying to use this?

Is it a field on the report or is it in a formula that produces a string?

mlmcc

Author

Commented:
It is a Qty field in the database (double).  it can be a formula on the report.
Fundamentals of JavaScript

Learn the fundamentals of the popular programming language JavaScript so that you can explore the realm of web development.

Did you try my suggestion?

 James
Mike McCrackenSenior Consultant
Most Valuable Expert 2011
Top Expert 2013

Commented:
I was really asking if you were displaying the value directly or using it in something like

"The total is :  " & CStr({MyValue})

James suggestion should work if you are just displaying the value or in a cross tab.

mlmcc

Author

Commented:
will try to modify the formula above, but it does not exactly solve my problem.  my client has their customers who do not want to see trailing zeros.  so each field could be a different length.  I'm not trying to round off and show fixed decimal places, just taking off any trailing zeros.

as per my original post:
(i.e.  4.567 would display 4.567,  4.560 would display 4.56,  4.500 would display 4.5)

in know it sounds crazy, but that's how they want it.  I am not very experienced with formulas and local variables in Crystal, so I will try to modify the formulas above.  I have some morning commitments, so after that I will work on this problem.  thanks for everyone's help.
Mike McCrackenSenior Consultant
Most Valuable Expert 2011
Top Expert 2013

Commented:
I believe you will find the formula does that.

What isn't working about the formula?
You use that in the CUSTOM FORMAT of a number  control the decimal places shown.
You might also have to change the ROUNDING so that it rounds with more significant digits than you might need like at 0.0000000001

If this is just for display then you could convert it to a regular formula.

Local NumberVar places;

 places := 0;

 while Truncate (CurrentFieldValue * 10 ^ places) <> CurrentFieldValue * 10 ^ places do
   places := places + 1;

CStr(CurrentFieldValue , places)

Open in new window


mlmcc

Author

Commented:
I am slammed right now but will get to this issue in a few hours.
Formatting to a fixed decimal is no problem.  Right click, choose how many decimals you want and viola.

what my customer wants is for a variable number of digits after the decimal with no zeros.
so in a column in the report you might see:

4.56
4.6
6.255

and so on.   just no trailing zeros...  I know its crazy, but that is what they are asking for...
what my customer wants is for a variable number of digits after the decimal with no zeros.
That is _exactly_ what my suggestion should do.  What it's doing is changing the number of decimal places in the field format, depending on how many significant digits there are.

 James

Author

Commented:
gotcha.  I see the logic in the formula, however I am getting an error when trying to save the formula:

"A number, or currency amount is required here."

and the cursor goes to the "Local NumberVar places;" statement.
the editor syntax is set to "Crystal Syntax"

suggestions?
You are using the formula for the Decimals setting in the field format, correct?

 Can you post your formula?  I used the formula that I posted on a field and it worked fine.

 James

Author

Commented:
yes, right-click on the Item_Qty field, choose Format.  Then Customize and put formula in the Decimal Formula:

Local NumberVar places;

 places := 0;

 while Truncate (CurrentFieldValue * 10 ^ places) <> CurrentFieldValue * 10 ^ places do
   places := places + 1;

CStr(CurrentFieldValue , places)
You don't want the CStr function.  That was from mlmcc's version of my formula (which was intended to convert the field to a string, rather than change the format on a numeric field).  The last line should just be places, by itself.  Other than that, your formula looks OK.

 Just to be clear, here's my formula again (the same thing I posted earlier):

Local NumberVar places;

places := 0;

while Truncate (CurrentFieldValue * 10 ^ places) <> CurrentFieldValue * 10 ^ places do
  places := places + 1;

places

Open in new window


 Also, as I mentioned in my earlier post, you should enter the same formula for Rounding in the field format.

 James
Mike McCrackenSenior Consultant
Most Valuable Expert 2011
Top Expert 2013

Commented:
The version you used is the one I suggested.  using that one you don't need to have the number on the report just the formula.  It works in a similar manner but the formatting is in the CStr rather than the number format.

mlmcc

Author

Commented:
Thank you guys for hanging in there with me thru my misunderstandings.  

I didn't pick up on the "Put the same formula in the Rounding AND Decimal formulas".
It works like a charm!

Thanks again
If you used my formula, then you should have accepted one of my posts (containing that formula) as the solution, not mlmcc's last post.  You can use the "Request attention" link to ask to have the question re-opened, and then close it again.

 James

Author

Commented:
sorry bout that.  I was just happy to have a solution.  I sent a message to "Request Attention" and asked for credit to be attributed to you (James0628).

thanks again
You're welcome.  And thanks for taking the time to correct your solution selection (not everyone does).

 James

Do more with

Expert Office
Submit tech questions to Ask the Experts™ at any time to receive solutions, advice, and new ideas from leading industry professionals.

Start 7-Day Free Trial