Crosstabs- display counts=0

[kykedrew9]

I am new in using ireport. I have a problem in using crosstab.

I want to have a sample report which contains this example:

FRUITS   Jan Feb Mar Apr May Jun Jul Aug Sep Oct Nov Dec Total

apple        0      0     0     3     0      0     0    0      0      0     0     5         8

bannana  0      0     0     8     0      0     0    0      0      0     0     10       18

Total         0      0     0     11   0      0     0    0      0      0     0     15       26

 

but i'm always ending up with this one:

FRUITS    Apr  Dec Total

apple          3    5         8

bannana    8    10       18

Total          11    15       26

I want to display other months even if their count is 0. what will i do?

 

thank you

[jasperforum]

the cross tab displays the zero's also . did you check the query?

[kykedrew9]

thanks for the reply :)

My query is

SELECT fruit.fruit_name,
               sales.sale_date,
      fruit.fruit_id
FROM fruit,sales

WHERE
   fruit.fruit_id=sales.fruit_id

zero will be displayed by the crosstab if there is one fruit sold in that particular month, but if there is no sales in that month it will not be displayed.

my row in crosstab is fruit.fruit_name

my column is sales.sales_date   --this is grouped by month

my detail_field is fruit.fruit_id. so it counts  the fruit_id in the table for each months

if  the ata in sales table is:

fruit_id  sales_date

    1            Jan

    1            Feb

    1            Mar

    1            Apr

    1            May

    1            Jun

    1            Jul

    1            Aug

    1            Sept

      1            Oct

    1            Nov

    1         Dec

   2           Jul

    2            Aug

    2            Sept

 

crosstabs will print this:

                    Jan  Feb Mar Apr May Jun Jul Aug Sep Oct Nov Dec

apple            1     1       1     1     1      1    1    1      1       1     1     1

banana        0      0       0    0      0      0    2    2      2       0     0     0

but if thedata in sales table is

fruit_id  sales_date

    1            Apr

    1            May

    1            Aug

    1            Sept

      1            Oct

    1            Nov

    1         Dec

   2           Jul

    2            Aug

    2            Sept

crosstabs will print this:

                     Apr May    Jul    Aug  Sep  Oct  Nov   Dec

apple               1    1        1       1       1      1       1        1   

banana          0      0         1    1         1       0     0     0

 

it's not printing other months like jan, feb and others

    i want to print all the months even if there is no sales in that month

thank you