creating a sub-report example with Jasper
Pre-requisite: I assume that you know how to create normal reports using iReport GUI tool also basic knowledge of database, sql with MySql.
before we start with actual creating of report lets understand the database that we require.
following is the table structure that we require.
I have taken the sample database that comes with iReport hsqldb, you can use that directly also.
following is that data within each table.
I have restricted data to minimum to show here if you fire the same query in iReport you can see all data.
so basically it shows the billing information for each transaction taken place, master table is document and its detail table is positions.
lets now create a sub report first which we need to include in the main report for every transaction.
so open iReport tool
1. file>> new... click on "Launch Report Wizard"
2. enter report name and its location where it will save report on local machine
name: sub-rpt-det
location: C:\data\mytest
file: C:\data\mytest\sub-rpt-det.jrxml
click on next,
3. if you are using hsqldb / mysql databse as a datasouce choose appropriately.
paste following query in the tab
select * from positions
where documentid=0
order by positionno
later, we will be replacing the documentid value with the actual parameter passed from the master report.
click next>>
4. select all fields shown in the left side of fields combo
click next>> next >> finish
5. set title, page header, summery band height as 0 (zero)
6. add the fields to report detail section
7. add fields column labels in the column header
8 now goto report inspector >> expand variables >> right click on variables>> select "Add Variable"
9. check the following variable1 properties for the variable that just created. modify them appropriately
10. similarly create another variable named tot check out its properties as below
11. Place the field "cost" in detail section at end and "tot" field in column footer along with label (see below)
12. add a "Rectangle" box in the column header and move it to back, add a background color as gray
13. add a "line" the column footer.
your report will look like as below
14. try to click on preview button and check if its generating output
15. go to report inspector>> expand parameters>> right click >> "Add Parameter"
add a parameter named "m_docid" change its parameter class as "Integer"
16. click on db tool button is next to preview button
replace the query parameter from (zero) 0 to $P{m_docid} so your query will look like as below
select * from positions
where documentid=$P{m_docid}
order by positionno
with this your sub report is complete now lets create a master report.
1. file>> new...
2. click on button "Launch Report Wizard"
3. enter report name as "mst_rpt"
name: mst_rpt
location: C:\data\mytest
file: C:\data\mytest\mst_rpt.jrxml
click on next,
4. if you are using hsqldb / mysql databse as a datasouce choose appropriately.
paste following query in the tab
select d.*, a.* from document d
left join address a on a.id= d.addressid
order by task
click next>>
5. select all fields shown in the left side of fields combo
click next>> next >> finish
6. select Palette>> report element>> sub report
drag it to the detail section. subreport wizard will appear on screen
a. select option use existing report and browse and select the sub report that we had created.
C:\data\mytest\sub-rpt-det.jrxml
click next>>
b. select first option "use the same connection used to fill the master report"
click next>>
c. it will show paramters created in sub report "m_docid" which we need to map with the master report field so we will be passing our document table's id filed here, click on expression drop down combo you will find all parameters, fields and variables that are defined in master report. choose "id" field from it.
click next>>
d. select the first option of subreport expression as "store the directory name in a parameter"
click FINISH
so your master report created with its sub report and other fields added in the details band will be seen as below
click on subreport and check its properties as below
note the parameters says that one parameter defined click on its button and see that it should show the m_docid as the parameter that we are passing to sub report.
here is the way to pass parameters to sub-report and to get value from sub-report
note that when you pass any value to sub report, that value may be parameter/field/variable defined in main report. you have to take special care for variable field of main report that it should have defined and has some value. please see below image how to pass
define one parameter in sub-report that will accept value from main report.
make sure that data type of sub-report parameter and main report parameter/field/variable should be same/compatible. here I have defined m_docid which will take value from main report.
similarly now when you have to define a variable that returns a value from sub report note that
1. values should come from sub-report variables, and place holder in main report also should be a variable.
2. variable defined in main report will get populated only after sub-report is totally generated, so it has to be placed after sub report defined.
please have a look how to get value from sub report.
1. I have defined "AValueFromSubRep" variable in main report
2. then I am mapping this variable to the sub report variable "totcost"
see how its get written in jrxml as below.
<subreport>
<reportelement height="34" width="555" x="0" y="43">
<subreportparameter name="m_docid">
<subreportparameterexpression></subreportparameterexpression>
</subreportparameter>
<connectionexpression></connectionexpression>
<returnvalue subreportvariable="totcost" tovariable="AValueFromSubRep">
<subreportexpression class="java.lang.String"></subreportexpression>
</returnvalue></reportelement>
</subreport>
now save the report and click on preview it will show the report with its sub report details as below
thats how you will do a subreport.
Lastly, If possible before closing, click on ad to support me.
