Crosstab in iReport

blog
Prajul P T's picture

iReport is an reporting tool used for designing jasper report in OpenERP. In OpenERP it is difficult to create a report that has a dynamic column. For instance consider an example of a project report that has details regarding the project task completion date. The task name will be the name of the column. In such cases, the cross tab in report comes handy.

Crosstab can be found in Palette in iReport. It can be dragged and dropped in to the report as needed.  Initially create a report. Drag the crosstab from pelette and drop it in the report. A wizard for the crosstab creation pops out. First we have ro select the datasource for the crosstab. I am using my main report data source for my crosstab.

Select your datasource and click next.

Next we have to select the row group. Row group defines the rows for grouping. In this case since the report deals with task for each project. Hence i am defining the row group as project which is the name of the project.

Next we have to select the column group. Column group corresponds to the name of the column. In this case the task name is the name of the dynamically varying column. So i am defining the column group as task which is the task name.

Next we have to select what detail of the task that correspond to each project has to be displayed. In my report i am giving the start date of the project.

Next we have to select the layout of the crosstab.  We can add row group, column group or guidelines based on the requirement. Now the crosstab appears in the main report. If we want to add some row group or column group, go to report inspector in iReport, Expand the crosstab right click on row group or column group  and click Add row group or Add column group as we need.

 

Cross tab is a useful tool in iReport and can be modified as our requirement. The project report will be generated as:

omarantonio's picture

Hi Prajul T. Thanks for your

Hi Prajul T.
Thanks for your help.

I am working with ireport and crosstab and right now i have a issue with a final report.
I need see in each column, the months of year but in order, for example: january, february, march, and so on, but I can to display that columns but in disorder.

The query was written in postgres:

with client as
(

select codclient, sum(total) as totalc
from v_sales
group by codcliente
order by totalc desc

)

select
vc.*,
cp.name as periodname,

date_part('year',cp.enddate) as anio,
date_part('month',cp.enddate) as numeromes,

date_part('month',cp.enddate) || '-' || initcap(fnc_period(cp.startdate, 1000000) ) as namecomplet ,

cp.periodno,

tc.totalc,
cp.enddate

from v_sales vc

inner join c_period cp
on vc.c_period_id = cp.c_period_id

inner join client tc
on tc.codcliente = vc.codcliente

order by namecomplet asc, anio asc;

Thanks a lot...!
Omar

Anonymous's picture

I have created a crosstab, if

I have created a crosstab, if placed in Summary section it works fine, but if in any other section means it is not giving any errors but it is not shown.
I think if we give increment type as Report it should work but it is not. Kindly help.

Anonymous's picture

Hi , In my report i am

Hi ,

In my report i am getting null value for one column even though data is there .Can any one help me and thanx in advance

Anonymous's picture

Hi, Can you please tell me

Hi,

Can you please tell me that how can we add a Serial Number in cross tab.

Anonymous's picture

hi sir,i have a problem when

hi sir,i have a problem when i keep all my subreports one by one in detail band all the reports are overlapping..if i keep them in summary then i have to set it like giving gaps between subreports...so if there will be more data for first subreport then it overlaps with second sub report how to remove it?
and in crosstab if complete row or column is null that is no value i want it to show 0(zero) but it doesnot give that row or column at all...what to do?
thanks...plz reply

Prajul P T's picture

Hi,     For your first

Hi,

    For your first query, ie; the overlapping of subreports , first select the subreport from main report go to properties, there you can see a field "Position Type". Set it to "Float" so that the subreport will not overlap.

      For Your second query, if the data in a crosstab is null it will be shown as null in the report. Please check weather you are retriving the data correctly. For example:

In the above report the the column group is the partner name and the measure is the phone. if there is no phone number for a partner, it will be shown as null.

Please check you are retriving the data correctly.

Hope this helps...

Anonymous's picture

Hi sir ,thanks for the reply

Hi sir ,thanks for the reply first answer which you gave has worked...my second doubt was if we have a task1 in row andtask1...n in column and if its having no value then i should get null/0 but that column is not visible at all what to do...for betteer understanding of my doubt i am trying to show it as table
task1 task2 task3 task4 task5 task6 total
task1 5(somevalue) 6 7 0 0 10 x value
task2 0 0 0 0 0 0 x value
task3 0 0 0 0 0 0 x value
task4 1 2 4 0 0 6 x value
task5 1 2 3 0 0 7 x value
task6 1 1 1 0 0 8 x value
total x x x x x x x value

if this is a cross tab,as u can see that columns task4 and task5 and rows task2 and task3 are having null values so in ireports its completely deleting or not showing that but i want it to display as i shown in the example not considering whether row/column is null...please help waiting for reply...thanks in advance...

Anonymous's picture

hope you will understand the

hope you will understand the above given example..as its not that clearly visible..but please clear my doubt..sorry for any kind of trouble in clarity..

Prajul P T's picture

Hi,      Please send me your

Hi, 

    Please send me your data retrieving details like whether you are using a JDBC connection or XML etc. It would be better if i could have a look at your .jrxml file.

murali's picture

HI, I am Working With

HI,

I am Working With iReport(jasper reports) and OpenERP 6.1.
I downloaded the integration addon of jasper_reports from community.
I am facing some problems while integrating it.
Can U Upload a blog for this?

Regards,
Murali