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

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!
lanier3532Asked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

James0628Commented:
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 ConsultantCommented:
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
lanier3532Author Commented:
It is a Qty field in the database (double).  it can be a formula on the report.
Introduction to R

R is considered the predominant language for data scientist and statisticians. Learn how to use R for your own data science projects.

James0628Commented:
Did you try my suggestion?

 James
Mike McCrackenSenior ConsultantCommented:
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
lanier3532Author 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 ConsultantCommented:
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
lanier3532Author 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...
James0628Commented:
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
lanier3532Author 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?
James0628Commented:
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
lanier3532Author 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)
James0628Commented:
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

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
Mike McCrackenSenior ConsultantCommented:
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
lanier3532Author 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
James0628Commented:
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
lanier3532Author 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
James0628Commented:
You're welcome.  And thanks for taking the time to correct your solution selection (not everyone does).

 James
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Crystal Reports

From novice to tech pro — start learning today.