I'm new to report builder and having trouble overcoming what I hope is a simple problem. We have a sql table holding error codes reported by a web application. These codes are logged along with a timestamp. My goal is to plot the number of errors in a line graph for the previous 30 minutes.
I've got it to work using the datetimestamp field for the x-axis, however if no errors are logged for a specific minute, it doesn't show 0. Also if there hasn't been any errors for 20 minutes, the graph will only show the 10 minutes that have values and not the 20 that should be 0.
You need to structure your data to give SSRS those data points. Set up your data set to have the time increments you want with their proper labels, then SSRS should be able to report it just fine.
Here's some code to explain what I'm talking about. Hopefully this example gives you some insight on how to structure your data.
--temp table to store all of the time groupings
create table #tmpTimeGroups
(
BeginTime datetime
,EndTime datetime
)
declare @reportStartTime datetime = dateadd(d,datediff(d,0,getdate()),0) --beginning of today
,@reportEndTime datetime
,@currentTimeGroupStart datetime
,@currentTimeGroupEnd datetime
,@minutesGrouping int = 30 --number of minutes to group each section
select @reportEndTime = dateadd(d,1,@reportStartTime) -- 1 day after reportStartTime
,@currentTimeGroupStart = @reportStartTime
--populate temp table with each grouping value
while(@currentTimeGroupStart < @reportEndTime)
begin
set @currentTimeGroupEnd = dateadd(MILLISECOND,-3,dateadd(MINUTE,@minutesGrouping,@currentTimeGroupStart)) --3 milliseconds before the next start period to avoid overlap
insert into #tmpTimeGroups(BeginTime,EndTime)
select @currentTimeGroupStart,@currentTimeGroupEnd
set @currentTimeGroupStart = dateadd(MINUTE,@minutesGrouping,@currentTimeGroupStart)
end
--final select statement with each time interval
select
tg.BeginTime [TimeGroup]
,count(tgroup.DateTimeField) [ItemCount]
from #tmpTimeGroups tg
left join tblTimeGroup tgroup --this is your dataset table
on tgroup.DateTimeField >= tg.BeginTime
and tgroup.DateTimeField <= tg.EndTime
group by
tg.BeginTime
The result set should look like this:
TimeGroup ItemCount
----------------------- -----------
2014-01-09 11:00:00.000 1
2014-01-09 11:30:00.000 0
2014-01-09 12:00:00.000 0
2014-01-09 12:30:00.000 1
2014-01-09 13:00:00.000 3
2014-01-09 13:30:00.000 4
2014-01-09 14:00:00.000 0
2014-01-09 14:30:00.000 0
2014-01-09 15:00:00.000 0
