Tip : How to Handle null values when passing nothing in Single Select Query parameter in Jasper Design Studio 6.x (Or) Get all the data of query result set when passing nothing in Jasper Design Studio 6.x

Hi Community,

In this post we will see how to handle NULL input (-- input value in single select parameter) in single select parameter.
i.e., When passing (--) input get all the values of query result set and when passing some value from selected input control get the report output for only that parameter.

Note that this example doesn't cover step by step procedure to develop a report from scratch.
Click on images to get the best view of content placed over.

Software Used to test this example : 
TIBCOJasper Design Studio 6.1.1 CE final
TIBCO Jasper Server 6.1 CE final
PostgreSQL database - foodmart database

Core part of the example 

1) Design the report as usually.

2) Create filter for your report, in this example lets create a folder for education filed
     parameter name : param_Education
     Type : java.lang.String
      Default value : I have not taken any.

3) Query
SQL Query
SELECT DISTINCT
                        education,state_province,
                       sum(total_children) "Total Children",
                       sum(num_children_at_home) "Children at home"
FROM customer
WHERE (education = $P{param_Education} OR  $P{param_Education} IS NULL)
GROUP BY
              education,
             state_province
ORDER BY
         education,
         state_province

4)
# Publish the report to the server (point out the report to use foodmart database created in jasperserver - which is not discussed in this post).

# Create the parameter in jasper server with the same id .
parameter name : Education
parameter id : param_Education
parameter type : Single Select Query

# Give the data source connection for parameter and write the query for this parameter to get the values in input control drop down.

Input control query : SELECT DISTINCT education FROM customer

# Do not select parameter as Mandatory

5) Preview of the report in Jasperserver by selecting input control as

Test 1 : Select nothing and click on Apply button ( i.e., select -- from drop down and click on apply button). 

Test-2 : By selecting an input control value

Download Example : Click Me

References :

 http://stackoverflow.com/questions/4697187/need-for-a-jasperreports-null-parameter-value-to-show-all

I hope this helps some one :-)

Did you find it useful ? Please share for community developer like us..!!!

Improvements, suggestions are welcome.

Cheers...!!!