Translate

Thursday 6 March 2014

Cascading Parameters in Jasper Reports

Cascading, what does one mean by saying cascading, literally it means anything that resembles a waterfall or especially seeming to flow. Now where to implement this in Jasper Reports?    

Right, it could be used in parameters, or rather the filters which are dependent on each other. You can find these kind of filters in online shopping websites, wherein we can choose on various filters like if we want to buy the laptop first filter would be Laptop next filter would be the company chosen among the companies which sell Laptops, and then we chose for the specifications like processor,ram etc. each have separate filters but the boundaries of the parameter depends on the previous parameter.

Before getting into the setup of Cascading Parameters in Jasper Report there are few things which we need to know before hand - 
Usage of     $X{IN, feild, parameter} in the SQL Query used in the Where clause, which filters the data based on the selection. Let me go one by one in explaining the components
field - Now this is the field in the database upon which the find will be done
parameter - Now this is the parameter selected by user and will be queried or looked in the field. This enable for multi select also in the parameters.

Now in  order to achieve this in Jasper Reports we need to follow few steps.

  1. First of all you need to design a Jasper Report and identify the parameters to be cascaded
  2. Then we need to define the expression for parameters,  and mind one thing that each of the parameter need to be of type java.util.collection if you want to enable multi- select.
  3. Next and very important thing is that the cascading parameters could not be tested in iReport / JasperSoft Studio, this  needs to be deployed on Jasper Server in order to check the correct functionality. Though could be run in iReport or JasperReports with default values hardcoded.



I have created a working sample which could be simply deployed, and checked for the functionality and working.
The Zip file could be downloaded from here  - LINK TO ZIP FILE

The Zip file contains
1. Cascade Parameter Report (JRXML File)
2. SQL DUMP, could be extracted using MySQL or any other tool
3. Table Creation Script in a notepad file, this will allow you to create the require table in the database.

For setting up the sample report you need to-
1. Create a schema in you database and create the table using the script command
2. Then run the JRXML file using iReport/Jaspersoft Studio
3. Then set the datasource referring to Persons table in the database.
4. Then deploy the JRXML  on the Jasper Server, and set the datasource and input controls. Do remember the names of the parameters used in the report should exactly match the input controls declared on the Jasper Server.

If you miss on some step then you could follow a YouTube video of mine which  will serve as a tutorial.
Below is the video where in you could see all the steps.




Please put your views or questions or suggestions below, I will be more than happy to hear them.Happy Coding!!

8 comments:

  1. Thank you for the post. I am writing a domain report and with cascading parameters.
    Filter expression :($F{FINANCE_SUB.CATEGORYIND}.contains($P{CAT})) && ($F{FINANCE_SUB.DESC_ID}.contains($P{SUBCAT}))
    Input Controls are defined to be populated from the following Queries:

    CAT: SELECT DISTINCT CATEGORYIND AS 'CAT' FROM perceive.TRAINING_01_CONTROLDATA WHERE LINEIND = 'SUB'

    SUBCAT:SELECT DISTINCT DESC_ID AS 'SUBCAT' FROM perceive.TRAINING_01_CONTROLDATA WHERE $X{IN,CATEGORYIND,CAT}

    Do you see anything missing?
    I am not even getting the input parameters to work?
    Thanks for your help.

    ReplyDelete
    Replies
    1. Have you taken the data type of the CAT as Collection ?

      As You are trying to use $X{IN} parameter so, CAT paramerter should be of Type Collection.

      In usage of $X{IN,CATEGORYIND, CAT}- CATEGORYIND should be column, and CAT should be the Parameter of type Collection.

      Delete
  2. I am writing a domain based report. So I want to know how to handle the multiple values in the filter statement. I am not writing the SQL based report. I have CAT parameter as Collection.
    Filter expression: $F{FINANCE_SUB.CATEGORYIND}.contains($P{CAT})) is not working.
    I am getting error filling in the report.

    ReplyDelete
  3. Is this example for Jasper Reports 5.5? since im getting an error while selecting Cascading Input Controls. They were working fine in 5.0

    ReplyDelete
    Replies
    1. What us the error you are getting?

      Delete
    2. this example of yours in on 5.5?

      Delete
    3. This Example was created in iReport 5.0 and I have retested the report in 5.1,5.5,5.6 all run the report correctly. There must be some mistake you are doing please follow the video tutorial.

      Thanks

      Delete

Please post your queries or suggestions here!!