2

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

Millie
  • 43
  • 1
  • 2
  • 8
  • I think you may need to split your questions up to get a good answer. The "Num column" question is not closely related to getting multiple crosstabs into a complex report. – mdahlman Feb 01 '12 at 17:32
  • 1
    The comment about the iReport forum not being active seems very strange to me. There are lots of questions and answers posted every day. (It's nothing like the total volume at StackOverflow... but it's steady.) – mdahlman Feb 01 '12 at 17:33
  • Why do you think the Num column is not closely related to the crosstab? – Millie Feb 02 '12 at 03:04
  • Both questions are related to crosstabs. I mean the 2 questions aren't closely related to each other. – mdahlman Feb 02 '12 at 17:56

2 Answers2

1

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.

Millie
  • 43
  • 1
  • 2
  • 8
0

'Num' column can be created by adding a row group and a count variable as it's expression. Also, for making correct counts, create a 'Date' group and put the crosstab in that group's footer.

bchetty
  • 2,231
  • 1
  • 19
  • 26