Discussions
Categories
Groups
Community Home
Categories
INTERNAL ENABLEMENT
POPULAR
PUBLIC CLOUD
PRIVATE CLOUD
Quick Links
MY LINKS
HELPFUL TIPS
Back to website
Home
Intelligence (Analytics)
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>)
Find more posts tagged with
Comments
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)'>http://wiki.eclipse.org/Null_Report_Parameter_Passed_to_Data_Set_(BIRT)</a><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>
;
