Pages

Wednesday, October 13, 2010

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.


 


Tuesday, October 12, 2010

creating jasper sub-report using iReport

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.