Thursday, 22 May 2014

Adding ‘All’ in JasperReport input control query and passing 'All' as parameter




In this post you will see how to append ‘All’ keyword in JasperReport input control query and passing 'All' as parameter.

Now the question is where we can use this trick in report, I saw a question in forum where user wanted to append ‘All’ with other input control values so user can also select ‘All’ to select all the values of that input control instead of selecting manually  all the values.

In the case of multiselect parameter of Collection type (java.util.Collection)  you do not need to give "All" to select all the values in iReport as default value for that parameter by default it means all the values are selected.

But if the parameter is single select of String type then the query for input control will be: -

SELECT *
  FROM (SELECT 'All Country' SHIPCOUNTRY FROM orders
        UNION
        SELECT DISTINCT SHIPCOUNTRY FROM orders) b
ORDER BY SHIPCOUNTRY
 
iReport query will be : - 
 
SELECT SHIPCOUNTRY,SHIPCITY
FROM orders
WHERE ($P{p_shipcountry}='All' OR SHIPCOUNTRY=$P{p_shipcountry})
 
Where p_shipcountry is a parameter in iReport and it is single select of String (java.lang.String) type parameter.

No comments: