| Author |
Message |
Nav522
Senior Member
Joined: 25 Aug 2009
Location: United States
Online Status: Offline
Posts: 166
|
  
Topic: Fee showing values asText instead of Numbers
Posted: 11 Sep 2009 at 11:51am |
Hello Folks,
I need this urgently, I have a crystal report 8.5 with data, which i have exported to an Excel sheet. Now the problem is that the Fee column is showing Text instead of Numbers in Excel. Is there any way i can modify the Fee column in crystal report so that its not a problem when lookin at the Excel sheet.
I was wondering whether we can use To Number() or anyother stuff like that.
Thanks,
Nav Edited by Nav522 - 11 Sep 2009 at 3:33pm
|
 IP Logged |
|
|
|
Jyothi Yepuri
Senior Member
Joined: 11 May 2009
Location: Australia
Online Status: Offline
Posts: 127
|
Posted: 13 Sep 2009 at 7:23pm |
|
Is Fee Column is a string type in database?
create a formula to convert fee column using tonumber or CDbl.
HTH,
Jyothi
|
| IP Logged |
|
Nav522
Senior Member
Joined: 25 Aug 2009
Location: United States
Online Status: Offline
Posts: 166
|
Posted: 16 Sep 2009 at 12:02pm |
Hi Jyothi Thanks for getting back to me. I didnt got what i want, probably i shud have explained a bit more, my bad.
Well here it is, The Fee column in the database is Numeric, no problems with that as it is asn Integer. In the Report Design we have specifically inserted a TextBox and then dragged this Fee(numeric) field into the Textbox eventually making it as a text when exporting it to the Excel Sheet.
So If anyone could please let me know what exactly shud i need to do to get this textbox converted to Numeric. Is there a way that i can edit the properties of this Textbox or something like that.Thanks for the help
Nav
|
| IP Logged |
|
DBlank
Moderator
Joined: 19 Dec 2008
Online Status: Offline
Posts: 9053
|
Posted: 17 Sep 2009 at 7:25am |
I am going to take a guess that the only reason you are going from an int to text back to an int is because you are trying to format it to look a certain way.
Rather than doing this as text leave it as an int and use the formating functionality within crystal.
Right click on the int field and use the Format Editor and NUmber tab to inlcude your choice of currency symbol and other formating. This allows you to export it as numeric.
|
| IP Logged |
|
Nav522
Senior Member
Joined: 25 Aug 2009
Location: United States
Online Status: Offline
Posts: 166
|
Posted: 17 Sep 2009 at 3:31pm |
|
Hey Thanks for the reply man. Well ur answer clarifies it but is there anyway that i can write a formula in the function editor of the textbox so that the textbox is converted to integer?
I have tried it like this nd hav a doubt whether i have provided the formula in the correct place. I have written the formula astable.fieldname = Tonumber (table.fieldname) in the format button beside the Suppress checkbox in formula editor of the textbox, and am not sure whether i did the rite thing. please thorw some light on this.
Yes the reason they are using textbox instead of a direct field is that to make sure the number comes out and looks in this format ($1234.00) meaning they just enclosed the Fee field within the brackets() for a certain purpose.
|
| IP Logged |
|
DBlank
Moderator
Joined: 19 Dec 2008
Online Status: Offline
Posts: 9053
|
Posted: 17 Sep 2009 at 5:11pm |
You cannot convert in a text object, you do that in a formula field.
YOur formula in this case would likely be:
ccur(talble.field)
However you will still need to use the format object to get it to look like you want it to.
I'll have to look tomorrow and see about the (xxxx) format but i think that is ussually indicates a debit instead of a credit. YOu want it applied to the field for every row regardless, correct?
|
| IP Logged |
|
Nav522
Senior Member
Joined: 25 Aug 2009
Location: United States
Online Status: Offline
Posts: 166
|
Posted: 17 Sep 2009 at 7:54pm |
|
yes i want to apply to the whole field. If i understood correctly u mean to say that we cannot convert a textbox to a Numeric integer in crystal reports?
Edited by Nav522 - 17 Sep 2009 at 8:00pm
|
| IP Logged |
|
DBlank
Moderator
Joined: 19 Dec 2008
Online Status: Offline
Posts: 9053
|
Posted: 18 Sep 2009 at 7:33am |
You can convert a text field to an integer (assuming the values are numeric characters). YOu do not convert text boxes.
You may want to rethink your process or really the design specs here though. I am not sure why your end user is asking for an int value that must be formatted as (xx.xx) that will be exported to a spreadsheet that would interpret that as a negative value. If what they really want is a negative value then you can use a fomrula field to convert the original DB int value to a negative currency amount and format it with the () which can be exported as a number field to excel.
You can quickly turn 12345.87 into ($12,345.87) by creating a formula field and using the formula of:
ccur({table.int_field}) * -1
However be aware that it is making all posittive values negative and all neg values positive.
Does that make sense?
|
| IP Logged |
|
Nav522
Senior Member
Joined: 25 Aug 2009
Location: United States
Online Status: Offline
Posts: 166
|
Posted: 18 Sep 2009 at 3:53pm |
|
Yes man this makes sense.Thanks i really appreciate it.
|
| IP Logged |
|
|
|
