Enabling report users to specify null values with dynamic drop down list

Jenkinsj5

Per 'BIRT A Field Guide" Third Edition, page 341; All that is required for a user to be able to search for null is for the report developer (me) to un-check 'is required' when creating the parameter. In practice it is not that simple.<br />
<br />
Examples are links to the downloads in http://www.birt-exchange.org/org/forum/index.php/topic/28498-enabling-users-to-specify-null/<br />
<br />
In the example specify null.rptdesign if you select "Null Value" no records are returned, if you select "NY" you get results. There are a bunch of records with Null in the State Field.<br />
<br />
While the solution example below is pretty simple it works well in complex reports, the production report the solution was developed for has multiple linked tables and multiple parameters. <br />
<br />
I have one parameter with a drop down list and another parameter with Radio Buttons to include null or not.<br />
* SQL returns for only selected parameters and where, field is null

select *
from CLASSICMODELS.CUSTOMERS
where ( CLASSICMODELS.CUSTOMERS.STATE = ?
	   or CLASSICMODELS.CUSTOMERS.STATE is null )
* Computed column generates integer value based on Null value
if (row["STATE"] == null){
2
}else{
1}

<br />
* Filter based on parameter to include or exclude Nulls.<br />
row["[color=#0000ff]StateNull[/color]"] <= (i.e. “Less than or equal” in filter selection box) params["[color=#0000ff]StateNull[/color]"].value<br />
<br />
Here is a simple report using the sample DB specify null_Solution.rptdesign (48.73K)<br />
<br />
The SQL in my production report is

Where SCRELATIONM1.SOURCE is null
and OPEN_TIME >= ?
and OPEN_TIME <= ?
and [OPEN] like ?
and INCIDENTSM1.AFFECTED_ITEM = ?
and ( METADATA_DOMAIN in (?)
 or METADATA_DOMAIN is null )