How Eclipse BIRT report display 24 hour a Day Interval

Question

Hey I have tried to show 24 hours a Day intervals hourly like:

 00,01,02,03,04,05 ....23.

Used Birt Jdbc Connection with Data Set :

Query :

select count(*) task_num , to_char(end_time,'hh24') dt from t_student where end_time>='2015-09-28 00:00' and end_time<'2015-09-28 23:00 group by to_char(end_time,'hh24')

result like that 09,11,14 but If data not available I need to add like symbol -- or null displayed.

Expected result like to Show Eclipse Birt Table : here task_num column values are 3,6.

  dt_00  dt_01   dt_02 dt_03 dt_04 dt_05 dt_06 dt_06....dt_23   total
    3      --      6     --     --     --  --     --       --     9  

Note :I referred below link but still i could not solve it

.http://help.eclipse.org/juno/index.jsp?topic=%2Forg.eclipse.birt.doc%2Fbirt%2Fsg-DisplayCreditLimitRangesInTheTableOfContents.html&cp=10_0_7_0_9

Answer 1

You need to generate empty rows in the query, otherwise BIRT won't be able to do it on its own. The easiest way would be to create a 24 rows table with all hours, and use a left join for your dataset such as:

select count(*) task_num , hour.dt 
from hour
left join t_student 
on hour.dt=to_char(end_time,'hh24')
where end_time>='2015-09-28 00:00' and end_time<'2015-09-28 23:00 
group by hour.dt

Another option with Oracle, it seems users make use of "connect by" to generate on-the-fly time ranges such in this example.

Then in cross-tables BIRT has a "show empty" property where we can set a String value such "--" that should replace empty values