How to make BIRT display null values?

Question

I have a situation in BIRT reporting. The report I created insists on displaying some fields as empty, not null. The situation arises when the field is actually a sub-selection that does not return rows.

So, for example, if the selection includes:

0 as p3, 

then 0. If, however, the selection has:

 (select sum(other_field) from other_table where ...) as p3, 

the field is displayed blank.

Changing the data so that the rows exist for the sub-results in the displayed value, even if their resulting value is zero.

So, I think that somehow BIRT handles the subselection, returning the null rows as NULL (which also appears as an empty cell), rather than zero. Does anyone know how to get BIRT to display the actual 0, not an empty cell?

I use DB2 / z v8 if someone needs to post a specific answer to the DBMS, although even recommendations based on other vendors will be appreciated.

Answer 1

Try using the COALESCE function to force a value when a column or expression can return NULL.

  COALESCE ((select sum (other_field) from other_table where ...), 0) as p3,

Answer 2

Another way, although not so elegant, but sometimes necessary (for example, if you are executing a stored procedure that you cannot change), would use JavaScript to force the display of "0" at runtime.

Use the following expression to bind data:

 if (dataSetRow["your_data_set"] == null) { 0 } else { dataSetRow["your_data_set"] } 

Depending on the type of data, you may need to add quotation marks to zero.

 "0" 

to represent strings without quotes, it is considered as a whole.