Wednesday, May 21, 2008

Dynamic Jasper Report Using Crosstabs

Today I was doing some searching for creating dynamic jasper reports, i.e. not have my columns defined in my report template. This article is a well known reference that discuss modifying the xml template at run time using velocity. This seemed like overkill for what I wanted to accomplish. So I continued searching and found references to using crosstabs to accomplish what I needed.

Unfortunately I was unable to find any examples on generating a report using crosstabs. The Jasper Reports example didn't apply to my scenario, and I feel it didn't apply to the majority of users needs. The forums for ireport and Jasper Reports contain many entries with developers struggling to get crosstabs working. So below is a simple example of how to accomplish this using a JRBeanCollectionDataSource.

The first thing I needed to do was create a simple bean to use in the datasource. I created a simple class called MyBean.



public class MyBean { String header; String row; String value;

public MyBean(String header, String row,String value){
this.row = row;
this.header = header;

this.value = value;
}
}



Next I created some simple beans and added them to my datasource. And generated the report based upon my template.


ArrayList beans = new ArrayList();
MyBean a1 = new MyBean("header1","row1","VA
LUE1");
MyBean a2 = new MyBean("header2","row1","VALUE2");
MyBean a3 = new MyBean("header3","row1","VALUE3");
beans.add(a1);
beans.add(a2);
beans.add(a3);

MyBean b1 = new MyBean("header1","row2","VALUE1");
MyBean b2 = new MyBean("header2","row2","VALU
E2");
MyBean b3 = new MyBean("header3","row2","VALUE3");
beans.add(b1);
beans.add(b2);
beans.add(b3);

JRBeanCollectionDataSource ds = new JRBeanCollectionDataSource(beans);


So now I have my datasource defined. For each row that I generate I can have as many columns as I want giving each column a unique name. To add a new row, I simply create new beans with a new row name. And for each column I can pass in a value.

To create the report template, add three fields, as defined in our bean above.

Then we need to create a crosstab. When creating make a row group using the $F{row} value for the row group bucket expression, and the $F{header} for the header group bucket expression. Lastly set up the measure as follows.



And when you generate your report you should have something like the following. This could use some cleaning up but gives the general idea, and hey, it's more information than I was able to find.



UPDATE
As requested here is the jrxml file for the crosstab report. In addition I have started to experiment with using the jasper reports api to create a report from a template or from scratch. This works well for some other scenarios that I have come across. The one nice thing about crosstabs though, is that it handles the wrapping of columns to new pages when they exceed the page width. Another option is Dynamic Jasper, I briefly looked at this, and it looks pretty nice, but using the api directly was a better solution at the time. As with any report, I don't think there is one magic bullet to solve all problems, and each problem must be looked at uniquely to determine the best approach.

35 comments:

gerov said...

Thank You!

This is exactly what i was looking for.

Cann you please post your .jrxml file?

Radha Krishna said...

As the previous post it would be great if you could please post a detailed .jrxml file please!!

I am a little curious how much more time is spent into creating this data when I have a huge collection/array of POJO's (my datasource) that I will have to iterate to fill in the header, row and value for JRDataSource. Any suggestions on how to fill huge data quickly? Anyways, its a great hack (kinda)!

Dominpe said...

Thanks!!!!

Exactly what I was looking for!!!

Seriously, thank you.

broschb said...

Thanks for the feedback. Sorry I didn't check to see if there were any comments, I will post the .jrxml file and hope it is still of use.

Bùi Thế Vinh said...

Thanks a lot, it's very hopeful

broschb said...

Glad you found it useful.

joelson said...

Me ajudou muito.

Obrigado

Shan said...

How to create dynamic report using IReport?

Because we are developing a Swing based project in java. We need solution for dynamic report generation. In my swing design i will select some column at run-time based on that input i have to create a report at run-time.

note:
How to create .jrxml file for run-time(its possible).
In net i found some solution for dynamic jasper, but there also they create columns using .addColumn();, but it need the .jrxml file.

give some solution as soon as possible

Shan said...

How to create dynamic report using IReport?

Because we are developing a Swing based project in java. We need solution for dynamic report generation. In my swing design i will select some column at run-time based on that input i have to create a report at run-time.

note:
How to create .jrxml file for run-time(its possible).
In net i found some solution for dynamic jasper, but there also they create columns using .addColumn();, but it need the .jrxml file.

give some solution as soon as possible

broschb said...

If you could post some more details on what you are trying to achieve I could probably be more helpful. I have found since doing this, the easiest way, especially if you will be doing this alot, is to create some wrappers and utilities around the jasper reports api, and use the api to create the report if it will be highly custom each time. If not another idea would be to have generic columns defined in the jrxml and then pass the data, including column names into the jrxml. It all depends on what data you will be displaying and the options(flexibility) you would like to have.

Vinod M said...

Cheers for the blog it was really helpfull but it would have been more helpfull if u could jus provide the whole code i mean source code

anyway thnx for a lovely blog

Vinod M said...

Hi what I really want is how to define the whole confugaration in ireport as I am new to it I had only done reports with database and dynamic query & thus converting the resultset So if u dont mind i will really appreciate the help

thnx in advance

Vinod

dev said...

Hey man i am using netbeans 6.5.i have done wat u have written but after i got error

"net.sf.jasperreports.engine.JRException: Error retrieving field value from bean : header"

"java.lang.NoSuchMethodException: Unknown property 'header'"

so if possible then please upload whole code.
Thank you very much.

broschb said...

@dev, I'll have to see if I have this code around somewhere. From the error I would guess you may not have created Getters/Setters on the bean. For all of the fields in the bean header,row, and value, create a getter and setter.

Cyrille said...

Working with Struts2 and iReport, my data is one single bean that contains all report data. One of it is a JRBeanCollectionDataSource object (built as you describe). It tried to pass it to a sub report witch contains a cross table (built as you describe), but the following exception is thrown at runtime : net.sf.jasperreports.engine.JRRuntimeException: Error incrementing crosstab dataset.

Thanks for helping me...

Neal

Pingo said...

Hello,
Actually I am working in struts 1.2
And using jasper report to display data.
My problem is that I have one list of bean and after that have one bean which have a list of this list bean means that:
ABCBean a = new Bean();
a.setName("this");
a.setClass("12th");
List abcList.add(ABCBean);
ABCBean2 b = new ABCBean2();
b.setList(abcList);
and then using one more list
List finalList.add(b);
Now I want to use these all fields in my jasper report. I am using JRBeanCollection for this. But jasper is giving error there is no property found for name, class.
Please can you help me. It is really urgent. I have read your this tutorials. It is really marvalous.

Please help.

Pingo said...

Hello,
Actually I am working in struts 1.2
And using jasper report to display data.
My problem is that I have one list of bean and after that have one bean which have a list of this list bean means that:
ABCBean a = new Bean();
a.setName("this");
a.setClass("12th");
List abcList.add(ABCBean);
ABCBean2 b = new ABCBean2();
b.setList(abcList);
and then using one more list
List finalList.add(b);
Now I want to use these all fields in my jasper report. I am using JRBeanCollection for this. But jasper is giving error there is no property found for name, class.
Please can you help me. It is really urgent. I have read your this tutorials. It is really marvalous.

Please help.

broschb said...

@Pingo, if you want to post your beans, jrxml, and a small class that executes the report, and duplicates your issue I would be happy to take a look at it.

Anbu said...

Hi,
I am using cross tab in summary band. Will it be possible to use that in detail band. So that I will get the headers and footers in all the pages.

broschb said...

@Anbu, I'm not sure give it a try and let me know how it goes.

Erdene said...

This is very helpful, Thanks!!!
If i have multiple rows and columns, how should I organize my Datasource. I think this will not work
ArrayList beans = new ArrayList();
MyBean a1 = new MyBean("header1","row1","VALUE1");
MyBean a2 = new MyBean("header2","row1","VALUE2");
MyBean a3 = new MyBean("header3","row1","VALUE3");
beans.add(a1);
beans.add(a2);
beans.add(a3);

MyBean b1 = new MyBean("header1","row2","VALUE1");
MyBean b2 = new MyBean("header2","row2","VALUE2");
MyBean b3 = new MyBean("header3","row2","VALUE3");
beans.add(b1);
beans.add(b2);
beans.add(b3);

Can you give me some guidance.

broschb said...

@erdene I am not sure exactly what you are referring too. For multiple rows or columns you just rinse/repeat what is shown in the example. The example is very simple but should give you the basic idea.

If you could maybe post an example of what the final report should look like I could help you come up w/ something.

Erdene said...

Thank for your reply. I'm trying to do this kind of report using DynamicJasper.

http://dynamicjasper.sourceforge.net/images/examples/crosstab-2x2_s.jpg

My requirement is row and column number is unlimited. My questions are:
1. If I want to do this how should I organize my datasource
2. You used Javabean for the datasource, but I cannot use that. Coz number of attributes is unknown. So how can I solve this?

vijaykumar said...

Above problem i also facing pls tell me the solution muiltple columns dyanamically and rows also.

vijaykumar said...

Hi,
I also facing same problem wht above guy facing multiple column and multiple row display dynamically in jasper.

Erdene said...

@vijaykumar
You can use HashMap instead of JavaBean.

HashMap key is your column or row property name, value would be anything that you want to display.

viju said...

Hi Erdene,
U did not get my question clearly i think.
My requirement is row and column numbers is unlimited.All this Data is coming from 2 arraylist(List).
1.One arraylist contains all the coulmn headers in one row.
2.Second arraylist contains
so many datarows for those above columns.

hint:i could not create javabeans(pojo).i wnt use only jasper report components.

Please give me some flow how to do.

Thank you,
Regards,
Vijay

viju said...

Hi Erdene,
U did not get my question clearly i think.
My requirement is row and column numbers is unlimited.All this Data is coming from 2 arraylist(List).
1.One arraylist contains all the coulmn headers in one row.
2.Second arraylist contains
so many datarows for those above columns.

hint:i could not create javabeans(pojo).i wnt use only jasper report components.

Please give me some flow how to do.

Thank you,
Regards,
Vijay

Bart said...

Hey, I was trying to repeat the example and I have a small problem. I'm using IReport 3.7.2. I can recreate the table and passing a List of object and everything gets displayed except the first element of my collection which doesn't seem to be taken into account, all the totals in my crosstab are correct, in comparison to the displayed values. But the first element of my collection isn't being included in the crosstab.

Lim Rong Ji said...

hi ,

I have tested with your example but i encounter this problem

net.sf.jasperreports.engine.JRRuntimeException: Error incrementing crosstab dataset
*******
Caused by: net.sf.jasperreports.engine.JRException: Crosstab data has already been processed.

below is my source

MyBean b1 = new MyBean("header1","row2","VALUE1");
MyBean b2 = new MyBean("header2","row2","VALUE2");
MyBean b3 = new MyBean("header3","row2","VALUE3");
beans.add(b1);
beans.add(b2);
beans.add(b3);



JRBeanCollectionDataSource data = new JRBeanCollectionDataSource(beans);


JasperReport jasperReport;
JasperPrint jasperPrint;


jasperReport = JasperCompileManager.compileReport(
"C:/work/projects/cross_tab_test/bin/com/test/crossTabTest.jrxml");
jasperPrint = JasperFillManager.fillReport(
jasperReport, new HashMap(), data);
JasperExportManager.exportReportToPdfFile(
jasperPrint, "C:/work/projects/cross_tab_test/bin/com/test/cross_tab_report.pdf");

the jrxml is from your source.

Thanks for your help

JEMAHN said...

Hi, I am passing data like List(Bean), can you pls advise how to use the beans set in list?

JEMAHN said...

Hi, I have to use list variable in gross tabs. I am using List(Bean). Can you pls advise how to use the values in bean set via list in crosstabs?

JEMAHN said...

Hi, I have to use list variable in gross tabs. I am using List(Bean). Can you pls advise how to use the values in bean set via list in crosstabs?

ht said...

Good job. Saved some time. Thanks
HT

MGelbana said...

Thanks a lot that was a great quicky into the subject.