Jasper iReport Complex Crosstab Column and Row

Question

I want to create a crosstab report with Jasper iReport that has layout like this

**Date: 1 January 2012**
    Num  Transaction        Blue      Red       Total
    1   | Bank Wire     |     2   |    5    |     7
    2   | Credit Card   |     1   |    2    |     3
    3   | Cheque        |     1   |    1    |     2
    ------------------------------------------------
    Total 
    Bank Wire & Credit  |     3   |    7    |    10
    ------------------------------------------------
    Total All           |     4   |    8    |    12  


**Date: 2 January 2012**
    Num  Transaction        Blue      Red       Total
    1   | Bank Wire     |     0   |    1    |     1
    2   | Credit Card   |     2   |    2    |     4
    3   | Cheque        |     1   |    1    |     2
    ------------------------------------------------
    Total 
    Bank Wire & Credit  |     2   |    3    |     5
    ------------------------------------------------
    Total All           |     3   |    4    |     7 


    --------------------------xx-----------------------
    --------------------------xx-----------------------
    Total 
    All Bank Wire 
    & Credit            |     5   |    10   |    15
    ----------------------------------------------------
    Total
    All Transaction     |     7   |    12   |    19
    --------------------------xx-----------------------
    --------------------------xx-----------------------

It is very complex, and in my own logic thinking I feel like its a crosstab in a subreport? And also Crosstab doesnt allow addition of single column or row, only row groups so that is a bit difficult..

Right now I can only create a crosstab with a Transaction, Blue, Red and Total column, and Total All Bank Wire & Credit and Total All Transaction

I still can't do Num column, though!

Also, I can only make a crosstab counts in the whole date range (1 to 2 January) instead of for each date.. so I still can't do Total Bank Wire & Credit and Total All according to each date

Jasper iReport forum also doesn't seem to be active..

Solution

I'm using SQL. I solved the date issue by putting my crosstab in the detail band, and use a sub dataset for that crosstab.

-My main dataset has parameters FromDate and ToDate, its query looks something like this

SELECT Date FROM Table WHERE Date BETWEEN $P{FromDate} AND $P{ToDate}

So now my main dataset has field Date

-Then I created new sub dataset with a parameter PDate and its query is sort of like this

SELECT columnName FROM Table WHERE Date = $P{PDate}

-Then in the Crosstab Data, in Parameters, I add in parameter PDate with expression F{Date} from the main dataset so that they will be linked together.

Hope this helps anyone.