Tips and Tricks
 Crystal Reports Forum : Crystal Reports 9 through 2020 : Tips and Tricks
Message Icon Topic: Decimal values in text format from db Post Reply Post New Topic
Author Message
DDSJ
Newbie
Newbie


Joined: 18 Aug 2021
Online Status: Offline
Posts: 3
Quote DDSJ Replybullet Topic: Decimal values in text format from db
    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.
IP IP Logged
kevlray
Admin Group
Admin Group
Avatar

Joined: 29 Oct 2009
Online Status: Offline
Posts: 1557
Quote kevlray Replybullet 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.)

IP IP Logged
lockwelle
Moderator
Moderator


Joined: 21 Dec 2007
Online Status: Offline
Posts: 4336
Quote lockwelle Replybullet 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

IP IP Logged
lockwelle
Moderator
Moderator


Joined: 21 Dec 2007
Online Status: Offline
Posts: 4336
Quote lockwelle Replybullet Posted: 23 Aug 2021 at 5:32am
the else would be the totext with the lower decimal places. forgot that part
IP IP Logged
DBlank
Moderator
Moderator


Joined: 19 Dec 2008
Online Status: Offline
Posts: 9052
Quote DBlank Replybullet 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.
IP IP Logged
lockwelle
Moderator
Moderator


Joined: 21 Dec 2007
Online Status: Offline
Posts: 4336
Quote lockwelle Replybullet 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.
IP IP Logged
Post Reply Post New Topic
Printable version Printable version

Forum Jump
You cannot post new topics in this forum
You cannot reply to topics in this forum
You cannot delete your posts in this forum
You cannot edit your posts in this forum
You cannot create polls in this forum
You cannot vote in polls in this forum



This page was generated in 0.031 seconds.