Crosstabs - No Data / No Columns

[gotque]

Hello,

Firstly, sorry for my english, I'm French and I will try to express myself the best possible

I try to do a crosstab rperenant the number of purchase per quarter in several stores of my group.

In my SQL table I have this:

ID | Date | Products |
1 | 20080101 | Tshirt
2 | 20090101 | Sweater
3 | 20090104 | Tshirt
...
99 | 20120201 | Shoes

My goal is to have an array like this
                ||                  2008               ||                  2009               || ...

                || 1er | 2nd | 3eme | 4ieme || 1er | 2nd | 3eme | 4ieme ||  ...
 
Tshirt       ||  1    |  2    |   4     | 3         || 0    | ....... | ....... | .......     ||  ...

Sweater  || 0    | ....... | ....... | .......    || 0    | ....... | ....... | .......     ||  ...

Shoes     || 0   | ....... | ....... |......     . || 0    | ....... | ....... | .......     ||  ...

Total        ||  1   | .......| ....... | .......     || 0    | ....... | ....... | .......     ||  ...


I succeed very well except when there isn't sale in one quarter. Indeed, if no sales are made ​​in one quarter (All lines to zero), the column does not appear.

I want to make them appear even if it is zero.

Have you the solution.

I work in iReport 4.1.1

thank you

Post Edited by gotque at 02/21/2012 12:30

[mennei]

 Hi,

How did you create the crosstab? How do you fill the "Measure"? (the data in the crosstab).

[gotque]

Thank you for your reply,

So already my SQL query looks like this:

 

SELECT

       SUBSTRING(RI_DATE_INSCRIPTION,1,4)) AS C_DATE,

      QUARTER(RI_DATE_INSCRIPTION) AS C_TRIM,

     C_PRODUCTS,

     COUNT(C_ID) AS NB_VENTE

FROM

      CARNET

GROUP BY

      C_DATE, C_TRIM

HAVING CATEGORY IS NOT NULL

ORDER BY D_RANDO ASC;

 

A little explanation:
- C_DATE me back the years that I've extracted the fields RI_DATE_INSCRIPTION
Ex: To date 20120101 (RI_DATE_INSCRIPTION), C_DATE = 2012
- C_TRIM returns the number of quarter
Ex: 1,2,3 or 4
- C_PRODUCTS represents the type of product
Ex: Shoes, T-Shirt ...
- NB_VENTE counts the number of sales

To create the Crosstab I drag the Crosstab in Summary part and I get the following information:
- Row 1 Group: C_PRODUCTS / Unique
Row 2 Group: None
- Column 1 Group: C_DATE / single
Column Group 2: C_TRIM / single
- Data: Measure: NB_VENTE / Function: Count

I put a preview of my problem as an attachment, you see that for 2004, only quarter 2 and 4 appear as During the quarter 1 and 3, no sale has taken place (no data in the table). Instead of automatically delete the column, I would like to see appear with that of 0.

Thank you in advance

QG

[mennei]

 some questions:

1. Is the crosstab on the summery band and the query is the main query? Or you use dataset? 

2. Is the query give you zero (in the 1,3 quarters for 2004), when you run it outside of iReport?

3. What are the values you specify in the crosstab data? (right click on the corsstab element -> crosstab data)

[gotque]

1. Is the crosstab on the summery band and the query is the main query? Or you use dataset?

Yes, my crosstab is in the summary band and the query described above is the main query

 

2. Is the query give you zero (in the 1,3 quarters for 2004), when you run it outside of iReport?

Since there are no data for Quarter 1 and 3 of 2004, when I execute my query outside iReport, it returns me "No data found" what should be 0 in iReport, I think.

 

3. What are the values you specify in the crosstab data? (right click on the corsstab element -> crosstab data)

See attachment file

 

Thank you  again

 

QG

[mennei]

 I think you should check number 2. If the query didn't return 0, how iReport will know to convert the "no data" to "0"?

Also, when you choose the data musare in the report inspector window, you have the "Blank when null" attribute. The defualt is false, so in the default behavaior the "no data" will disapper.

Good luck.

[gotque]

I understand that iReport can not do with it but why did not I get all the same on some line 0? That's why I do not understand. Sometimes it will not interpret the data with a 0 and sometimes not.

 

What I want is a table with the years from 2002 to 2011, cut into 4 quarter but I'm not always the 4 quarter.

 

I will try to change my request.

 

thank you again

[am0rales]

I think you can resolve this issue by addding a UNION with the value of your measure to 0 to your query and aggregating then the result of that UNION. Then you have all the combinations of your quarters, even if you have no data for them.

Example for your query:

 

SELECT C_DATE, c_TRIM, C_PRODUCTS, SUM(NB_VENTE) AS NB_VENTE

FROM

(

(SELECT

 

       SUBSTRING(RI_DATE_INSCRIPTION,1,4)) AS C_DATE,

 

      QUARTER(RI_DATE_INSCRIPTION) AS C_TRIM,

 

     C_PRODUCTS,

 

     COUNT(C_ID) AS NB_VENTE

 

FROM

 

      CARNET

 

GROUP BY

 

      C_DATE, C_TRIM, C_PRODUCTS

 

HAVING CATEGORY IS NOT NULL)

 

 

UNION 

(SELECT

 

       SUBSTRING(RI_DATE_INSCRIPTION,1,4)) AS C_DATE,

 

      QUARTER(RI_DATE_INSCRIPTION) AS C_TRIM,

 

     C_PRODUCTS,

 

     0 AS NB_VENTE

 

FROM

 

      CARNET

 

GROUP BY

 

      C_DATE, C_TRIM, C_PRODUCTS

 

HAVING CATEGORY IS NOT NULL ) TUNION

 

GROUP BY C_DATE, c_TRIM, C_PRODUCTS

ORDER BY  D_RANDO ASC;

 

 

[kasunhnw]

Same issue no solution