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.