iReport Group with Date and Incrementing Date?

[izreena]

 I have a report that is grouped by Date.

I take in parameters $P{fromDate}  as 1 January and $P{toDate} as 3 January.

Problem is, my report only displays for the record available which is on 2 January. There are no records of 1 January and 3 January in the database (no data). What I want is that my group to print all 1 January, 2 January, and 3 January, but for no data, it will print out zero. For now it totally ignores 1 January and 3 January.

So what I'm trying to do, I have a variable $V{varDate} which takes value from parameter $P{fromDate}. The purpose of the variable is because I want to increment the value.

Let say $P{fromDate} is 1 January, I want to increment the $V{varDate} to 2 January, 3 January and up until my $P{toDate}.

 

The reason for this is because I want to put in $V{varDate} as my Group expression. That way maybe $V{varDate} will display value os 1, 2 and 3 January?

 

I am a bit lost here :( Please see the code, I have write in my expectations and what the problem is.

Thanks in advance.

Code:

Post Edited by themillie at 03/06/2012 09:15

[cricketfreak]

Hi,

 

Here is the query u have to write

select coalesce(id,0) as data,tdate from test where tdate between '01-jan-2010' and '03-jan-2010' group by id,tdate order by tdate

 

[izreena]

 I dont think my query has problems since I already put in parameters in my query.

[cbarlow3]

I continue to recommend Anthony Molinaro's excellent SQL Cookbook, published by O'Reilly.  This problem is discussed under section 9.10 "Filling in Missing Dates".  The syntax of the solution depends on what flavor of SQL you're using.  The book always gives sample solutions for DB2, Oracle, PostreSQL, MySQL, and SQL Server.  Personally, I'm using DB2 on a daily basis, and the solution is to use a recursive common table expression (CTE) to generate a temporary table of all dates within the range, and then you can use a LEFT JOIN to link that table to your actual data.  I've pasted an example of how that's done below.  The DatesInRange CTE has a non-recursive section first that refers to the parameters, and then a recursive section that lets it generates all the other dates.  Then the main query links this list of dates to your actual data and groups, counts, and sorts it.

I had to use CORE.ENV in my non-recursive FROM section.  In my database, that's just a table that has a single row ("ENV" standing for "Environment").  Your syntax might allow you "FROM NULL", which is really more correct.

Hope that helps out.  You can see an interesting example of how to use such a temporary table of dates in another posting of mine titled "Example of calculating holiday vs. business days in a date range".

Carl

 

Code:

WITH DatesInRange(SD, ED) AS(SELECT  $P{StartDate},  $P{EndDate}FROM  CORE.ENV  /* This is a table that only has a single row--I needed to put something here */UNION ALLSELECT  SD +1 day,  EDFROM  DatesInRangeWHERE  SD < ED),SELECT  DatesInRange.SD,  COUNT(MyData.PostingDate) AS DateCountFROM  DatesInRange LEFT OUTER JOIN  MyData ON    MyData.PostingDate=DatesInRange.SDGROUP BY  DatesInRange.SDORDER BY  DatesInRange.SD