How to include all dates in a range on crosstab

[skeeve]

Hi there,

I'm wondering if anyone knows of the best practice to get all of the dates in a range reflected in the rows of a cross-tab report, even if there are no records for some of the dates in the range.

 

So, as an example, I run the crosstab with rows for each date in a range, and get:

 

Code:

Date   | some info | more info | Total
1/1/07 |     4     |     8     |  12
1/2/07 |     3     |     2     |   5
1/3/07 |     4     |     5     |   9
Total  |    11     |    15     |  26

 

 

But, let's say there was no data for 1/2, it would be:

 

Code:

[code]Date | some info | more info | Total 1/1/07 | 4 | 8 | 12 1/3/07 | 4 | 5 | 9 Total | 8 | 13 | 21

 

I want:

 

Code:

[code]Date | some info | more info | Total 1/1/07 | 4 | 8 | 12 1/2/07 | 0 | 0 | 0 1/3/07 | 4 | 5 | 9 Total | 8 | 13 | 21

 

Any ideas on how this might best be done using JasperReports? I've toyed with creating a SP to add dummy rows for dates that don't exist, but this seemed wrong somehow.

 

Thanks for your thoughts!

 

Post edited by: skeeve, at: 2007/02/01 18:32

 

Post edited by: skeeve, at: 2007/02/05 20:11

Post edited by: skeeve, at: 2007/02/05 20:13

[skeeve]

Hi again,

Sorry to reply to my own post, but I've been digging and digging, and still can't figure out an answer to my problem. So, I'm re-asking my question in the hopes that someone might have an answer out there for this one.

 

Thanks again! I'm hoping to get strong enough in Jasper to start answering questions myself at some point.

 

Skeeve

[lucianc]

This functionality is unfortunately not present in JasperReports. You can post a feature request here.

 

Depending on your particular scenario you might be able to workaround this by writing a custom data source which in addition to the original data would return rows which contain the required values for the "date" field and empty/default values for the other fields.

 

Regards,

Lucian

[skeeve]

Ok, Lucian. Thanks for the info. I'll try the custom data source and I'll post a feature request as well.

Regards,

Skeeve

[ktalarico]

Thanks for posting this question, Skeeve. I was looking for this as well. I ended up doing it all in my SQL query, but the downside was somehow I ended up with an extra row at the bottom of my crosstab with no date and all zeros across. I could live with that, however. But if anyone knows how to get rid of it, I'd welcome suggestions.

 

Post edited by: ktalarico, at: 2007/10/12 02:19 [file name=CROSSTAB.jpg size=47743]http://www.jasperforge.org/components/com_joomlaboard/uploaded/files/CROSSTAB.jpg[/file]

Post edited by: ktalarico, at: 2007/10/12 02:22

[skeeve]

Hey there,

Doing it all in SQL is great, but how did you do it? Did you do it in a stored procedure? Or in the SQL query itself?

 

Thanks a lot.

[ktalarico]

I did it all in SQL with outer join.

 

select SHIFT_DATE,

nvl(round(24 * (psa.shift_end_datetime-psa.shift_start_datetime), 1),0) as total_hour,

nvl(c.external_code,c.leave_type_code) ||'('||c.leave_type_code||')' as leave_type_code

 

from

pa_shift_allocation psa,

leave_type_code c

where

psa.leave_type_code(+)=c.leave_type_code

AND psa.shift_date(+) like '%$P!{p_month}'

and psa.status(+) = 'A'

and psa.assignment_type_code(+) = 'L'

Post edited by: ktalarico, at: 2007/10/17 12:26