Sunday, 19 May 2013

Create a cascading input control in JasperReport Server

In Business Intelligence reports, input control play an important role, input control gives flexibility to the customer to manage large amount of data, to filter the dashboard values based on the input controls values.
              With cascading parameters, the list of values for one parameter depends on the value chosen in preceding parameter. Order is important for cascading parameters because the dataset query for a parameter later in the list includes references to parameters earlier in the list. For example suppose we have two input controls and names are Shipcountry and Shipcity, In this condition if we select Shipcountry then next input control Shipcity should show only those cities which belongs to that selected Shipping country.
 In this post, we will learn how to create cascading input controls in JasperSoft.
For this post we are using previous report which we had created, just add two new input control p_shipcountry and p_shipcity and add condition in where clause .

(shipcountry=$P{p_shipcountry} or $P{p_shipcountry} is null)
 (shipcity=$P{p_shipcity} or $P{p_shipcity} is null)
If you have noticed I have added null condition ($P{p_shipcountry} or $P{p_shipcountry} is null )with parameter, it gives you all records if you didn’t select any input control value.
In JasperSerever create a new input control with resource id p_shipcountry and you do not have to use the data source for this input control, just use the bellow query:-
                              SELECT DISTINCT shipcountry FROM orders ORDER BY shipcountry
Create second input control with resource p_shipcity  , which will be cascading input control, but you have to use the data source for this input control and use the bellow query:-
                              SELECT DISTINCT shipcity FROM orders
                              WHERE shipcountry= $P{p_shipcountry} ORDER BY shipcity
After creating these two input controls in JasperSoft, add the input controls in report by editing the report.

1 comment:

Meiji Jap said...
This comment has been removed by the author.