How do I show blank entries on a BIRT Table sub-group?

Question

Hi, I am trying to create a table with two groups in a BIRT report.

I first group by year, and then by a criteria in a second column. Let's say this criteria is one of [A, B, C, D]. If there doesn't exist a criteria for a year, the default in BIRT is for it to be blank. For example, if 2011 didn't have any B or D criteria, my report would look like:

2010
----
A 1
B 2
C 3
D 4

2011
----
A 5
C 6

However, I want all the possible criteria to show up, even if they don't have any entries for a particular year.

I tried setting the property under advanced->section->show if blank = true, but that didn't do anything.

Any ideas?

(I am using birt 2.6.0)

The SQL query (connecting to a mysql datasource) is fairly simple:

SELECT year_field, decision_field, sales_field
FROM databaseName

The report is http://bit.ly/9SDbNI

And produces a report like:

Answer 1

I noticed that you are using MySql. Use SELECT IFNULL(,0) from my_table; to replase the null values with 0;

Answer 2

As I commented earlier, this is a dataset issue, not a BIRT issue. The issue is that the dataset does not include rows where there were no sales for those decision codes, in those years.

I was rather hoping there would be separate tables for the years and decision codes, but it looks as though there's a single table for everything. Therefore, I suggest the following query (based on the query in the rptdesign file, rather than the question):

select y.year_field, d.decision_field, t.sales_field
from
(select distinct year_field from databaseTable) y
cross join (select distinct decision_field from databaseTable) d
left join databaseTable t 
on y.year_field = t.year_field and d.decision_field = t.decision_field

Also, change the definition of the Count column to be a count of the sales field, rather than the decision field.