Issue charting data over last 24 hours...

I have a table which contains data regarding calls to the Help Desk, I want to chart this using a simple line chart in SSRS 2005 with the chart displaying the number of cases opened by the help desk each hour for the last 24 hours. Although our Help Desk provides 24/7 support, there are periods of an hour in which no calls are received. The issue I'm having is I want the chart to still display these hour periods of time even though there are no records created in the time span. I want the x-axis to display every hour for the last 24 hours.

Anyone have any suggestion on how I can accomplish this? The only idea I've come up with is creating a new table containing a list of every hour in a day and referencing this to build the x-axis...but it seems as though it should be easier than that?

Thanks in advance for any input or assistance.

>>The only idea I've come up with is creating a new table containing a list of every hour in a day and referencing this to build the x-axis

I have to do the same thing a lot, except on the calendar level rather than the hour level.

Although maybe it *should* be easier, I don't know a better way to do it.

Because the calendar is a bit more dynamic, I don't store the data in a table but rather generate it on the fly. In your case, it's static (there are always 24 hours) so it makes some sense to just store the data in a table, in the form you need it --

However you *can* generate it on the fly pretty easily if you wish.

Pick some table that always has >= 24 rows for this task -- I'll use master.dbo.spt_values here --

Code Snippet

SELECT TOP 24 ROW_NUMBER() OVER (ORDER BY name) FROM dbo.spt_values

... put this in a table-valued udf, reference the udf in your query...

HTH,

>L<