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:-
