Null formula return

[Kebabmeister]

CR v8.5
Oracle DB

This formula returns a null value perhaps 80% of time. Cannot see what is wrong. HELP!!!

Shared NumberVar Costs;
Local NumberVar Line;

If {WORKORDER.ACTFINISH} >= {?Pm-?Lower} and {WORKORDER.ACTFINISH} <= {?Pm-?Upper} Then
(
If {WORKORDER.ACTLABCOST} > 0 Then
Line := {WORKORDER.ACTLABCOST}
Else
Line := {SERVTYPE.SC2};
)
Else
Line := 0;

Costs := Costs + Line;

Line

 

[synapsevampire]

Check for the existence of nulls where applicable, for instance:

If {WORKORDER.ACTFINISH} >= {?Pm-?Lower} and {WORKORDER.ACTFINISH} <= {?Pm-?Upper} Then
(
If isnull({WORKORDER.ACTLABCOST})
or
{WORKORDER.ACTLABCOST} <= 0
Then
Line := {SERVTYPE.SC2};
Else
Line := {WORKORDER.ACTLABCOST}
)
Else
Line := 0;

You may need to check other fields as well, if the formula encounters a null, it will fall out and return null.

One way around this is to select File->Options->Convert Null Values to Default

-k

 

[Kebabmeister]

Thanks for the suggestion, it was my first thought also, but been there and done that.

 

[synapsevampire]

OK, then let's get some real technical information.

First, are you resetting this formula in the subreport prior to executing the subreport?

Where is the subreport within the main report?

Did you try testing for nulls everywhere?

If you had reset the shared variable first and then turned off nulls, then it couldn't return a null, so you need to be more thorough when being there and doing that.

If you have tried another formula which checks all columns for null, please post it, otherwise pleas do so.

-k

 

[Kebabmeister]

Unsure what u mean by 'reset'. The shared variable is an iterative value which is only reset at each new instance of the sub.

Subreport is in the detail section of the main report.

I am running the report on limited test data which I have validated to ensure no nulls.

I have tried commenting out the shared variable lines so that the formula is completely self contained and the outcome is the same.

 

[synapsevampire]

Reset means that in a section prior to running the subreport, use a formula such as:

Shared NumberVar Costs:=0;

-k

 

[Kebabmeister]

Okay.

No it doesn't reset. The variable is used across a number of subs and then feeds baqck into the main report. However the shared variable doesn't affect the formula field's output.

Interesting fault huh!