How to develop a Tabular Report using iReport

In the previous post we saw how to develop a simple bar chart with optional where clause now will see how to develop a tabular report for the same set of records so user can also see the data when he clicks on the chart. 
 

Create a report with three input controls, p_year (java.lang.Integer), p_shipcountry(java.util.Collection) and p_shipcity(java.util.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)

For Tabular report, we have to create a sub dataset. To add sub dataset follow these steps : -

1 - Go to Windows and select Report Inspector.

2- Right click on the report name and click on “Add Dataset” and select option “Create an empty dataset” and then finish, edit the dataset and paste the above query and add all the parameters and click on the Read Fields button.

Once sub dataset is ready, go to Window, select Palette, and then drag Tabular Component to the Summary or Title Band (Do not use Detail band for any chart and Tabular components)

Select The Dataset name that you just created for the report

Move the field in the same order in which you want in the report.

Use alternated detail rows background option to get the alternate row color.

You can also select the “Add Column Footer” option if you want the total by column.

 Edit the sub datsource and pass all the parameters.

 

Click on the Preview button.

You can find the jrxml file here which i used for this post.
 Tabular report

Useful links:-

http://stackoverflow.com/questions/19401825/optional-where-clause-jasper-reports

http://stackoverflow.com/questions/11082379/jasperserver-null-values-for-input-controls?lq=1