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..
Best Answer
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
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
-Then in the Crosstab Data, in Parameters, I add in parameter
PDate
with expressionF{Date}
from the main dataset so that they will be linked together.Hope this helps anyone.