Enabling users to specify null

Jenkinsj5

I am using BIRT 4.2 and deploying via Apache. My SQL education, is what I have picked up from modifying drag and drop tools.<br />
<br />
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 (<em class='bbc'>me</em>) to un-check 'is required' when creating the parameter. I am using a dynamic list for the parameter.<br />
<br />
As best I can tell it actually sends "Null" as a string, and does not return the nulls, when the SQL is..<br />
<br />
<span style='color: #4B0082'>where</span> METADATA_DOMAIN <span style='color: #4B0082'>= ?</span><br />
<br />
I can get any value other then null, regardless of the check or un-check 'is required' <br />
<br />
<br />
When the SQL is <br />
<br />
<span style='color: #483D8B'>where</span> METADATA_DOMAIN <span style='color: #4B0082'>= ?</span><br />
<span style='color: #483D8B'>or</span> METADATA_DOMAIN <span style='color: #483D8B'>is null</span><br />
<br />
I can get any value and nulls, regardless of the check or un-check 'is required'<br />
<br />
Presumably I need to use something other than " <span style='color: #4B0082'>= ?</span> " in the SQL when passing either a " <span style='color: #4B0082'>is null </span>" command or string value. I looked around and don't find the answer so I guess it is really simple. (<em class='bbc'>or I just did not understand the answer when I saw it</em>)

kclark

Hi Jenkins! Would you mind creating a report with the sample DB for me?

Jenkinsj5

Here you go, 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.

Jenkinsj5

Missed hitting the "add this file" button

Clement Wong

Jenkins,<br />
<br />
You may want to read this Wiki entry about passing null to a parameter:<br />
<a class='bbc_url' href='http://wiki.eclipse.org/Null_Report_Parameter_Passed_to_Data_Set_(BIRT)'&gt;http://wiki.eclipse.org/Null_Report_Parameter_Passed_to_Data_Set_(BIRT)&lt;/a&gt;&lt;br />
<br />
Attached is your sample report modified.

Jenkinsj5

Thank you both, I found a solution that is fairly simple. Works on complex reports, (my production report has multiple linked tables and mutliple paramters). 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
* Computed column generates integer value based on Null value
* Filter based on parameter to include or exclude Nulls.

I have attached a simple report using the sample DB

kclark

Glad you got it working! If you have the time it would make a good devshare

Jenkinsj5

Devshare posted <a class='bbc_url' href='http://www.birt-exchange.org/org/devshare/designing-birt-reports/1580-enabling-report-users-to-specify-null-values-with-dynamic-drop-down-list/'>here</a&gt;