Amazon festival offer

Saturday 26 April 2014

Optional Where Clause in iReport Query



In this post I am going to show you how to pass optional where clause to the query in report query and will use the same query which I used to create the chart in previous post.
In this report I have three input controls, p_year (Integer), p_shipcountry(Collection) and p_shipcity(Collection).
Where p_year is a single select input control to select year means user can select only one year at a time, p_shipcountry is a Collection type input control, which is multiselect cascading input control means user can select multiple ship countries for the selected year and p_shipcity is also a Collection type multiselect cascading input control.

Query for the report:-

SELECT  YEAR(ORDERDATE) year,
                 MONTHNAME(ORDERDATE) month,
                  CONCAT(MONTHNAME(ORDERDATE), RIGHT(year(ORDERDATE), 2)) monthyear,
                  COUNT(*) orders
FROM orders
WHERE (YEAR(ORDERDATE) = $P{p_year} OR $P{p_year} IS NULL)
               AND $X{IN,shipcountry,p_shipcountry}
               AND $X{IN, shipcity,p_shipcity}
GROUP BY YEAR(ORDERDATE), MONTH(ORDERDATE)
ORDER BY YEAR(ORDERDATE), MONTH(ORDERDATE)



In this report I have not set the default values for any input controls , so if you do not pass the value at run time by default it will show you all the values in case of Collection type input controls(p_shipcountry and p_shipcity) and for the p_year(Integer) input control you can use this expression to make optional in where clause(If you do not use this expression then you have to pass any value otherwise you will get no document page popup):-

    YEAR(ORDERDATE) = $P{p_year} OR $P{p_year} IS NULL

This means if you do not pass any value for this input control it will show you for all the years.
Now run the report and do not pass any values to parameters then you will get this chart for all the years.



Other Useful Links:-

No comments: