Translate

Monday 9 December 2013

How to write conditional query in iReport using parameters

Inside iReport creating static query that is, writing a simple static query in the the Query executor and set it up running is quite simple and too monotonous.

The real challenge arrives in actually changing the query of the report dynamically based upon some condition, this could be achieved with a simple implementation of parameters.

Following are the steps to use the conditional SQL Statements.

1. Create a parameter if you want user triggered change of the SQL Query , if not user triggered then it must be based upon the field, so that can be used too.

2. Create a different parameter which will be responsible for triggering the changed query based upon the condition.

3. Now identify the condition which is responsible for changing the query,

4. The parameter which contains the conditional SQL switching should have the property "Used as prompt" turned off and the condition should be placed in the default value.

5. Then after setting this, move onto set the Report Query Expression. Now see the dynamism of the query could be provided to the whole query or a portion of query so accordingly place the expression .The parameter containing the conditional expression should be used as the expression and it should be denoted as $P!{name of the param containing expression}
Here is a little illustration
Suppose there is a parameter called $P{TestParamSQL}, the prompt should be turned off as we are setting the value at runtime checking the value of the $P{TEST_PARAM}. 
Set the default value of the parameter as:
For a single condition 
$P{TEST_PARAM}.equals("test")
? " select SQL query"
: " else SQL select query"
For a multiple condition 

$P{TEST_PARAM}.equals("test")
? " select SQL query"
: $P{TEST_PARAM}.equals("test1")
? " SQL select query"
: $P{TEST_PARAM}.equals("test2")
?" SQL Query"
: "else SQL Query"

Now in the iReport Query executor you should give the following expression$P!{TestParamSQL} 

Do take care that the dependent parameter $P{TEST_PARAM}. should be set before the $P{TestParamSQL} else would throw a null pointer exception.
Another thing which should be kept in mind is that the query given in the quotes in the expression should be syntactically correct, that is the SQL in as the string should be correct.

Here is a running sample-

Prerequesites-
1. JRXML File
2. PDF Export

Steps to set up this Sample Report.
1. Then you must choose Sample Database in the datasource
4. Then next you need to activate the the sample database from Help(in the iReport Menu bar)---> Samples---->Run Sample Database
5. Then Run the  Report

There is a PDF export also which would show exactly how the report looks(The input value is employeeId= 2).

Please put in you queries/suggestions, I will be more than happy to know.
Cheers .. Happy Coding!!

52 comments:

  1. Hi Ankur,

    Would you please let me know how we can add a parameter in Query Executor?

    ReplyDelete
    Replies
    1. Hi,

      After you make the parameter which contains the conditional SQL or the SQL in form of string as the default value.Then open the Query Executor and on the right hand side will be listed all the parameters, you can identify the parameter and then drag and drop in the parameter in the expression space and then add the exclamation mark
      Suppose the parameter is $P{abc} so after dragging and dropping you would see the parameter and then modify it by adding $P!{abc}

      And then if the query contained in the parameter is correct you would see the fields below and you are good to go.

      If you want more clarification then I can make a video, so that you can get the method better.

      Thanks,
      Ankur Gupta

      Delete
    2. Here is what I did : I am running Postgres SQL and iReport3.0 I created $P{TEST_PARAM}. which is has NULL value then query1 will be executed and if not NULL then query2 will be executed. This TEST_PARAM is Prompted one. Then I created TestParamSQL which has following in default expression -- $P{TEST_PARAM}.equals("") ? "query1" : "query2" ... This parameter is non prompted. Then I clicked on Database (Query) icon where you can mention your query for report. I selected SQL as my query language and typed $P!{TestParamSQL} ... and try to run the report but I get empty report. I'd appreciate any help. Let me give you my number as well .. Nine Eight Eight 10 79840 .....Thanks ... JOSHI

      Delete
    3. You have followed the right procedure, have you selected the datasource as the your PostGreSQL Database, or is it Empty Datasource.

      I guess you have missed choosing the datasource correctyly.
      If you have done this already then, please check the query.

      If you still can't get it working then you can reach me through skype my skype ID is mfsi_ankurg
      mail id : ankur,gupta.aug@gmail.com

      Thanks,
      Ankur Gupta

      Delete
    4. hi Mr.ankur .Suppose the parameter is $P{abc} so after dragging and dropping you would see the parameter and then modify it by adding $P!{abc}. But how to modify $P{abc} to -> $P!{abc}?

      Delete
  2. Problem Resolved.......Thank U Ankur Gupta.....

    ReplyDelete
  3. Welcome,,, Stay tuned to my blog for more Jasper Reporting tips.!!

    ReplyDelete
  4. Dear Ankur,

    Wanted to ask if it's possible in Jasper Reporting to do the following:


    Query Result:

    ID Name
    ------- ------------
    1001 NICK
    1010 BOB
    1022 TOM

    I have text in REPORT that needs to fetch all the names,

    The creditor, Nick with id 1001, bla bla bla bla, and the Guarantor BOB with id 1010 Bla bla bla bla, and guy who vouches TOM with ID 1022, bla bla lba.

    Is it possible to fetch the second of third row of query in Variable.

    I tried like this but it doesn't work. I put this on Variable Expression:
    ($F{ID} == 1001) ? $F{NAME} : ""

    ReplyDelete
  5. hello Ankur, thank you for this blog.
    i want to add parameters as you did.
    $P{TEST_PARAM}.equals("test")
    ? " select SQL query"
    : $P{TEST_PARAM}.equals("test1")
    ? " SQL select query"
    : $P{TEST_PARAM}.equals("test2")
    ?" SQL Query"
    : "else SQL Query"

    but, my query syntax has a parameter something like that,

    ......
    : $P{TEST_PARAM}.equals("test1")
    ? " select * from query where $P{NewParameter}"
    ..............
    i add $P{NewParameter} and it does not work in query designer. because, query designer has not compile like a parameter. how can define as a parameter in your syntax?

    ReplyDelete
    Replies
    1. : $P{TEST_PARAM}.equals("test1")
      ? " select * from query where id=$P{NewParameter}"

      Delete
    2. Hi ,
      Trying out conditional parameters one thing you should keep in mind that the parameters which are created in Jasper Reports are dynamic in nature as in your case - $P{NewParameter}. On the other hand the SQL query conditionally used is static in Nature.

      So as in case of Strings and a number we use '+' operator to append, so will be used for appending a parameter to the query.

      In order to correct your expression you will need to add '+' as an connector between the query and the parameter.\
      So the changed query will look like this

      : $P{TEST_PARAM}.equals("test1")
      ? " select * from query where id=" + $P{NewParameter}

      Mind the spaces and syntax of the query after addition of the parameter value

      Hope that this helps

      Thanks

      Delete
    3. Hi Ankur,
      thank you for your answer. i tried your solution. but it did not work in expression. After that, i wrote query in expression of query parameter, like that,

      : $P{TEST_PARAM}.equals("test1")
      ? " select * from query where colour=red "

      second,

      in query designer,
      $P!{query} and id=$P{newParameter}

      this is work.

      Delete
  6. HI Ankur:

    I'm using Jaspersoft Studio Professional to implement a conditional query. In the Dataset and Query Dialog, I have keyed in simply

    $P!{Query_Controller}, a parameter with a conditional expression of the form

    $P{Query_Input_Control} == 1
    ? $P{First_Query}
    : ($P{Query_Input_Control} == 2
    ? $P{Second_Query}
    : ($P{Query_Input_Control} == 3
    ? $P{Third_Query}
    : $P{Default_Query}))

    and the P{Query_Input_Control} is an integer type, set for prompting.

    When I try to "read fields" in the dataset and query dialog, I get an error telling me "Expected lexical element not found: CREATE, DROP, SELECT, INSERT..." which is odd since all my subqueries are simple SELECT statements. Is this something in Jaspersoft, something related to the database, or is it something else I am doing wrong?

    Any assistance is appreciated. I am very new to Jasper and I wouldn't have even thought of trying a conditional query had I not seen your video. Thanks, Ankur!

    M. B. Morgan

    ReplyDelete
  7. Ankur, you may now disregard this question as I have now figured out something that worked...it was a matter of correcting the syntax in my sub-queries and assigning the correct data types to the parameters. Thanks again,
    M. B. Morgan

    ReplyDelete
    Replies
    1. Hi there,

      Syntax of the sub-query is most of the time the villain of the story, so getting that right is most crucial.

      Anyways thanks for giving a try to this.

      Ankur Gupta

      Delete
  8. Hi Ankur,

    Thank you for the blog.

    I want to add same way as you did .
    ($P{param1}.equalsIgnoreCase("All") && $P{param2}.equalsIgnoreCase("All") &&
    $P{param3}.equalsIgnoreCase("All")) ? $F{avg_cond1}.doubleValue() :
    ($P{param1}.equalsIgnoreCase($F{dynamicfieldvalue3}) && $P{param2}.equalsIgnoreCase("All") &&
    $P{param3}.equalsIgnoreCase("All")) ? $F{avg_cond1}.doubleValue() :
    ($P{param1}.equalsIgnoreCase("All") && $P{param2}.equalsIgnoreCase("All") &&
    $P{param3}.equals($F{fieldvalue3})) ? $F{avg_cond1}.doubleValue() :
    ($P{param1}.equals($F{fieldvalue1}) && $P{param2}.equalsIgnoreCase("All") &&
    $P{param3}.equals($F{SELECTED_INST})) ? $F{avg_cond2}.doubleValue() :
    ($P{param1}.equalsIgnoreCase("N/A") && $P{param2}.equals($F{dynafieldvalue2}) &&
    $P{param3}.equalsIgnoreCase("All")) ? $F{avg_cond2}.doubleValue():
    ($P{param1}.equalsIgnoreCase("N/A") && $P{param2}.equals($F{dynamicfieldvalue2}) &&
    $P{param3}.equals($F{dynafiledvalue3})) ? $F{avg_cond3}.doubleValue() : 0.0

    I am fetching the values from DB which has to display for the satisfying conditions.

    Please help me.!!

    I am not able to fetch the values from DB if i add the parameter conditional expression.



    ReplyDelete
    Replies
    1. Hi Shilpa,

      Sorry for a late reply, actually you will have to use nested ternary operator, rather && suppose
      (condition1)
      ? result
      :(condition2)
      ?(result)
      :(condition3)
      ?(result)
      ..............
      :(result)

      Hope that this helps

      Thanks

      Delete
  9. Hi Ankur,
    I liked your post - it was very informative.
    I tried making a conditional query work on my set up but it was not being accepted in the Query Executor. when I put $P!{Qry}
    It shows an error. The Parameter is a simple select statement.

    I am using Jasper Studio 5.5 - has this changed since you wrote the post?

    Your help is much appreciated.

    ReplyDelete
    Replies
    1. Hi Maithili,

      The procedure is just the same,
      I hope you have taken care of following points:
      1. The query which you have written as a string in the parameter $P{Qry} should be syntactically correct according to the norms of DB you are using.

      2. The expression in the $P{Qey} parameter should only be string.

      If you have taken care of above points and still you get the error,
      Please put the error you are getting in the comments

      Thanks

      Delete
    2. Hi Ankur,
      Yes $P{Qry} is a java.lang.String
      with the simplest query in the expression
      "select transaction_date, fl_qty_sold from transaction"
      which runs on postgres db.

      In the Dataset and Query window
      i put $P!{Qry}
      The error next to the line is
      Multiple markers at this line
      - mismatched input '' expecting 'FROM'
      - missing 'SELECT' at '$P!{Qry}'

      Read fields does not work.
      not sure where I am making the mistake

      Delete
    3. Hi Ankur,
      Just wanted to send an update.
      I had an older version of iReport - The $P!{} feature works there fine. But in Jasper reports Studio it does not work. I even downloaded the example you had uploaded.. that is not working in Studio.

      Has this feature been removed?

      Thanks

      Delete
    4. Yes, probably it would have been morphed into some other function in the newer version, though I will find out how is it done in JasperSoft Studio 5.5

      Delete
    5. I will update you with my findings here itself.

      Thanks.

      Delete
    6. Hi Maithili,

      As a resolve for your problem, I have created a tutorial video for Dynamic Parameters in JasperSoft Studio

      Here is a link
      https://www.youtube.com/watch?v=FeClina0uuY&feature=youtu.be

      I hope that this helps.

      Thanks
      Ankur Gupta

      Delete
    7. Thanks a ton.. That was real helpful

      Delete
    8. Good to know that it helped
      Cheers!!

      Delete
  10. Hi Ankur,

    What do you mean by "Do take care that the dependent parameter $P{TEST_PARAM}. should be set before the $P{TestParamSQL} else would throw a null pointer exception." ?

    I have multiple condition like this

    ($P{C_ProjectIssue_ID} != null )
    ? "... "
    :($P{C_ProjectLine_ID} != null)
    ?"..."
    :($P{C_ProjectTask_ID} != null)
    ?"..."
    :($P{C_ProjectPhase_ID} != null)
    ?"..."
    :"..."

    but the result always go to the "else" part.

    Whats wrong with that ?

    Thank you.

    ReplyDelete
    Replies
    1. Hi Spidey,

      When I said -- "Do take care that the dependent parameter $P{TEST_PARAM}. should be set before the $P{TestParamSQL} else would throw a null pointer exception." - I meant to say that if in a parameter if you use a parameter which does not have the value assigned to it earlier it will always take the default value null that is why you always get the conditional result which is present in the else clause


      For Instance,
      If there is a parameter $P{age}(default value null) and there is a SQL part of it namely $P{ageSQL} which has the expression like "table1.age >=" + $P{age}


      If in the list of parameters ageSQL comes before than age it will obviously give you null pointer exception if you do not handle null in you expression
      Or in your case when, you have taken the null value as the base so always and always it will run the expression which corresponds to the age being null

      So, always make an habit to keep the User Input parameter above the parameter which uses the user defined parameter, in this manner you can avoid this situation

      As a resolve for your problem, I understand that you are taking $P{C_ProjectIssue_ID} from the user, so the parameter which contains the above expression needs to come after the parameter $P{C_ProjectIssue_ID} in the parameter list.
      That will probably fix the issue.

      Hope that this helps

      Thanks.

      Delete
    2. Hi Spidey,

      To answer your question
      I have created a video,here is a link
      Hi Maithili,

      As a resolve for your problem, I have created a tutorial video for Dynamic Parameters in JasperSoft Studio

      Here is a link
      https://www.youtube.com/watch?v=FeClina0uuY&feature=youtu.be

      Later part of it will explain the significance of the parameter sequence.

      Delete
    3. Hi Ankur,

      You really save my day! Thank you so much :)

      Delete
    4. Good to know that

      Cheers!!

      Delete
  11. Hi Ankur,


    I setvariable in scriptlet and now I want to assing that value to another variable in my report.I did it.It always print null(second variable).but setvariable value is print.What is wrong.I'm tired to fix it.


    Please help me.......
    Thank you

    ReplyDelete
    Replies
    1. I fixed it using Print when Expression in variable properties

      Delete
  12. how to use getFeildValue("feild_name") in scriptlet.I have a error
    when I use following
    this.setVariableValue("c_int_amt",(String)this.getFieldValue("pbpm_loc"));


    error:

    net.sf.jasperreports.engine.JRScriptletException: Field not found : pbpm_loc at net.sf.jasperreports.engine.JRAbstractScriptlet.getFieldValue(JRAbstractScriptlet.java:114) at com.loit.scriptlet.rpt_bsja7.Bsja7Scriptlet.getFieldValue(Bsja7Scriptlet.java:25) at com.loit.scriptlet.rpt_bsja7.Bsja7Scriptlet.afterReportInit(Bsja7Scriptlet.java:38) at net.sf.jasperreports.engine.fill.JRFillDatasetScriptlet.afterReportInit(JRFillDatasetScriptlet.java:91) at net.sf.jasperreports.engine.JRAbstractScriptlet.callAfterReportInit(JRAbstractScriptlet.java:188) at net.sf.jasperreports.engine.fill.JRVerticalFiller.fillReportStart(JRVerticalFiller.java:281) at net.sf.jasperreports.engine.fill.JRVerticalFiller.fillReport(JRVerticalFiller.java:152) at net.sf.jasperreports.engine.fill.JRBaseFiller.fill(JRBaseFiller.java:963) at net.sf.jasperreports.engine.fill.JRBaseFiller.fill(JRBaseFiller.java:873) at net.sf.jasperreports.engine.fill.JRFiller.fill(JRFiller.java:87) at net.sf.jasperreports.engine.JasperFillManager.fill(JasperFillManager.java:287) at net.sf.jasperreports.engine.JasperFillManager.fillReport(JasperFillManager.java:760) at com.jaspersoft.ireport.designer.compiler.IReportCompiler.run(IReportCompiler.java:891) at org.openide.util.RequestProcessor$Task.run(RequestProcessor.java:572) at org.openide.util.RequestProcessor$Processor.run(RequestProcessor.java:997)
    Print not filled. Try to use an EmptyDataSource...

    please help me
    thank u

    ReplyDelete
  13. Hi Nadeeka,

    After going through the error it seems that there is some discrepancy in the field name, try and tally it with the one in the DB/Report.

    Hope that this resolves your issue, please let me know if you are unable to resolve.
    Thanks

    ReplyDelete
  14. Hi
    It's work
    I change the variable name as DB/Report.Now its work.
    Thank you very much.

    ReplyDelete
  15. Hi
    I setvariablevalue in scriptlet (in afterDetailEval()).Then I used it to get sum in DB/Report.
    It's always print null.
    I want to get summation of that value.
    how I found summation when set variable value in scriptlet.

    please help me
    Thank u

    ReplyDelete
  16. Hi
    I want to know how to use triggers in scriptlet

    Thank you

    ReplyDelete
    Replies
    1. Hi Nadeeka

      Can you please elaborate your usage of triggers
      As the events in the scriplet are already subject to band execution in the report.

      Delete
  17. Hi Ankur,

    I have successfully created this in Jasper studio and able to see data in preview based on conditions, But when deploying it on server it always assign NULL value to parameter and passing NULL in querystring.
    Please suggest as i am stuck from long time.

    Thanks,
    Rakesh

    ReplyDelete
  18. super, works also for xpath!

    ReplyDelete
  19. Hi Ankur,

    I want to parametrize schema name in jasper report query.
    eg
    select e.name from $P!{SchemaName}.employee e

    But this is not working in jaspersoft studio 5.6.2
    Please Help !

    ReplyDelete
  20. your post was very helpful. thank you!

    ReplyDelete
  21. iam writing query like select * from employee where empid=$P{ID}
    iam calling the jrxml from java. iam getting error like
    java.lang.NoSuchMethodException: No such accessible method: addParameter() on object: net.sf.jasperreports.engine.design.JRDesignQuery

    can anyone help me in resolving this

    ReplyDelete
  22. Hi Ankur,

    hope your doing good.

    I am using jasper 6.0.1 version ,I was wondering weather we can call web service as data source in jasper .Can you please assist me by providing with a sample example on this .please help me.

    Thanks

    ReplyDelete
  23. Hello Ankur..
    My name is aaditiya gupta
    My problem is that i am new on jasper report
    i have created a varible emp_name in ireport and in variable expression i want some some query to run because my employee name is saved in other table i want something like this"
    select emp_name from emp where emp_id=$f{'emp_id'}
    ****
    where $f{emp_id} is database field

    ReplyDelete
  24. Hi Ankur,

    Need to handle the option date values parameters. In my report need to select the data based on date range.
    If user provided one parameter alone then i need to get all the values greater then provided date.
    If user provided second parameter alone then i need to get all the value lesser then provided date.
    If user provided both the parameter then i need to fetch all the data between date range.

    How i need to handle this?

    ReplyDelete
  25. Im having a problem passing date parameters as string. how do I use string parameter in conditional query using dates?

    ReplyDelete
  26. This comment has been removed by the author.

    ReplyDelete
  27. Hi Ankur,
    Suppose I have two Lists like "A" List and "B" List and i wanted to display a "statictext" when the list size of A is greater than 3 ,, if not i want to display "B" List..Is it Possible by Using Conditional Operator in Jrxml File??

    ReplyDelete

Please post your queries or suggestions here!!