Multisheet Excel Jasper Report With Subreport Using Wizard

Wizard enables the user to enter data and based on the data inputted, corresponding report can be generated. But in some cases there can be situations to generate Excel report with multiple sheet with different data in different sheets.

We can easily create a report using wizard. Please refer http://zbeanztech.com/blog/creating-jasper-report-using-osvmemory-openerp-6 for more details regarding report creation using wizard. Anyway i am going to have a quick overview of creating jasper report using wizard. For my report designing, I am using iReport 4.0.3.

Creating Wizard and its View:

Model and view creation is same as osv.osv but the main difference is the use of osv.osv_memory. osv.osv_memory does not create any table in the database. A simple model definition for wizard is given below:

View of the wizard should have a button which calls a function in the wizard. The function is given below:

This files creates the wizard which enables the user to enter data.

Data Generation:

Now lets have a look at how data is generated and send to the report from OpenERP. Usually this data fetching and processing is done from an python file associated with the report. The structure of the python file is show below:

Initially we define a class. We can give any name to the class and it depends upon the programmer. The class has an argument “JasperDataParser.JasperDataParser” which the parser used for generation of report. This parser file is uploaded with this blog.

__init__ function is the constructor. and it calls the constructor of the class defined. Since we are using Excel with multiple sheets, self.sheet_name is used to assign the name for each sheet. We can specify the names we wish to give to our sheets. self.sheet_name is a list and its content should be as:

[‘name1‘,’name2‘,’name3‘………]

This is optional. If self.sheet_name is not specified, name of the sheets will be page1,page2,page3…etc.

Fetching and generation of data is done inside the function generate_records. The data from wizard can be fetched as:

user_id=data[“form”][“field_name”]

This fetches the value entered by the user for the field_name specified. Likewise we can fetch all the data entered by the user. Using this data we are going to generate data for the report. We can browse or search records based our requirement and generate data. The data send to the report should be a list of dictionaries. Ie; the data send should be in the format:

[{‘name’ : ‘abc‘ , ‘date_of_birth’ : ’01-01-2001′ ………….}]

These variable name,date_of_birth will be available in the report and it can be displayed in each position in our report as we like.

Subreport also uses the same concept. The data to the subreport should also be a list of dictionary and it should also be send with the data to the main report. The data to report with one subreport should be in the format:

[{‘name’ : ‘abc‘ , ‘date_of_birth’ : ’01-01-2001’….’sub_report’ : [{‘age’ : ‘9’ , ‘height’ : ‘100’………}]……..}] 

In above example, sub_report is the variable that has the data to be passed to the subreport. Subreport is treated as another report and data to the subreport should also be a list of dictionary. This should be specified as the data source to the subreport which will be explained later in the blog.

Function generate_data_source is used to specify the data source of the report. In my case I am using xml_record ie, xml data source.

Function generate_parameter is used to pass parameters to iReport. We can set values to existing parameters or create new parameter according to our needs. The value for parameter is specified as:

return{‘<name of parameter>’ : ‘<parameter value>’}

Function generate_properties is used to set properties of the report. To generate a multi sheet jasper report we have to set some properties here. 

net.sf.jasperreports.export.xls.one.page.per.sheet specifies weather each report page should be printed in separate excel page.

net.sf.jasperreports.export.xls.sheet.names.all specifies the name that should be give to each sheet in excel sheet. The name is passed in the format 

\name1\name2\name3………

For more details regarding properties that can be used, please refer

http://jasperforge.org/uploads/publish/jasperreportswebsite/trunk/config.reference.html

iReport:

iReport is an powerful report designing tool used to design jasper reports. We can create reports that has charts, graph etc 

Create a report and inside the main report create a subreport. For details regarding how to create a subreport, please refer http://zbeanztech.com/blog/subreports-jasper-reports. 

To display the data generated before, click on fields tab in the iReport and click on add variable button as shown below:

Now select the newly created field and select properties.

Now assign a name to the field, Field class as java.lang.Object, and description as the name of the variable from wizard that has the data to be passed to the subreport. In this case sub_report since i am passing data as:

[{‘name’ : ‘abc‘ , ‘date_of_birth’ : ’01-01-2001’….’sub_report’ : [{‘age’ : ‘9’ , ‘height’ : ‘100’………}]……..}]

Now create a subreport. (Refer:http://zbeanztech.com/blog/subreports-jasper-reports.). Select the subreport generated and from properties, scroll down to Connection Type. From the selection select ‘Use a datasource expression’. 

Now go to Data Source Expression and select it to edit. Enter the expression :

((net.sf.jasperreports.engine.data.JRXmlDataSource)$P{REPORT_DATA_SOURCE}).subDataSource(“//in_data”)

Replace in_data with the field name that has the data to be passed to the subreport. In this case sub_report.

Now open the subreport and arrange the data based on how you want the data to be displayed in subreport.

Now the report can be printed. If the report goes beyond one page, it will be displayed as another sheet in excel. If you want to display data for each group in a different sheet, create a group first. Select the group and go to properties. Check the option Start on a new page.

I have tested this on OpenERP 6.1 and no error has been reported.