Null Values Causing Blank Fields

[Crystalyzer]

Hi All,

I have a problem with fields that have null values in some of my formulas. What is happening is that if one field in the formula is null the result of the formula is blank (ie the field containing the formula and any formulas dependend on it are gone from the displayed report) The formula and my attempts to trap null values are as follows:

//DECLARE VARIABLES
numbervar IntWO;
numbervar DeemedGain;
numbervar PrPerCLR;
numbervar TotGL;
numbervar CLRpct;

//CHECK ENTITY AND RETURN CLR %
Select {Activity.Legal Entity}
    Case "Bessemer Venture Associates LLC":
        CLRpct := .2        
    Case "Bessemer Venture Associates V LLC":
        CLRpct := .3
    Case "BVP IV Special Situations L.P.", "BVP III Special Situations L.P.":
        CLRpct := .2/.99
    Default :
      0;

//CHECK FOR NULL VALUES IN FIELDS AND MAKE THEM ZERO
IIF(ISNULL({#Interest Rec WO}),IntWO := 0,IntWO := {#Interest Rec WO});
IIF(ISNULL({#Deemed Gains}),DeemedGain := 0,DeemedGain := {#Deemed Gains});
IIF(ISNULL({Prior_Period_CLR.Contingent Loss Reservers}),PrPerCLR := 0,PrPerCLR := {Prior_Period_CLR.Contingent Loss Reservers});
IIF(ISNULL(Sum({@Total Gain/(Loss)},{Activity.Legal Entity})),TotGL := 0,TotGL := Sum({@Total Gain/(Loss)},{Activity.Legal Entity}));

//FORMULA
IF PrPerCLR <=0 THEN
    IF (TotGL)<=0 THEN
        ROUND((TotGL - IntWO - DeemedGain) * CLRpct, 2)
    Else
        IF ((TotGL) * -CLRpct) > PrPerCLR THEN
            Round((TotGL  - IntWO - DeemedGain) * CLRpct,2)
        Else 
            PrPerCLR*-1
ELSE
    0;

Thanks in advance for any advice/help you can offer.

Thanks and best regards,
-Lloyd

 

[Crystalyzer]

It probably would help if I told you that the problem is definitely in the way I am trying to trap the null values. I know this because for a specific test case I excluded a field I knew to be null from the formula and the results were correct.

Thanks and best regards,
-Lloyd

 

[Madawc]

I'd split the formula into several separate bits of logic. Refer to them in selection. And also try commenting them out one at a time, to isolate the source of the problem if separation does not fix it.

Madawc Williams (East Anglia, UK)

 

[Crystalyzer]

Madawc,

Thanks for the reply. That's what I did in order to isolate that it was the data item (didn't matter which one) that was a null value that cause the error, however, I found a way around it. I checked the check boxes for the settings under Report Options for "Convert Database NULL values to Default" and "Convert Other NULL values to Default" which cause null values to be zero and therefore my formula worked without a hitch.



Thanks and best regards,
-Lloyd