creating a crosstab report with jasper report

creating a crosstab report with jasper is became very easy task now...

as I explore it I found it very easy and simple to do...

the most tedious task was earlier to manage with the query to get the data in cross format. since there was no alternate or easy feature available from database either.

but for now with japserreport it become so easy that you don't have to modify your query at all just fire simple query and its done.

lets see how.

to show example I will use hssqldb that shipped with ireport tool.

concept: in a business case we have some times a requirement where we need a crosstab data for analysis which is difficult to achieve. because what we store in database is in number of rows and if we need that to be shown in columns is very difficult to achieve.

in our example lets see following result of table "tasks"

and assume now that we have a requirement to show this data in a different format as

group scheduled and actual and subgroup with tasks within and show them as columns

and subtasks as rows. that means it will look like something like this

                actual                           | scheduled

               task1 | task2 | task3      |task1 | task2 | task3     

 subtask1 

subtask2

subtask3

etc.

lets see how we can put this in cross tab report as below.

1. open ireport

2. file>>new>>launch report wizard (click) button

3. give reportname.>> next>>

4. select database connection>>add sql query as "select * from tasks" >> next>>

5. select all fields >> next>>

6. next>> finish>>

7. set all bands height as 0 except summery band.

8.   drag and drop crosstab icon on summery band.

9.   select "main report databaset" >> next

10. in rows we want to show subtasks so select "subtask Field" in rowgroup1 , group by "unique" >> next>>

11. now for column we have two level of grouping first is actual and scheduled and under it tasks so for

columngroup1 select "series Field" group by "unique">>

columngroup2 select "task   Field" group by "unique">> next>>

12. for data we will select "percent Field" , function "nothing" because we want to show values as it is.>> next>>

13. you can choose colour scheme of your choice and also column/row grouping if you want for my example i am leaving it unchecked that means I am not using row& column grouping.

 >> finish.

Thats all your report is done now click on preview and try to see how it looks.

 

after genrating view see it as below.