I would like to Thank “Maithili” from Jaspersoft Community to push me to do this. in this blog I will be explaining how to Dynamically Change the SQL Query of a Report using Parameters.
If you are searching for a solution related to Conditional Query, I just assume that you have basic knowledge of report building using Jasper Studio without a query (A Blank Report).
Now lets begin the Condition Query Process.
1) First decide what modification you need in the SQL. For ex: I need the query to display data for country ‘Brazil’ for one condition and ‘Argentina’ for the other.
2) Hence, I created two Parameters with the
- Class as “String”
- Written SQL Query as Default Parameter Value.
- Is Prompting is False for both these Parameters
Please check the screenshots below.
3) Now Create a Third Parameter which will be user prompt Parameter. This will allow user to select a condition.
4) Now Create a Important Parameter which will change the execution of the SQL according to the condition selected by user. For Example purpose, I have created a parameter which will select Brazil code if the input value is 1 and select Argentina if input value is 2 and select all if input value is not provided.
5) Now go to the Query Window and Just add $P!{Parameter_Which_Has_the_Logic_FOr_Conditional_Query}. In My Example it would be $P!{Q_Controller}
6) Now when we preview the Report, It will ask for a parameter value as integer. I have given 1 and the expectation is it will display Brazil Data.
7) And Yes, It is displaying data for Brazil.
8) Now I will Check for the data for Argentina, just to make sure that its working.
9) And Yes, it is working
This is how you will create and modify the SQL to generate report on a condition.
Pingback: Conditional Query – Using Parameter Value in the Query to Fetch Data | Rajesh Sirsikar