Issue/Symptom/Question

I created a formula column in Workbench.  It calculates values for some rows but then leaves the rest of the rows blank. 

Example formula column:

Discount Delta = [Discount Available - Invoice Currency] - [Discount Taken - Invoice Currency]

Highlighted values are calculated, but the next row is blank.

Applies To

Workbench 

Formula columns

Columns with NULL values

Resolution/Fix/Answer

1. Create a formula column that converts the NULL values to 0.
   1. In example above the formula column would convert the NULL values in [Discount Taken - Invoice Currency] to 0.
2. Formula should be IIF(IsNumeric([column name]),[column name],0)
   1. In the example above the formula would be Calculated Discount Taken = IIF(IsNumeric([Discount Taken - Invoice Currency]),[Discount Taken - Invoice Currency],0)
   2. This formula works by first determining if the column is numeric (not NULL).  If it is numeric then the formula uses the value of the column.  If the column is not numeric (NULL) then it replaces it with 0.
3. By default, the column created in step 2 will be added to the report.  Uncheck the box next to that column name to exclude it from the report.  It does not need to be shown.
4. Use the column created in Step 2 to replace the original column in your formula.
   1. In the example the new column would be Discount Delta = [Discount Available - Invoice Currency] - [Calculated Discount Taken]

If the formula seems to be resulting in no value, make sure the referenced columns are all of the same data type. This can be done by casting them to doubles using CDbl(). For example IIF(IsNumeric([column name]),CDbl([column name]),CDbl(0))

Cause

The functions used for formula columns are VB code and do not handle NULL values.  

An error occurs when the formula tries to use a NULL value and after a specified number of errors the formula stops working so it doesn't slow down the report.