Change field data type for export to Excel

[elsenorjose]

I am using CR XI R2 against a SQL Server 2000 backend. A field in one of the tables in my report is typed NVARCHAR (5) and can have numeric values, text values, or NULL. The column is a scoring column where a user can enter a 1-10 rating, leave blank, or enter some text such as 'NA'. We export this report to Excel for further analysis. The end user has asked if we can format the field to numeric so they don't have to do it in Excel. Is there a way to write a formula that can accomplish this? I've tried "If NumericText ({field}) then ToNumber ({field}) else {field}" but I get an error highlighting the field name after "else" stating "A number is required here". The end result should be that once the data is exported, Excel sees the field as Number and not String.

Thank you.

 

[hilfy]

I don't think there's any way to do this. Your formula won't work because the results of "then" and "else" need to be the same data type - your formula returns either a number or a string and Crystal doesn't know how to handle this.

-Dell

DecisionFirst Technologies - Seven-time SAP BusinessObjects Solution Partner of the Year

http://www.decisionfirst.com

 

[fisheromacse]

i think you would have to replace any data that is not numeric.
You could replace the field value with an out of range number (ie: your accepted number values are 1-10, so use 99)
//[@SampleFormula}
If NumericText ({field}) then ToNumber ({field}) else 99

You could use a second formula elsewhere to display any 'comments' data (which would be the next thing my users would be asking for, hehe).
//[@SampleFormula2}
If NOT(NumericText ({field})) then ({field}) else ""

 

[elsenorjose]

Thanks Dell. I thought so. User was OK with just replacing NA with 0 so I was able to use the formula after all.