Crosstab in iReport

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:

Comments

Prajul P T: 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... ".

Leave a Reply Cancel reply

Anonymous says:

June 12, 2012 at 5:40 pm

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

Reply
1. Prajul P T says:
  
  June 12, 2012 at 7:49 pm
  
  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…
  
  Reply
  1. Anonymous says:
    
    June 13, 2012 at 6:07 pm
    
    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…
    
    Reply
    1. Anonymous says:
      
      June 13, 2012 at 6:10 pm
      
      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..
      
      Reply
      1. Prajul P T says:
        
        June 18, 2012 at 12:48 pm
        
        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.
      2. murali says:
        
        June 25, 2012 at 4:03 pm
        
        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
      3. Prajul P T says:
        
        June 25, 2012 at 6:13 pm
        
        Hi,
        
        Can you please provide the details regarding iReport version you are using?
      4. murali says:
        
        June 29, 2012 at 11:30 am
        
        Sir,
        I am facing an issue while using Cross tab.
        I am getting a empty row while converting rows values into columns.
        I have production of products and daily that products production will be entered. I want report as columns will be shown with month-year(jan-12) like that and rows will be with product name
      5. murali says:
        
        June 29, 2012 at 12:33 pm
        
        Sir,
        I have seen many places people are writing java code to do some things like Dynamic Images and all.
        Can you please post a blog on how to add java code to iReport?
        Regards,
        Murali
      6. Sameer says:
        
        July 30, 2012 at 12:23 pm
        
        We have a requirement wherin we need to create a multi tab excel report. But, i dont have any idea on how to create it as i have worked only on single PDF/XLS creation.
        Any help from your side would be highly appreciated.
        Thanks
        Regards
        Sameer
      7. Prajul P T says:
        
        July 30, 2012 at 7:52 pm
        
        Hai,
        
        Printing a XLS report in multi sheet if you know how to create it in a single sheet is easy. Try to fetch the data in such a way that we will be able to group the data to be displayed in page one and two in to two groups. Create a new group in iReport to group the data fetched. Now select the group created and go to properties and check "Start on new page" option. Ensure that in page properties, "Ignore Pagination is not selected". Create a new property for the page as "net.sf.jasperreports.export.xls.one.page.per.sheet" and set it as "true". Now compile the iReport and print the report. The data in group 1 will be displayed in sheet 1 and data in group 2 will be in sheet 2.
      8. Sameer says:
        
        July 31, 2012 at 10:47 am
        
        Hi Prajul,
        Thanks for the prompt reply.
        But, the problem here is; we have to create a sinngle XLS file which will have details of lets say under first tab master account details, on second tab child account 1 details, on third tab child account 2 details and so on..
        i am not able to figure out how this can be achieved and where do i need to write the java piece of code?
        can u hep me by taking an example or any step by step process.
        Thanks
        Sameer
      9. Prajul P T says:
        
        August 6, 2012 at 10:25 am
        
        Hi,
        
        Based on your given example, i think you could use subreport with JDBC datasource. Create two subreport let say A and B where A is for main accounts where account type is 'View' and B for normal reports. Create a new main report and in the main report, fetch all the accounts that has the type 'View'. Create a group to group this accounts. In the group properties, set start on a new page property. The details band of the main report should contain the subreport A which is for the account of type "View". Pass the accont number from the main report to the subreport as a parameter.
        
        In sub report A fetch details of the accounts that comes under the main account that is passed from main reports. The pattern of the sql query should be:
        
        <Query to fetch account details> where parent_id in $P{account from the main report}
        
        create a group in subreport A which groups the child accounts fetched. The details band of the subreport A should contain the subreport B. Set the property of the group to start on a new page. Pass the account number of the child account fetched in to the subreport B as parameter.
        
        In subreport B, display the details of the child account passed from the sub report A.
        
        I think this could help you……
      10. Anonymous says:
        
        November 6, 2012 at 7:27 pm
        
        i am facing one problem in cross tab.. when cross tab column group is field value is null then i want to restrict to create new column ..
      11. Aman says:
        
        November 3, 2012 at 12:27 am
        
        Hi Prajul,
        I have created a multiple sheet report in single excel sheet by using the same method you mentioned. My requirement is that I have to give names to these sheets. As per my project requirement, I have to use ireport 3.0.0.
        I tried using the property tag in my jrxml to define sheet names as but it is not working. Can you please suggest a way to handle it.
        Thanks in advance,
        Aman
      12. Prajul P T says:
        
        November 9, 2012 at 12:35 pm
        
        Hi,
        
        Please use 'net.sf.jasperreports.export.xls.sheet.names.all' property. The report name shoud be passed in the form '/Sheet1/Sheet2/Sheet3'.
      13. oy says:
        
        November 22, 2012 at 1:08 am
        
        Hello,
        I’m trying to print data in different excel sheets. I did that you said until “Create a new property for the page as “net.sf.jasperreports.export.xls.one.page.per.sheet” and set it as “true””.
        Could you please explain how and where I can create a new property?
        Thank you
      14. Prajul P T says:
        
        November 23, 2012 at 1:18 pm
        
        Hi,
        
        In report inspector, right click and select the properties. Properties window opens up as shown below:
        
        You could see a entry called Properties. By default there will be 3 properties set. To add, click on the small button in right side of the property. A new window opens up as shown below:
        
        Press on "Add". You could add the property name and value here.
      15. oy says:
        
        November 23, 2012 at 8:19 pm
        
        Hello Prajul,
        Thank you for answering.
        I still can’t print my report on different pages.
        Let me explain from the beginning. I use iReport 3.5.1 version.
        I created three new groups and I have a subreport in each group. I try to print each group/subreport on different page. I checked “Start on new page” option for each group. I verified that Ignore Pagination is not selected for each page. I created new property for each page “net.sf.jasperreports.export.xls.one.page.per.sheet” and set it for “true”.
        Did I miss something?
        When I was adding new property I had “0 property set” by default but you told me that it’s supposed to be three properties by default. Can it be the problem?
        There is a Group Expression set by default as “$V{PAGE_NUMBER}” in the group properties. Should I change it?
        Thank you in advance for your help
        OY
      16. Prajul P T says:
        
        November 26, 2012 at 10:37 am
        
        Hi,
        
        Please verify the property "net.sf.jasperreports.export.xls.one.page.per.sheet" is set for main report ie; the main report file that contain the groups and the subreports. Also please verify the Ignore Pagination is not set for the main report. If these properties are set, i don't see any problem in your report. Can you try it with the latest iReport available. There are many new features and bug fixes available for new report.
Anonymous says:

August 30, 2012 at 3:23 pm

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

Reply
Anonymous says:

March 14, 2013 at 12:30 pm

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

Reply
Anonymous says:

March 14, 2013 at 2:58 pm

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.

Reply
omarantonio says:

March 19, 2013 at 9:07 pm

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

Reply
1. Neeraj kumar gupta says:
  
  March 2, 2015 at 12:11 pm
  
  set the order descending or acceding in crosstab report properties also after selecting the details fields.
  
  Reply