I have a requirement in a report where I need to show date on Xaxis of a chart. However, my source table doesn't have all the date entries. I need to fill in the intermediate dates for which I use a calendar table and outer join it to my source table.

 

The user wants to drill down as year, month, day and hour. I am able to get till day as my calendar table outer join gets me day. How, do i populate the missing hours with in a day without adding hours to my calendar table as this will add 24 records per day and lead to very bad performance. is there anything I can do at report level like arrays or something. I need to only display the hours on the chart xaxis if the use runs the report for less than a day.

Please  help and thanks in advance.