Print Page | Close Window

Decimal values in text format from db

Printed From: Crystal Reports Book
Category: Crystal Reports 9 through 2020
Forum Name: Tips and Tricks
Forum Discription: Have you learned some great tricks to share with the group? Post them here!
URL: http://www.crystalreportsbook.com/forum/forum_posts.asp?TID=22949
Printed Date: 20 Apr 2024 at 2:43am


Topic: Decimal values in text format from db
Posted By: DDSJ
Subject: Decimal values in text format from db
Date Posted: 23 Aug 2021 at 1:20am
In my crystal report ,I am trying to display 'N/A' if field of datatype number has null value fetched from stored procedure else the value has to converted to text as shown below. If not converted to text it is throwing error but if converted to text then decimal value gets round off. I want to display exact decimal values as in database. Below is the code written in formula for field value. How to display exact decimal value in this condition where 'N/A' is also required in null case.
In formula :

if isnull (Field.VALUE}) then "N/A"
else (totext({Field.VALUE}))

eg : if value is 12.345 it displays 12.35

required value : 12.345
Different values will have different decimal places, not specific to any fixed decimal place.


Replies:
Posted By: kevlray
Date Posted: 23 Aug 2021 at 4:18am
Look at the ToText optional parameters
ToText (x, y, z, w)

  • x is a Number or Currency value to be converted into a text string; it can be a whole or fractional value.

  • y is a whole number indicating the number of decimal places to carry the value in x to (This argument is optional.).

  • z is a single character text string indicating the character to be used to separate thousands in x. Default is the character specified in your International or Regional settings control panel. (This argument is optional.)

  • w is a single character text string indicating the character to be used as a decimal separator in x. Default is the character specified in your International or Regional settings control panel. (This argument is optional.)


Posted By: lockwelle
Date Posted: 23 Aug 2021 at 5:32am
given your requirements that the number of decimal places changes, randomly, I do not see a solution.

As you have noticed, Crystal needs both parts of a IF clause to return the same type...numeric, date, boolean, or string.

the only thought that comes to mind is not a pretty to code, and would be something like this...and there is probably a more elegant solution as well...

if totext({field.value}, 1, "") + "0" > totext({field.value}, 2, "") then
if totext({field.value}, 2, "") + "0" >
    totext({field.value}, 3, "") then


to however many places you want. It won't be pretty...

It is a thought


Posted By: lockwelle
Date Posted: 23 Aug 2021 at 5:32am
the else would be the totext with the lower decimal places. forgot that part

Posted By: DBlank
Date Posted: 27 Aug 2021 at 11:53am
Since this is just a display issue can also place two fields on top of each other, one numeric and the other a text field that is "N/A" and conditionally suppress each field around the null value of your original field...assuming the original number field was displaying to your liking.

Posted By: lockwelle
Date Posted: 31 Aug 2021 at 9:56am
that would work too...at for the N/A part.
Not sure about the part for different lengths of decimals. You would need something else to know how many decimal places to go.


Print Page | Close Window