Thursday, 2 May 2013

Parameterizing the report



Now I am going to parametrize the report so we can filter the records at the run time of the report, suppose we want to filter the data by year and we want to see the chart for only a singal year,  we can achive this by follwing these simple steps:-
Open the report in which you want to add paramter(I am using the same report which we had developed in the previous post) and edit the query.

Click on the “New paramter” button  and add parameter name and value expression, in the Default Value Expression section we can set the default value for paramter.


Add the Where clause in the query
select count(*) orders,
       year(ORDERDATE) year,
       MONTHNAME(ORDERDATE) month,
       CONCAT(MONTHNAME(ORDERDATE) ,
       right(year(ORDERDATE),2))monthyear
from orders
where year(ORDERDATE)=$P{p_year}
group by year(ORDERDATE), MONTH(ORDERDATE)
order by year(ORDERDATE), MONTH(ORDERDATE)
And click on the preview tab, now you should get the prompt for parameter , you can enter the years or use default value.

As you can see in the final report output, report has only year 1996 data.


No comments: