'All' in SQL query with Jasper iReprt and passing 'All' as parameter

Hi guys..

This post teach you the usage of   'All' in SQL query in Jasper iReport.


Example:
You have the following list of states in your SQL query and you are making it as "single select Query" parameter in your input controls.

You can send any of the states as input controls by selecting that state.. If you want to select 'All' states information at a time , then how come it is possible ????

So your actual query is something like this :
 
SELECT  DISTINCT state FROM example_table  ORDER BY state

States
X
Y
Z

Use Union to append the 'All' word in your output.. now, your query should looks like some thing like below.

SELECT  'All' AS state
UNION
SELECT  DISTINCT state FROM example_table  ORDER BY state


States
All
X
Y
Z

Now, how to pass these states and 'All' word as input parameter in your main report query... ????

Your main Query should be designed in such a way that it accepts individual states and 'All' states ..
Example report Query :

SELECT
    industry,
FROM example_table
WHERE  ($P{state}='All' OR state=$P{state})
GROUP BY
industry
ORDER BY
industry

NOTE:
1) state=$P{state} where state is the name of the column and it accepts the individual states as input.
2) P{state}='All' where 'All' represents the all states information.


So in this way you can use 'All' in your iReport queries.


Sadakar
BI developer