Add missing dates to chart category / table
You need to create a chart on a specific type of date/time interval, and the data, in some cases, may not have any records for specific intervals. A good use case, is drawing an hourly chart, where 24 bars are expected, but for a few days, there is no data for some of the hours, and the charts look odd.<br />
<br />
A good solution, is to create a table in the database, with a record for each slice in the interval, then use some date and math logic in the SQL statement with a left join against the table. Once you get a handle on how to do each type of interval you require, and convince the DBA to allow you to add the tables, you still spent quite a bit of effort... not anymore.<br />
<br />
BIRT provides a way to do a left join between data sets, and it allows you to do scripted data sets, where you can specify each record. Mix in some BIRT scripting, leveraging the java.util.Calendar class and we have ourselves, a solution. The attached sample is capable to create dates for any specified range in any of the following intervals: Minute, Hour, Day, Week, Month, and Year.<br />
<br />
The "Date Ranger Data" data set which makes all this possible, has 2 columns defined on it, one of type Date/Time the other of type Date, both containing the same data with different precision, which can be joined against tables with either type defined. There are 3 parameters which drive the code, contained within the open and fetch methods of the data set. The open method sets up the start and end dates, as well as the interval to use. The fetch method will make use of the java.util.Calendar class methods to increment the date based on the interval, and compare it to the end date, spitting out a single record for each interval specified.<br />
<br />
The 3 parameters are Start Date, End Date and Date Interval. The first 2 are obvious. Date Interval can be any of the following:<br />
<br />
m = minute<br />
H = hour<br />
h = hour<br />
D = day<br />
d = day<br />
W = week<br />
w = week<br />
M = month<br />
Y = year<br />
y = year<br />
<br />
Next is to add the standard data set for the BIRT report. Finally create a BIRT Joint Data Set, using a left join between the "Date Ranger Data" data set and the report's standard data set.<br />
Now you can use the BIRT Joint data set for charts and tables, with confidence that you will get at least 1 record for each interval specified within the date range.
