lanier3532

asked on

# 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!

(i.e. 4.567 would display 4.567, 4.560 would display 4.56, 4.500 would display 4.5)

thanks!

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

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

mlmcc

ASKER

It is a Qty field in the database (double). it can be a formula on the report.

Did you try my suggestion?

James

James

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

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

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

mlmcc

ASKER

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.

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.

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.

mlmcc

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)
```

mlmcc

ASKER

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...

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

ASKER

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?

"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

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

James

ASKER

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)

Local NumberVar places;

places := 0;

while Truncate (CurrentFieldValue * 10 ^ places) <> CurrentFieldValue * 10 ^ places do

places := places + 1;

CStr(CurrentFieldValue , places)

ASKER CERTIFIED SOLUTION

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

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

mlmcc

ASKER

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

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

James

ASKER

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

thanks again

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

James

James

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:

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