Crystal Report Tonumber Error
Contents |
SAP Crystal Reports, version for EclipseWhere is this place located?All Places SAP Crystal Reports, version for Eclipse 6 Replies Latest reply: Feb 5, 2010 6:45 PM by jennifer carneiro Tweet ToNumber - Crystal XI - convert string to number syntax jennifer carneiro Feb tonumber in crystal report formula 4, 2010 9:16 PM Currently Being Moderated i have a field which is a crystal reports tonumber no decimals string in the database, but i want to covert to a number so that i can format decimals etc.i have tried crystal reports tonumber decimal places toNumber {table.field} but I get "the string is not numeric" - so not all are numeric or there are nulls?then i tried the If NumericText ({table.field}) Then ToNumber ({table.field})Else 0but I get unwanted 0 showing up crystal reports tonumber the string is non numeric on the report. i have triedIf NumericText ({table.field}) Then ToNumber ({table.field})Else ({table.field})but then I get "a number is expected here"So what is the most efficient way to achieve this? i've read the posts about similar questions but not getting the answer that works for me. thanks for all help! Jenn 25119Views Re: ToNumber - Crystal XI - convert string to number syntax Darren Jackson Feb 5, 2010 11:08 AM (in response
Crystal Reports Tonumber Function
to jennifer carneiro) Currently Being Moderated Jenn, Are you not able to do an data manipulation on the database side, before it gets to the report?Just an option.Darren Alert Moderator Like (0) Re: ToNumber - Crystal XI - convert string to number syntax jennifer carneiro Feb 5, 2010 2:47 PM (in response to Darren Jackson) Currently Being Moderated Hi Darren,Would that I Could. I'm afraid it is not an option for me. I have no access to the database tables.JC Alert Moderator Like (0) Re: ToNumber - Crystal XI - convert string to number syntax Darren Jackson Feb 5, 2010 5:46 PM (in response to jennifer carneiro) Currently Being Moderated Jenn, What do you want to return if the value is not Numeric? Your first bit of formula should give you what you are wanting: If NumericText ({table.field}) Then ToNumber ({table.field}) Else 0If you place an un-formatted version of {table.field} next to the above field, what are its values when "0" is returned.This will give you an idea of what items are in the database that are causeing the NumericText to fail.Maybe try IsNumeric(str) and see if that is different.Maybe check the database locale settings compared to the CR settings, if decimal indicators are different you might get is
here for a quick overview of the site Help Center Detailed answers to any questions you might have Meta Discuss the workings and policies of this site About Us Learn more about Stack Overflow the
Crystal Reports Tonumber Format
company Business Learn more about hiring developers or posting ads with us Stack Overflow Questions convert string to number in crystal report formula Jobs Documentation Tags Users Badges Ask Question x Dismiss Join the Stack Overflow Community Stack Overflow is a community of 4.7 million crystal reports convert string to number without decimals programmers, just like you, helping each other. Join them; it only takes a minute: Sign up Convert string to number field up vote 6 down vote favorite 2 I have a Database field whose datatype is String https://scn.sap.com/thread/1599237 in Crystal Reports. How can I convert it to a number value? crystal-reports share|improve this question edited Jul 26 at 15:41 4444 2,10051539 asked Oct 31 '11 at 12:05 Mathew Paul 29111232 add a comment| 1 Answer 1 active oldest votes up vote 21 down vote Within Crystal, you can do it by creating a formula that uses the ToNumber function. It might be a good idea to code for the possibility that http://stackoverflow.com/questions/7953435/convert-string-to-number-field the field might include non-numeric data - like so: If NumericText ({field}) then ToNumber ({field}) else 0 Alternatively, you might find it easier to convert the field's datatype within the query used in the report. share|improve this answer answered Oct 31 '11 at 12:51 Mark Bannister 34.4k32450 add a comment| Your Answer draft saved draft discarded Sign up or log in Sign up using Google Sign up using Facebook Sign up using Email and Password Post as a guest Name Email Post as a guest Name Email discard By posting your answer, you agree to the privacy policy and terms of service. Not the answer you're looking for? Browse other questions tagged crystal-reports or ask your own question. asked 4 years ago viewed 81253 times active 2 months ago Linked 0 Summarize the fields in Crystal Report Related 0How to filter a number field against a multiple string parameter or string array?7Concatenate two fields2How to convert string value to proper datetime format6How to remove comma from crystal report string and from integer field2Getting value into crystal report as integer/number-2how to convert above crystal synax to basic syntax in crystal report1Converting numeric value to English Text in Crystal Reports0crystal report from dataset in c# has a lot of spaces on string fields0Convert Crystal Reports string to Date format yyyy-MM-dd1How
1 July 2007 @ 12:16 pm I find many formulas using the ToNumber() function in Crystal Reports formulas to convert numeric text into a true numeric value. Usually it is because they don't know the advantages of using the Val() function. While ToNumber() works, http://kenhamady.com/cru/archives/81 it will generate an error if the string has any characters, punctuation or symbols in it. https://community.tableau.com/thread/131758 The usual solution is to test the value by saying: If NumericText ({field}) then ToNumber ({field}) else 0 However, even this doesn't work well if characters come after the numerics. For instance street addresses that start with numbers, or a list of part numbers like this: 11220 11220C 127 128 128P 129 2458B All the values above that end with a crystal report letter would return a zero with the formula above. The Val() function is more forgiving and will stop when it reaches the alpha character or symbol. It then returns the value of the numbers up to that point. So the list of parts above would be converted to their numeric values and all the trailing letters would be ignored. I have found only a few rare reasons for using ToNumber() instead of Val(). 1) ToNumber() can crystal reports tonumber work with Booleans and currency values in addition to strings. Val() only work's with strings 2) ToNumber() can also convert the following strings, while Val() will return zeros for both: ToNumber ( "(123)" ) becomes -123 ToNumber ( "$123" ) becomes 123 (For examples of my most popular formulas, please visit the FORMULAS page on my website.) Comments (1) Posted in Formulas,Tips Subscribe RSS 2.0 feed. You can skip to the end and leave a response. Pinging is currently not allowed. One Response to 'ToNumber() vs VAL():' Vinay - June 15th, 2011 at 2:13 am Nice work Buddy… Thanx a lot… Leave a Reply Name Email (will not be published) Website Crystal Training at Your Location! Get an experienced instructor to teach at your office using your data. www.KenHamady.com Resources Subscribe RSS 2.0 Comments RSS 2.0 RSS 0.92 Atom 0.3 Pages About the Author About this Blog Product Comparisons: Crystal Reports Viewers Crystal Reports on the Web Scheduling Engines (Desktop) Scheduling Engines (Server) RPT Management Utilities Crystal Reports User Function Libraries Reporting Tools Admin Admin Log in Blog Categories Alternate Reporting Tools (9) Application Development (21) Bugs and Errors (77) Formulas (133) Method (158) News (172) Off Topic (15) Products (170) Services (4) Tips (209) Monthly Archives By Month September 2016(3) August 2016(5) July 2016(4) June 2016(4) May 2016(5) April 2016(4) March 2016(4) February 2016
Oct 1, 2013 8:22 AM by Natalie Woo Repalce "ToText" and "To Number" from Crystal report? Natalie Woo Oct 1, 2013 7:32 AM I would like to know what syntax in Tableau can i use to repalce "ToText" and "To Number" from Crystal report?To text- convert number, date, boolean, or time values to a stringTo Number - convert a Number, Currency, text string, or Boolean value to a NumberThank you~ 3298Views Tags: none (add) This content has been marked as final. Show 5 replies 1. Re: Repalce "ToText" and "To Number" from Crystal report? Shawn Wallwork Oct 1, 2013 8:00 AM (in response to Natalie Woo) Natalie, welcome to the forums!To text = STR()There really isn't an equivalent to the 'To Number' Crystal function. Here's a list of the conversions available in Tableau:--Shawn Like Show 0 Likes(0) Actions 2. Re: Repalce "ToText" and "To Number" from Crystal report? Natalie Woo Oct 1, 2013 8:03 AM (in response to Shawn Wallwork) Thank you Shawn for your help! Like Show 0 Likes(0) Actions 3. Re: Repalce "ToText" and "To Number" from Crystal report? Natalie Woo Oct 1, 2013 8:08 AM (in response to Shawn Wallwork) Hello Shawn,I am looking at each of the function that tableau have... I cannot find anything that could convert a string to a number. Could you suggest any other ways to do so please? Thank you Like Show 0 Likes(0) Actions 4. Re: Repalce "ToText" and "To Number" from Crystal report? Shawn Wallwork Oct 1, 2013 8:15 AM (in response to Natalie Woo) If it really is a number and Tableau thinks it is a string, then in the data window (left side) right click and go down to Change Data Type and click Number:Is that what you're looking for?--Shawn Like Show 0 Likes(0) Actions 5. Re: Repalce "ToText" and "To Number" from Crystal report? Natalie Woo Oct 1, 2013 8:22 AM (in response to Shawn Wallwork) Great, than