Have a Crystal Reports formula convert numeric strings to values, but leave non-numeric blank/null
Solution 1
In the past, I've created a SQL Expression that returns a NULL:
-- {@DB_NULL}
-- Oracle syntax
(
SELECT NULL FROM DUAL
)
-- {@DB_NULL}
-- MS SQL syntax
(
SELECT NULL
)
Then I reference this field in the formula:
// {@FormulaField}
If IsNumberic({table.field} Then
ToNumber({table.field})
Else
ToNumber({@DB_NULL})
Solution 2
You can get this to work by doing the following:
- Create a new formula and just enter a number into it, then save. Crystal will now associate this formula with a numeric return value.
- Go back into the formula and delete the number and resave it. Now you have a formula that returns a null, but CR has already associated it as being a numeric formula so you can now use it anywhere you could use a numeric type.
if isNumeric({a_omgang.omg_resultat})
then toNumber({a_omgang.omg_resultat})
else {@NullNumeric}
Note that you can also use this for any other data types (including strings since an empty string is not equivalent to a null string) and it is extremely useful for using with summary functions where you just want to straight-up ignore certain rows.
LapplandsCohan
Field IT Application Specialist and RHCSA by day and hobby programmer by night.
Updated on July 09, 2022Comments
-
LapplandsCohan almost 2 years
I have a string field that mostly contains numeric decimal values, but sometimes contains values like "<0.10" or "HEMOLYSIS".
I want to use a formula to convert these numeric value strings to values, leaving non-values blank (null).
if isNumeric({a_omgang.omg_resultat}) then toNumber({a_omgang.omg_resultat})
returns 0 for all non-numeric values, which makes it hard to calculate e.g. average or mean, or to count the number of values. (The latter can of course be achieved by using a running total count with
isNumeric
evaluation formula.)Any suggestions how I can get the formula to work as I want?
Edit: I want the value to be blank (null), not just turn of visibility if non-numeric.