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

edited November 2013 in DevShare Downloads
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.



Examples are links to the downloads in http://www.birt-exchange.org/org/forum/index.php/topic/28498-enabling-users-to-specify-null/



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.



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.



I have one parameter with a drop down list and another parameter with Radio Buttons to include null or not.

* 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}


* Filter based on parameter to include or exclude Nulls.

row["[color=#0000ff]StateNull[/color]"] <= (i.e. “Less than or equal” in filter selection box) params["[color=#0000ff]StateNull[/color]"].value



Here is a simple report using the sample DB specify null_Solution.rptdesign (48.73K)



The SQL in my production report is
Where SCRELATIONM1.SOURCE is null
and OPEN_TIME &gt;= ?
and OPEN_TIME &lt;= ?
and &#91;OPEN] like ?
and INCIDENTSM1.AFFECTED_ITEM = ?
and ( METADATA_DOMAIN in (?)
 or METADATA_DOMAIN is null )
Silence Bestows Consent
Sign In or Register to comment.