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.

10 comments:

  1. 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. 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. 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. 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. 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. 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. 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. 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. hi, How to create parameters like radio button or list box in ireport.

    ReplyDelete