Featured Post

Applying Email Validation to a JavaFX TextField Using Binding

This example uses the same controller as in a previous post but adds a use case to support email validation.  A Commons Validator object is ...

Tuesday, June 28, 2011

Expanding a Crosstab in Jaspersoft iReport Designer

To create a crosstab report (sometimes called a "Pivot Table") in Jaspersoft's iReport Designer, simply drag a Crosstab component from the palette to a Summary band and configure through the wizard.  However, if you need more than 2 row or 2 column groups in the crosstab, you'll need additional configuration.

Jaspersoft's iReport Designer features a New Crosstab wizard that will guide you through adding a Crosstab component to a report.

The wizard is limited to 2 row groups and 2 column groups.  For most concise reports, this is sufficient.  Too many row and column groups can be disconcerting because various totals will appear throughout the tabular data.

Row Groups

If you need additional row and column groups, add a Row Group and then build a set of totaling cells into the cross tab.  This example works with the following spreadsheet.  A 2 x 2 crosstab was built with Year and Region forming the initial set of rows and Dept Name and Dept ID forming the initial set of columns.  This is the maximum set that can be configured using the wizard.

The spreadsheet has an additional column, "Sr. Officer", that will be added as an additional row group.

Data Supporting a Crosstab Report

Start by right-clicking in the Report Inspector and selecting "Add Row Group".

Add a New Row Group
The new row group will appear in the report's Crosstab tab.

Crosstab with Sr. Officer Variable Added
Total

The report can be previewed as this point and the data will total correctly.  To add in the Sr. Officer subtotaling, components need to be added from the palette for the label (Static Text) and each of three totals: Dept ID, Department, and Total.  The totaling components are Text Fields.


Crosstab with Totals Created

Each of the three Text Fields are configured to use the same variable $V{BudgetAmount_Measure}.  The type is set to java.lang.Integer.

The result is a report that includes the extra data.  You may want to apply background colors to make it appear more consistent with the other table cells.  (I skipped the extra formatting to make the cells stand out.)

Crosstab Report with Row Group Created Outside of Wizard
Most crosstab reports can be rendered using 2 row groups.  Jaspersoft's iReport Designer provides a convenient wizard walking you through crosstab creation.  If you need more row groups, add them manually then add extra components from the palette for the totals.
Posted by at

29 comments:

  1. AnonymousJanuary 14, 2012 at 4:28 AM

    this post helped me a lot.
    can u tel me how to add additional measure.

    ReplyDelete
  2. CarlJanuary 14, 2012 at 8:15 AM

    Hi,

    I uploaded a tutorial on YouTube showing how to add a measure. Look for "Adding a Measure to a Jasper Reports Crosstab".

    http://www.youtube.com/user/bekwam

    Good luck.

    ReplyDelete
  3. AnonymousFebruary 17, 2012 at 6:03 AM

    Hi,
    How to pass a paramater/field value of main report to cross tab. I want to use/print main report parameter value in cross tab.

    I have tried in one way but getting compilation error.
    1. I have added a crosstab parameter
    2. In Value Expression I have given $P{MAIN_REPORT_PARAM}

    If I compile now I am getting following compilation error.

    net.sf.jasperreports.engine.design.JRValidationException: Report design not valid :       1. Incompatible expression class for crosstab parameter AMOUNT_FORMATTER.      2. Parameter not found : CB_AMOUNT_FORMATTER     at net.sf.jasperreports.engine.design.JRAbstractCompiler.verifyDesign(JRAbstractCompiler.java:258)     at net.sf.jasperreports.engine.design.JRAbstractCompiler.compileReport(JRAbstractCompiler.java:140)     at com.jaspersoft.ireport.designer.compiler.IReportCompiler.run(IReportCompiler.java:491)     at org.openide.util.RequestProcessor$Task.run(RequestProcessor.java:561)     at org.openide.util.RequestProcessor$Processor.run(RequestProcessor.java:986)

    Thanks in advance.

    ReplyDelete
    Replies
    1. CarlFebruary 19, 2012 at 11:57 PM

      Hi,

      Add a parameter to the main report, add a second parameter to the crosstab, then link the two together.

      See this post for more details.

      http://bekwam.blogspot.com/2012/02/parameterizing-jasperreports-crosstab.html

      Delete
  4. Asha SreekanthApril 23, 2012 at 2:03 AM

    how we reducing space in the Crosstab in Jaspersoft iReport

    ReplyDelete
    Replies
    1. CarlApril 24, 2012 at 7:24 AM

      Hi Asha,

      To resize the columns and rows in the crosstab, select the Crosstab tab. The Crosstab tab is located at the bottom of the screen and may be called "Crosstab 1". Selecting this control will let you manipulate the parts of the crosstab by dragging object handles within the crosstab table.

      When you are finished resizing the crosstab, select the tab "Main report" to return to the report.

      Good luck

      Delete
  5. AnonymousMay 18, 2012 at 7:27 PM

    Can iReport creata a chart directly from the results of a crosstab?

    ReplyDelete
    Replies
    1. CarlMay 19, 2012 at 8:00 AM

      Hi,

      I haven't accessed parts of the crosstab from the main report.

      Some databases have a PIVOT function (Oracle 11g, SQL Server) that can render the crosstab using SQL. However, iReport may have a problem bringing in the metadata to generate the report because of the varying number of columns.

      If you know the domain of the columns -- in this post's case the set of Departments -- then you can build a query where each Department's value is a column for use in an iReport chart.

      Delete
  6. AnonymousJuly 1, 2012 at 9:29 PM

    Hi Carl,

    How to do the right sorting in Crosstab? Because everytime I create a crosstab the headers is not sort properly.

    regards,

    weng

    ReplyDelete
    Replies
    1. CarlJuly 2, 2012 at 6:32 PM

      Hi Weng,

      Take a look at this blog post: http://bekwam.blogspot.com/2012/03/dynamic-crosstab-sorting-in-jasper.html. It does a little more than you may be asking -- sorting on a parameter rather than a static sort -- but I think it covers your question.

      There is a video embedded in the post. At about 1:33, take a look at the Properties Panel, specifically, the setting for Order.

      Good luck.

      Delete
  7. Usman AsifAugust 7, 2012 at 2:49 AM

    hi carl
    Did you know the way to make drill down reports in ireport 4.60

    ReplyDelete
    Replies
    1. CarlAugust 7, 2012 at 9:40 PM

      Hi Usman,

      I haven't done this with pure iReport. When I've had a requirement for drill down, I've used the JasperReports Library to integrate with JSP. I published reports for each of the levels and coordinated their interaction with links embedded within the report and a parameter (both a JasperReports parameter and a URL parameter).

      Delete
  8. UnknownSeptember 20, 2013 at 11:28 AM

    Hi Carl,

    How to Calculate the Row Wise Total in the End of the Column.

    Like ,

    A B C TOTAL
    10 10 10 30

    Regards,
    Karthik

    ReplyDelete
    Replies
    1. CarlSeptember 22, 2013 at 9:13 AM

      Are you using a crosstab for this summation? If so, the crosstab control will create a total row automatically.

      On the other hand, if you are creating a plain report (say columns A, B, and C are known in advance), then add a new text control to the Detail pane. In the text control, specify an expression that is a calculation (A + B + C).

      Delete
    2. UnknownSeptember 23, 2013 at 3:20 AM

      Thanks Carl ,

      Am using Crosstab Only,

      I have Columns Like SNO,ITEMID,ITEMDESC ,GROSSQTY ,WEEKNOS

      I Added Row Groups --- SNo, ITEMID

      and Column Groups --- WEEKNOS .

      and Measure --- GROSS QTY

      Now , I need Ouput like ,

      SRNO ITEMID ITMDESC 31 32 33 34 35 36 Total

      1 CA-901 CA-901 DRILL 1 3 4 5 3 2 20

      i need that Total 20 .. in end .

      Can you Explain , How i model cross tab for
      the above output.

      this is my mail id krish.karthikn@gmail.com

      Thanks & Regards ,
      Karthikeyan K



      Delete
  9. InformatikNovember 6, 2013 at 8:56 AM

    hi carl,
    Friend'm beginner in ireport and I see that you have extensive experience with this, I have a problem with crosstabs: group of columns I have a variable that you want to have a rotation to the left when running for any file it does but for a html file does not, and can not get enough corect answer for this, I hope you can help me and that you have a great knowledge on the subject thanks brother.

    Sincerely

    Albert Espinoza
    Merida, Venezuela

    ReplyDelete
  10. AnonymousNovember 26, 2013 at 7:48 AM

    How to add a main heading to this cross tab report so that it streches according to the streching of y-axis elements ?

    ReplyDelete
    Replies
    1. CarlNovember 26, 2013 at 7:56 AM

      Hi,

      I haven't had to do this. Have you tried selecting both the y-axis header and the cross tab and setting the Stretch Type to Relative to Tallest Object?

      Delete
  11. Suryakant SoniApril 7, 2014 at 9:16 AM

    Hi Carl,

    I have a Child Report (Having CrossTab in it). This child report is getting a Parameter from Main Report.

    Now in one cell of Crosstab i have added a Hyperlink to drill down to other Report (which takes three parameter to run). In my Crosstab Query, i have all those there values, when i am passing 4 parameters (4th is "_report"), i am getting error "com.jaspersoft.jasperserver.api.JSExceptionWrapper:
    Report design not valid :
    1. Parameter not found : auditproject_id
    2. Field not found : asset_id
    3. Field not found : policyset_id "


    Please help.

    ReplyDelete
    Replies
    1. CarlApril 14, 2014 at 11:26 AM

      It sounds like the linkage between the reports may be broken. Try declaring parameters auditproject_id, asset_id, and policyset_id in the child report and setting them to the parameters and fields from the parent report. You may want to name these parameters different in the child report so as not to confuse them with the parent.

      Delete
  12. AnonymousApril 27, 2014 at 12:21 PM

    Hi Carl,
    I've a problem.I've created a pie chart using ireport designer.....everything is fine until I click on 'preview' button.....on clicking it no preview is generated,no .jasper file is generated ,it is simply going back to 'designer' .Please help.I've included all the JAR files and have tried everything possible.Thank you.

    ReplyDelete
  13. FodedoKadoiJune 25, 2014 at 5:38 AM

    hi carl
    Did you know the way to make dynamic report width?

    ReplyDelete
    Replies
    1. CarlJune 25, 2014 at 10:37 AM

      I don't see a stretch width property matching the stretch height properties mentioned a few comments up. Maybe you can use a parameter passed to the report's width property and calculated for the crosstab, subtracting the margins?

      Delete
    2. FodedoKadoiJune 26, 2014 at 3:00 AM

      thanks carl,
      did you know how to pass a parameter to the report's width?

      Delete
  14. AnonymousJuly 28, 2014 at 7:28 AM

    Hi Carl,

    I am using crosstab. I have taken amount as a Measure.
    Can you please tell me how to add total at the end?

    Pls help.

    ReplyDelete
  15. AnonymousNovember 11, 2014 at 12:06 AM

    Currently in my report i have a column group at the end.I need to add a row 'Remarks' at the end of the report.I am not possible to add this because whenever i try adding new row group it is coming automatically after previous row groups.

    ReplyDelete
    Replies
    1. AnonymousDecember 28, 2014 at 5:04 AM

      I'm also facing the same issue , Please let me know any possibility to achieve this type of formatting.

      My email id is sivesh032@gmail.com

      Delete
  16. AnonymousDecember 4, 2014 at 6:25 AM

    Hi,

    Please let me know how can i print data in cross tabs without the blank cells.I want each and every cell filled with a unique data and grouping done on the basis of column header.

    ReplyDelete
  17. AnonymousFebruary 25, 2015 at 12:21 PM

    Hi
    I need help ,

    I have main report , and Crosstab based on dataset, I need to display a crosstab as detail of main report row.

    when i test the crosstab with hardcoded ID in the dataset query it work fine.
    But when i create a parameter and link it to the id of main report , it seems not working .

    So, I need to filter the dataset by parameter wich take his value from a main report field .
    but it doesn't work.
    is ther a way to solve that.

    Thanks

    ReplyDelete

Subscribe to: Post Comments (Atom)