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 ...

Sunday, February 19, 2012

Parameterizing a JasperReports Crosstab

To parameterize a JasperReports crosstab, create a parameter in the main report and link it with a crosstab parameter.

This crosstab report lists the mileage associated with each department.  There is a summation of miles per year and by department.  "Total Mileage Exp" is a computation based on the Total Department sum.  The computation multiplies the sum by a passed in parameter "rate".

A Crosstab with a Computation
Previewing the report will display the following prompt which gathers a Float value, making it available to expressions as $P{rateMain}.


The data supporting the report is the following Excel spreadsheet.  The spreadsheet makes a datasource with four fields: Year, Department, Expense Reports Filed, Miles Driven.  "Filed" and Miles Driven are Integers.

Datasource for Crosstab
The main report is structured with a single Summary band.  The band contains a crosstab.  To configure the parameters, add one to the main report "rateMain" and one to the crosstab.  Link the two parameters together using the parameter name "rateMain" as a value expression for the crosstab parameter "rate".
Link a Main Parameter with a Crosstab Parameter
 In the crosstab tab, the expression will include a computation that references the crosstab (not the main) parameter.  The computation multiplies the crosstab parameter $P{rate}and a measure, the variable $V{Miles DrivenMeasure}.

Use a Computation that References a Parameter
To add a parameter to a crosstab, add a parameter to both the main and the crosstab.

UPDATE 9/6/2012 - Additional Screenshot for Date Rollups


This is a screenshot to go along with several of the comments listed below.

If you have a field from your datasource of java.util.Date, you can use the Crosstab Wizard to define date-based Row Groups.  For example, from a single date field on the Main Dataset (called "Date" in the Fields node), I'm prompted to build Row Groups for both Year and Month.


Two Crosstab Row Groups from a Single Main Dataset Field

Posted by at

25 comments:

  1. Tom ParryMarch 9, 2012 at 6:05 PM

    Your explanations are very good.
    Perhaps you could explain how to alter the sort order in a cross tab via a parameter - so far I have had no luck. Or do I have to use the comparator (like in Java)

    ReplyDelete
  2. CarlMarch 10, 2012 at 7:10 PM

    Hi Tom,

    I have a video coming out soon that shows how to do this using a custom Java class applied to a Comparator expression in the crosstab.

    ReplyDelete
    Replies
    1. CarlMarch 10, 2012 at 8:58 PM

      Here's a link to the post referencing the video. There are some notes about installing a Java class in iReport Deisgner.

      http://bekwam.blogspot.com/2012/03/dynamic-crosstab-sorting-in-jasper.html

      Good luck

      Delete
  3. °ღ•ÁŚℋℐŚℋ●•٠·August 13, 2012 at 1:23 AM

    Is there any way to use crosstab and subreport together, or we can use any thing else instead of crosstab.

    ReplyDelete
    Replies
    1. CarlAugust 13, 2012 at 9:29 PM

      I haven't put these two elements together, but this video on creating a report that has both a list and a chart might help. A lot of reports will have a one-to-one correspondence to a query, but you can create more complicated ones.

      http://bekwam.blogspot.com/2011/04/tutorial-ireport-list-component.html.

      Delete
  4. °ღ•ÁŚℋℐŚℋ●•٠·August 30, 2012 at 5:48 AM

    thanks Carl,
    Can u pls tell, how to add serial number in cross tab.

    ReplyDelete
    Replies
    1. CarlAugust 30, 2012 at 5:40 PM

      Hi,

      I'm not sure what you mean by serial number. Are you referring to something like an item number of a product or a numerical counter value?

      Delete
    2. °ღ•ÁŚℋℐŚℋ●•٠·August 31, 2012 at 1:47 AM

      numerical counter value, which will get incremented with each row of cross tab.

      Delete
    3. CarlSeptember 1, 2012 at 2:02 PM

      Hi,

      Take a look at this blog post: http://bekwam.blogspot.com/2012/09/a-crosstab-counter-in-jaspersoft.html.

      I wrote a small Java class that invoke via a Text Field Expression in the Row Group Header of the Crosstab.

      Delete
    4. AnonymousJanuary 5, 2015 at 6:04 AM

      Hi Carl ,

      I have using the counter jar class.I'm able to getting serial no. but whenever I',m generating the same its serial no is not resetting. serial number is generating from the last generated number. How to fix this issue ? Please help me.

      For example .

      Attempt 1: Counter No starting from . 1,2,3,4,5
      Attempt 2: 6,7,8,9

      Delete
  5. °ღ•ÁŚℋℐŚℋ●•٠·September 3, 2012 at 5:10 AM

    Thanks, and i also find the solution without using the Scriptlet, something like this.....

    1. Create at variable under Variables, named as GROUP_COUNT.
    2. Create a Group Header on the basis of which you want to count.
    3. Go to GROUP_COUNT properties and select the following:
    a. Variable class Integer
    b. Calculation Count
    c. Reset Type Report
    d. Increment type Group
    e. Increment group
    f. Variable expression $V{GROUP_COUNT}++
    g. Initial Value Exp. 0

    Now you can use this variable in the Cross tab it will print the serial number as per you group/column.

    This is thing which I tried and worked at grouping level.

    ReplyDelete
    Replies
    1. CarlSeptember 3, 2012 at 12:25 PM

      Hi,

      Are you using Groovy (rather than Java) for this?

      Delete
    2. °ღ•ÁŚℋℐŚℋ●•٠·September 4, 2012 at 1:49 AM

      Hi,
      Yes , I am using Groovy. What happened Carl.

      Delete
    3. CarlSeptember 4, 2012 at 6:45 AM

      The counter wasn't incrementing for me. I could pass the variable to the crosstab's text expression, but using "++" didn't seem to change the value. Using a count function in the variable (with the added group header), I got the same total listed with each expression.

      Are you on 4.7.0?

      Delete
    4. °ღ•ÁŚℋℐŚℋ●•٠·September 5, 2012 at 1:50 AM

      Actually it is 4.6.0 version,
      And when you are accessing this variable as a measure in the cross tab then you need to select the Calculation for that measure is Nothing.

      Delete
    5. CarlSeptember 5, 2012 at 7:15 PM

      Hi,

      I got this to work with 4.7.0. I skipped the auto-increment (++), so Groovy isn't required.

      Thanks for commenting.

      http://bekwam.blogspot.com/2012/09/the-improved-crosstab-counter-in.html

      Delete
    6. °ღ•ÁŚℋℐŚℋ●•٠·September 6, 2012 at 2:03 AM

      Ok

      Delete
  6. °ღ•ÁŚℋℐŚℋ●•٠·September 6, 2012 at 2:08 AM

    Hi Carl,

    I am having one more question, that i need to make a grouping in the cross tab.
    Let's take your above example, in that you are having the years on left hand side with serial number, now I want to add the months under that the years (suppose each year take any of two months)
    Can you help me how it can be possible.
    It looks like this:
    1. 2012
    Jan
    Mar
    2. 2013
    Jun
    Aug

    ReplyDelete
  7. CarlSeptember 6, 2012 at 5:13 PM

    Hi,

    I think this can be handled with the Crosstab Wizard. Use a java.util.Date field from the Main Dataset. When you walk through the wizard, you'll be presented with a row group option for the field and can group by the date parts (year, month, day).

    See a screenshot that I just put up on this post.

    ReplyDelete
    Replies
    1. °ღ•ÁŚℋℐŚℋ●•٠·September 7, 2012 at 12:57 AM

      Hi Carl,
      Thanks for your support, This thing gives the subgroup in front of the Parent group, I want that option in the below of the Parent Group(in same row) not in another column.

      Delete
  8. منNovember 28, 2012 at 7:54 AM

    Hi,
    I have a crosstab made by iReport, I want to click on a measured value and drilled into the detailed information that is summarized in this value. So far, I could not find any solution for it. I am just wondering if it is possible. If so, would you please help me in this issue?

    ReplyDelete
  9. UnknownSeptember 11, 2015 at 6:23 AM

    hi i want to map report variable to PAGE_COUNT in cross tab how i can i do this

    ReplyDelete
    Replies
    1. CarlSeptember 11, 2015 at 7:34 AM

      Hi,

      I'm not sure this will be available within the crosstab. Are you able to see it in a page header or page footer band?

      Delete
  10. UnknownMarch 9, 2016 at 8:16 PM

    Hi,

    How to hyperlink from a crosstab Row Group cell. pls help

    ReplyDelete
  11. AnonymousMay 20, 2016 at 1:45 PM

    Hello carl,

    I just start to use jasper report. it is a simple problem but it does not work.
    I just want to click on and it should show me the report below the link as a drop down.

    thanks

    ReplyDelete

Subscribe to: Post Comments (Atom)