4

I have a situation in BIRT reporting. A report I created is insisting on displaying some fields as blank rather than zero. The situation arises when the field is actually a sub-select that returns no rows.

So for example, if the select includes:

0 as p3,

then the 0 is displayed okay. If however, the select has:

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

the field is displaying blank.

Modifying the data so that rows exist for the sub-select results in a value being displayed, even if their resultant value is zero.

So I'm thinking that somehow BIRT is treating a sub-select returning zero rows as a NULL (which it also displays as an empty cell) rather than a zero. Does anyone have any idea how to coerce BIRT into displaying an actual 0 rather than an empty cell?

I'm using DB2/z v8 if anyone needs to post a DBMS-specific answer, although even suggestions based on other vendors would be appreciated.

paxdiablo
  • 854,327
  • 234
  • 1,573
  • 1,953

3 Answers3

6

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

COALESCE((select sum(other_field) from other_table where ...), 0) as p3,
Fred Sobotka
  • 5,252
  • 22
  • 32
1

Another way, although not as elegant but sometimes necessary (e.g. if your are executing a stored procedue that you cannot change), would be to use JavaScript to force the display of "0" at run time.

Use the following expression for your data binding:

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

Depending on the data type you might need to add quotes to the zero

"0"

to represent Strings, without quotes it is treated as an Integer.

m00hk00h
  • 507
  • 2
  • 7
  • 21
0

While using decimal data I was able to do a quick +0 to the field as a shortened version.

dataSetRow["your_data_set"]+0
Aaron Robertson
  • 111
  • 1
  • 5