How to create a timeline in a SSRS report.

Timeline reports can be very usefull to display what is happening during a specific time period. For instance, you have a server on which a lot of background tasks are running. In the beginning, you know which jobs are running at which moment. However, when the number of jobs are increasing, you will lose the overview. Especially when the execution times of the background jobs are taking longer after a while. A timeline report will help you to visualize all running background jobs. In SQL Server Reporting Services (SSRS) their is not a standard reporting item to make a timeline. However, there is a way to do this with the current report items. In this blogpost, I will explain how you can make a timeline report. I will use the example to display which backgroundjobs are running on which moment.

First of all we need to have a dataset which the execution times and durations of the backgroundjobs.
I have table named: Activitylog. In this table the starttime of the background job is registered including the execution time (duration).

SELECT StartTime, APP, Duration FROM ActivityLog

The report will have 4 filters to specify a date range. This date range is divided in a date and hour of thedate on which the backgroundjobs have been executed. (@MeasureDateStart, @MeasureDateEnd, @MeasureHourStart, @MeasureHourEnd)

This will results in a dataset which calculates the EndTime of the backgroundjob based on the StartTime and duration of the backgroundjob. It will use the parameters: @MeasureDateStart, @MeasureDateEnd, @MeasureHourStart, @MeasureHourEnd to select the data you need. All backgroundjobs will be displayed which have a start time and or end time within the selected time period.
SELECT AL.App, AL.StartTime,
DATEADD(ms, AL.Duration, AL.StartTime) AS EndTime, AL.Duration
FROM ActivityLog AS AL
WHERE (AL.StartTime >= @MeasureDateStart) AND
(DATEPART(hh, AL.StartTime) >= @MeasureHourStart) AND  
(DATEADD(dd, 0, DATEDIFF(dd, 0,DATEADD(ms, AL.Duration, AL.StartTime))) <= @MeasureDateEnd) AND
(DATEPART(hh, AL.StartTime) <= @MeasureHourEnd) 


The dataset is ready for use.

• Start Business Intelligence Development Studio(BIDS) and create a new report with a datasource to the database and define the dataset.
• All defined parameters in the dataset will be added automatically to the report.
• Add the report item Chart and select the Range Bar.
  
  
• Define the chart, Values: Startime ;Series Groups: App ;Category Groups Details.

• Configure the StartTime by opening the series properties.
  
  
• if desired you can configure the tooltip to show additional information.
• Configure the horizontal Axis to display the hours by opening the properties and select custom format: hh
  
   

• Your report is ready for use and should look like the first picture in this blogpost.

Auto refresh of SSRS reports.

In some situations you want to use a SSRS report to monitor activity on your solution which requires some action of people. For instance your internal helpdesk has made an overview of the number of logged errors in the last 10 minutes. This overview will help the helpdesk in their daily operation. In SSRS you can configure your report to refresh automatically. Of course you should be very carefully in implementing this feature. It can result in a lot of recurring datbase activity which can have a big impact on the overall performance of your solution.

To configure auto refresh of your report, open the property windows of your Report. In the Other section you will find a property AutoRefresh. Sets its value to 60 and the report will auto-refresh every 60 seconds.

Picture: Milford Sound, New Zealand Dec 2011.