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.

QQ_Img1

QQ_Img2

QQ_Img3

QQ_Img4

3) Now Create a Third Parameter which will be user prompt Parameter. This will allow user to select a condition.

QQ_Img5

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.

QQ_Img6

QQ_Img7

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}

QQ_Img8

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.

QQ_Img9

7) And Yes, It is displaying data for Brazil.

QQ_Img10

8) Now I will Check for the data for Argentina, just to make sure that its working.

QQ_Img11

9) And Yes, it is working

QQ_Img12

This is how you will create and modify the SQL to generate report on a condition.