How to ignore zero decimal place values in Crystal Reports

Question

I'm making a report and I need precise decimal values. A number could have a value of 2 decimal places and a number could have a value of 10. If I set the decimal range to 10 places that column will always show 10 decimal places of mostly 0's. How do I ignore 0's in the value if they are present? For example I like the data to show like this:

123.24
98.234
1212.678432

instead of:

123.2400000000
98.2340000000
1212.6784320000

See my answer to [round decimal in formula CR][1] [1]: http://stackoverflow.com/questions/15462307/round-decimal-in-formula-cr — craig, Jun 24 '13 at 23:44
I don't want to do any rounding though. I just want to show the exact number and omit any extra 0's. — Icebreaker, Jun 25 '13 at 13:58

score 12 · Accepted Answer · edited Mar 22 '19 at 02:42

12

Do the following:

Right-click the "Details" textbox you want to apply this formatting to
Click "Format field"
Click the "Number" tab
Click "Customize..."
Set the Appropriate "Decimals" and "Rounding" values to reflect the DECIMAL scale of the table that feeds into this report
Click the "Decimals" formula button

Paste the following code, and make sure to change {myproc;1.col1} to reflect your procedure/column:

stringVar number := ToText({myproc;1.col1}, 8); 
while (right(number, 1) = "0") do 
number := left(number, len(number) - 1);
len(number) - InStr(number, ".");

Important:

Change the "{myproc;1.col1}" with the actual variable you are using.
The last line is a formula that dictates how many decimal place would be shown. So after removing all the excess zero, it compute it by subtracting the position of the period with the length of the trimmed string.

edited Mar 22 '19 at 02:42

Nap

8,096
13
74
117

answered Jun 24 '13 at 21:36

sion_corn

3,043
8
39
65

Thanks. I will try this in an hour and get back with my results. – Icebreaker Jun 24 '13 at 22:48
Hey I tried this out and it said there was an error in the code. It states "The field name is unknown" and it highlights this part: {myproc;1.col1} Any ideas? – Icebreaker Jun 25 '13 at 13:37
@Marino3d you have to replace that with the name of your stored procedure/column that you'd like to display in the field. – sion_corn Jun 25 '13 at 13:54
If the Stored proc name is GETPRICE for example and the column name is Price would the correct code for the first line be: stringVar number := ToText({GETPRICE;Price}, 8); because it gave me the same error? I put GETPRICE.Price in there and it gave a new error, a boolean is require here. – Icebreaker Jun 25 '13 at 14:04
1

In the Formula Editor, you should be able to expand the Report Fields tree and simply drag the field into the formula. – sion_corn Jun 25 '13 at 14:17
Thanks Alex. I was dumping the code in the wrong area. It worked great I see the correct values. Thanks a lot! – Icebreaker Jun 25 '13 at 14:20
Finally a sure-fire way to display the exact number. I changed the 8 to 10 for my purposes though, that seems to be the maximum. I was trying all kinds of Decimals and Rounding formulas. – MC10 Aug 13 '15 at 18:33
2

Yeah, I remember how frustrating this was back when I had to figure it out... best solution is to not use Crystal :) – sion_corn Aug 13 '15 at 20:19
1

Hi, I am quite new to crystal report. But what "len(number) - InStr(number, ".");" line means over here? I can understand "Len" function will return length of "number" variable and "InStr" function will return position of "." but where your are assigning value of it. Can you explain me. – Neo Aug 08 '18 at 04:36

score 7 · Answer 2 · answered Dec 30 '14 at 02:40

7

A simplest just try the code below at format filed->Number->Customize-> Decimals Code Part:

if CurrentFieldValue=Int(CurrentFieldValue)  then
    0
else
    2

The number '2' is decimal place of yours and you can replace it by your own.

answered Dec 30 '14 at 02:40

Kingzing

163
2
8

Thanks. This solved my two problem. First, this is what I needed. Second, I was looking for 'CurrentFieldValue'. – Himanshu Nov 19 '15 at 09:20

John Kopina Jr · Answer 3 · 2015-05-01T18:51:52.053

This will allow you to keep the field as a number and not have to convert to text. This solution is a formatting change at the decimal point.

•Right-click the "Details" field you want to apply this formatting to

•Click "Format field"

•Click the "Number" tab

•Click "Customize..."

Start with the largest decimal place that you want to keep and then duplicate the line backwards. For example if you know the maximum decimal position that has a value you want to keep start with that number, i.e. 1.2345600 you would start with 5. If the number is 0 you must account for this first as otherwise you will get an error

The "Field Name" is the database field name that you are wanting to change the formatting for.

If {"Field Name"} = 0 then 2 else 
if((({"Field Name"})/(truncate({"Field Name},4)))>1) then 5 else
if((({"Field Name"})/(truncate({"Field Name},3)))>1) then 4 else
if((({"Field Name"})/(truncate({"Field Name},2)))>1) then 3 else
2

Could you please elaborate more your answer adding a little more description about the solution you provide? — abarisone, Apr 28 '15 at 14:12

score 0 · Answer 4 · answered Jun 24 '13 at 21:38

0

If you are not doing any calculations on this field and just reporting it, can you feed it into your report as a string and not as a number?

answered Jun 24 '13 at 21:38

PowerUser

11,583
20
64
98

No calculation. It's pulled directly from my oracle database. I didn't create the procedure that pulls the data but I do know it doesn't manipulate the number at all. No calculation is done at the CR level either. – Icebreaker Jun 24 '13 at 21:48

How to ignore zero decimal place values in Crystal Reports

4 Answers4