Zero data point for empty TimeSeries intervals

[mathewplunkett]

I've been using Jasper for a while and this is the first time I've run into something that I can't find by searching the forum. 

I've created a timeseries line chart that uses a dataset grouped by date.  The chart shows the number of rows returned for each day using the date field as the time period and the Group_COUNT variable for the value.  What I found is that if there are no rows for a date then no data point is created.  The line continues past the empty interval at the same number it was at in the previous interval. I have been trying to get it to print a zero data point in that case but have been unsuccessful so far. 

Any suggestions would be greatly appreciated.

Thanks

Matt

[teodord]

Hi,

 

I don't think there is an easy way to solve this. You would have to so some preprocessing or post processing of the data to fill the gaps. Maybe this could be pushed down to SQL, where you make an outer join with a dummy table containing dates, just to make sure all dates will be present in the result.

 

I hope this helps.

Teodor

 

[mathewplunkett]

That is what I am currently doing.  My sql procedure creates a temporary table with blank entries for each date in my range, then inserts my actual data results, and returns everything .  I then subtract one from my group count to correct the data.   For that to work I had to switch from using the jdbc sqlserver connection to a jdbc:odbc bridge connection due to some issue with multiple result queries to sql 2000.  I was hoping there was a way to do it inside the report.

Code:

@StartDate datetime, @EndDate datetimeASDECLARE @WorkingDate datetime;SET @WorkingDate = @StartDate;SELECT 'Handled' AS CallResult, @WorkingDate AS CallStartDateTime into #tempdbSET @WorkingDate = DATEADD(Day,1,@WorkingDate)WHILE (@WorkingDate <= @EndDate)  BEGIN	INSERT #tempdb(CallResult,CallStartDateTime) SELECT 'Handled' AS CallResult, @WorkingDate AS CallStartDateTime	SET @WorkingDate = DATEADD(Day,1,@WorkingDate)  ENDINSERT #tempdb SELECT CallResult, CallStartDateTime FROM HandledCallDetailReport WHERE CallStartDateTime >= dateadd(day,datediff(day,0,@StartDate),0)and CallStartDateTime <= dateadd(day,datediff(day,-1,@EndDate),0)ORDER BY CallStartDateTimeselect * from #tempdbDrop Table #tempdb