BIRT: How to include NULL values in crosstab average function

Question

When creating aggregations on crosstabs, BIRT ignores NULL values. It leads to incorrect values for AVERAGE. How can I replace NULL with zero. My data is from a stored procedure query. Thank you !

Leo

Answer 1

So you want the average of [4,null,null,null] to be 1? I think BIRT's calculation 4 is correct instead of this.

But anyway, you could a computed output column XXX_nvl0 to your dataset which is computed as

( row["XXX"] == null? 0.0 : row["XXX"] )

Then compute the average of XXX_nvl0 instead of XXX.