Featured Post

Applying Email Validation to a JavaFX TextField Using Binding

This example uses the same controller as in a previous post but adds a use case to support email validation.  A Commons Validator object is ...

Tuesday, March 8, 2011

Adding a Parameter to a Jasper Report WHERE Clause with iReport Designer

Adding a parameter to a Jasper report and using the parameter in the WHERE clause of a query is a simple three-step operation.

In Jaspersoft iReport Designer, you can define a parameter that will be retrieved by a dialog box when a report is previewed.  This parameter can be used to restrict the result set -- like added to a WHERE clause -- and that result set can saved off along with the report.

The first part of the operation is to add the parameter.  Right mouse click on the Parameters node in the Report Inspector docker and select "Add Parameter".  Go to Window> Report Inspector if the docker isn't showing.

Parameters Node in Report Inspector Docker
Select the parameter and edit its properties in the Properties docker.  Go to Window > Properties if the docker isn't showing.  Enter a name.  If you're working with a number, use Integer, Long, or Double.

Parameter in Properties Docker
Lastly, select the database icon with an arrow next to the Preview button and edit the Report Query.

Report Query Button

When the Report Query is displayed, edit the SQL to receive the parameter: "WHERE dy.year_nb =".  Then drag the parameter from the Available Parameters box onto the query.
Add Parameter to Query
In this example, a parameter "Fiscal_Year" was added to the WHERE clause.

Previewing this example will now display a prompt (if configured as such in the parameter Properties docker).  This prompt retrieves a parameter value and uses it to form a result set.  I can run the report repeatedly, exporting the results as HTML and PDF.
Posted by at

11 comments:

  1. AnonymousApril 19, 2013 at 2:16 PM

    Hello Carl,

    Your videos related to Jasper are helping me and many people like me.
    I am kind of new to jasper and trying to create a report
    but I am struggling with some issue here

    Can you please look at
    http://community.jaspersoft.com/questions/809247/create-multi-select-list-ireport

    and guide me to fix this issue.
    Thank you for all the support

    ReplyDelete
  2. UrsJuly 30, 2013 at 12:05 PM

    Great stuff. I got that far.
    What I would like to do goes a bit further.
    Instead of WHERE YEAR = $P{year} I would like to use
    WHERE $P{whereClause}
    But the problem is that I get something like this sent to the SQL Server:
    ...WHERE 'FISCAL_YEAR = 2013'
    which is obviously useless.
    My parameter is of type String, which follows described behavior. I wonder if there is another data type that would allow me to send my parameter without apostrophes.
    Any idea?

    ReplyDelete
  3. CarlJuly 30, 2013 at 12:11 PM

    You can try using a special syntax that will not bind the value (2013): $P!{}. For example, instead of trying to put "FISCAL_YEAR = 2013" into WHERE ?, try

    SELECT * FROM sales $P!{FISCAL_YEAR}

    where FISCAL_YEAR is a parameter defined as "WHERE FISCAL_YEAR = 2013". This will substitute the whole clause in, not just the value. Watch for bad input!

    ReplyDelete
  4. UrsJuly 31, 2013 at 2:50 AM

    Wow! That was quick!
    Thank you, the P!{} did the trick! Excellent!!
    And yes, there is no user interaction involved in filling the parameter. Depending on conditions, there will be a where clause or there won't.
    Thank you very much!

    ReplyDelete
  5. AnonymousFebruary 18, 2014 at 9:31 AM

    hi carl,
    i have a table, its syntax as "car_year".

    such as, car_2012, car_2013, car_2014 etc.

    i add a parameter for year. i've added parameters as car_$P{year} or car_:$P{year}
    or car_+$P{year} or car_||$P{year} etc, what else...

    it didn't work and i dont know how can i to do.

    What would you recommend me for this problem?
    Thanks.

    ReplyDelete
    Replies
    1. CarlFebruary 18, 2014 at 10:36 AM

      Hi,

      Here's a link to a technique you can use if you have SQL Server. http://bekwam.blogspot.com/2012/04/swapping-out-sql-server-table-in-jasper.html

      I think the $P{} is limited to bind variables. Bind variables are place holders that are swapped in as Jasper Reports uses the query. Bind variables are not text concatenations and that may explain why you're able to use $P{year} in a WHERE clause but not in a FROM or SELECT clause.

      In the link I'm mentioning, I'm passing $P{year} in as a bind variable to a stored procedure. This is similar to the parameterization of a WHERE clause. Note that the stored procedure name is fixed; I am not dynamically selecting a stored procedure.

      Delete
  6. AnonymousAugust 9, 2014 at 8:56 AM

    Hello Carl, I am working on a software with ireport and experiencing a little problem. I have two queries, one being in the main query and the other in the table dataset query. However both query have where clauses which is equal to the table_click. This is to help the software generate it own value for the where clause. When the report is previewed in the software, only the fields from the main query shows but the field from the table dataset does not show.
    I would be glad if you can help me on this. Thank you

    ReplyDelete
    Replies
    1. CarlAugust 11, 2014 at 7:30 AM

      Try creating the report without the parameter. As a test, hardcode a parameter value in the queries to determine if the problem is in the parameter handling or the report itself.

      Delete
  7. UnknownJune 16, 2015 at 8:40 AM

    Thank you very much sir

    ReplyDelete
  8. piyushDecember 20, 2016 at 5:28 AM

    hi, How to create parameters like radio button or list box in ireport.

    ReplyDelete

Subscribe to: Post Comments (Atom)