Sorry I’m exporting from Crystal Reports to excel. But I’m needing the logic written inside Crystal reports so when i export it to excel it is already formatted the way I need it.
Unfortunately when exporting to Excel. Excel looks at the first few values and determines what it thinks the data type is. I have yet to figure out how Excel does this. Maybe someone else knows.
My issues is within Crystal Reprots not excel. I’m trying to write the code within Crystal Reports so when my report runs it converts a string data that is a number to values and text stays the same. It can get half of it I just can’t figure out the 2nd half.
for example:
Ord Number
123
C5654
I want the 123 to be converted a value and I want C5654 to stay as a text.
If Not isNull({ordnum}) Then
If isNumeric({ordnum}) Then
ToNumber({ordnum})
Else 0
The above converts the numbers to a value correctly, but it changes the C Ord numbers to 0 instead of keeping the original.
I tried using this instead, but it doesn’t like the last line it says it needs a value instead.
If Not isNull({YourStringField}) Then
If isNumeric({YourStringField}) Then
ToNumber({YourStringField})
Else {ordnum}
The problem then is that you can have only one data type per field (expression). So you can either have the expression return a number or a string (of course other data types are available, but not relevant in this situation).
I agree with kevlray said but one thing you could try, as long as you only have two expected output types. My thought is something like this:
Formula 1: Checks for numeric value and returns the value as a number otherwise returns a 0. If zero is a valid number us an invalid number like -9999. [Return type numeric]
Formula 2: Checks for a non-numeric number and returns the values as a string. Otherwise returns a blank. [Return type string]
In this way you have two formulas so you can have two different output types. Now you set the formulas up to suppress if they have certain results. Formula 1 should suppress if it returns a zero (or whatever invalid number you choose). Formula 2 should suppress if it returns a blank.
In the designer if you place these two formulas on top of each other and make sure they are the same size they will produce either a number or string depending on the input value. I’m not sure how Excel will handle this but it should get you where you want to go in Crystal.
I did not think of that solution (even though I should have, I have some something similar in the past). Excel should be find, it will only see ‘one data’, how it interprets the data is yet to be seen.