Please start any new threads on our new
site at https://forums.sqlteam.com. We've got lots of great SQL Server
experts to answer whatever question you can come up with.
| Author |
Topic |
|
Grifter
Constraint Violating Yak Guru
274 Posts |
 Posted - 2011-05-31 : 14:26:42
|
| HiI have a crystal report that displays a float value from the sql server database. I have been given a requirement to show two zero values on any value that does not have a floating point on it, that is a value that is effectively a whole number. So if a value is encountered like:100 Then it should be displayed as 100.00Any other values (100.01, 100.1) should remain as is.I have been racking my brains but unsure how to do this?Crystal does not let you customise the display, I think it only lets you manipulate number values, and even if it did it would not show two decimal point zeros (on a value such as 100) unless the data already had that on it.Any suggestions?ThanksG |
|
|
jimf
Master Smack Fu Yak Hacker
2875 Posts |
Posted - 2011-05-31 : 14:30:45
|
| eclare @str floatset @str = 100SELECT str(@str,6,2)JimEveryday I learn something that somebody else already knew |
 |
|
|
Grifter
Constraint Violating Yak Guru
274 Posts |
Posted - 2011-05-31 : 15:25:54
|
| I guess that if a realy long value were encountered over the value in the STR function it would chop off the decimal places at the end.I created this and I think this will always take into account the length of the number being pulled from the DB:begindeclare @str floatset @str = 37055.53453534534564656564564select LTRIM(str(@str, len(37055.53453534534564656564564), 2)), @strendFor some reason doing a LEN function on the @str variable does not work - keeps showing value 7? But it seems to work in my query with the DB value ok. |
|
|
|
Grifter
Constraint Violating Yak Guru
274 Posts |
Posted - 2011-05-31 : 15:36:42
|
| In fact it doesn't work. Can anyone suggest a beter way to do this? Here is a list of values and what I am trying to do:100 = 100.00100000000 = 100000000.00100.01 = 100.01100.1 = 100.101234.45677 = 1234.45Basically fill out a whole number with 2 zero decimal places .00, and fill out any decimal places up to two values with a zero .1 = .10, otherwise limit to two decimal places if value already there .1234 = .12G |
|
|
|
sunitabeck
Master Smack Fu Yak Hacker
5155 Posts |
Posted - 2011-05-31 : 15:44:56
|
You can do it if you cast it as string in SQL as shown below, but it would be much preferable to do this formatting in Crystal. I am not familiar with Crystal, so I don't have any suggestion on how to do that in Crystal. The disadvantage of what I am showing below is that you are casting the number to a string - with the associated disadvantages; you will lose the ability to do numerical sort in your client application for example.CAST( CAST(100 AS DECIMAL(18,2)) AS VARCHAR(18)); |
|
|
|
Grifter
Constraint Violating Yak Guru
274 Posts |
Posted - 2011-05-31 : 16:18:13
|
| HiNot really done much programming in crystal. If I cast as string then I would need to convert back to do the STR function too. It seems like a lot of messy programming just to do this!I could just do as jim suggested but make the six value whatever the max length of the field is then ltrim it.G |
|
|
|
robvolk
Most Valuable Yak
15732 Posts |
Posted - 2011-05-31 : 16:34:07
|
| Crystal Reports, among others, can format numbers and dates independently of their value. You'd want to look for Number format or similar terminology in the report designer, or on the Properties panel for the relevant text box. You can easily set it to display 2 decimal places, but it will always show 2 regardless of value. |
|
|
|
|
|
|
|
|
Float Value Question