Our existing SQL Server 2005 db has table with a number of numeric flds containing NULLS. I am writing a bunch of custom functions in CR X that call each other and ultimately reference these NULL-ridden flds. The functions of course return NULL when they encounter a null input fld.
I have 3 approaches (listed in order of decreasing desirability) to solving this problem:
* Use the File=>Options=> Reporting => 'Convert db NULL values to defaults'. This would be the best approach since it is universal and requires no code changes, BUT checking this option seems to have no effect at all!!
* Use the builtin isnull() function to check each potentially NULL fld and substitute its default (0 for numerics). I always get an error message saying that ISNULL() can't be used inside custom functions. Functions apparently can be built only with Basic Syntax, so I haven't tried this with Crystal Syntax.
* Create a formula for every potentially null field and set it to the default if null. Then change all the function calls to use the formula instead of the database field directly. This works if the formula uses Basic syntax. It is a pain to do all this, and will increase the maintenance of these fields and possibly decrease performance.
Can anyone think of another approach or suggest why the first (and best) approach isn't working??
IP Logged
. The solution to getting the first option above to work is:
