Gantt charts in SSRS 2012 - the chart approach

This post is the second part of a series concerning Gantt charts in SSRS 2012. The initial post was Gantt charts in SSRS 2012 - three approaches to rule them all.

The chart approach is the most obvious solution.
TL;DR: It's a horizontal stacked bar chart trimmed to look like a Gantt chart. If you already know that approach - which can be found widely throughout the net - you can skip that post and wait for the next ones in this series.



For everyone else: That's what you'll get after we walked trough the How-To.

Gantt chart - chart approach

Scenario

As you can already see from the picture above we have a scenario where there are different work-places (or resources or employees) that work on certain tasks (or production orders or whatever). Those tasks have a cycle-time and an amount of workload.

Source Data

The source data for most environments might consist of these apsects:

• Task Number - a unique identifier for the task
• Resource - the resource the task is assigned to
• Task-Start - the planned start-date
• Task-End - the planned end-date
• Work-Hours - the planned capacity demand for the resource

Of course there could be a lot more to it. But these are the basics for our example. We also assume that the time-scale for our chart will be days. Of course the solution can be modified as you wish. The following SQL will give you a table variable with said source data.

-- >> set up source data as table variable >> ----------------------------------

DECLARE @TasksStartEnd TABLE (
 TaskNr int,
 Resource nvarchar(100),
 TaskStart date,
 TaskEnd date,
 WorkHours money
 )

INSERT @TasksStartEnd 
 SELECT 
    TaskNr = 1, 
    Resource = 'WP01',
    TaskStart = GETDATE(),
    TaskEnd = DATEADD(day, 2, GETDATE()),
    WorkHours = CONVERT(money, 5)
 UNION SELECT 2, 'WP03', 
    DATEADD(day, 2, GETDATE()), DATEADD(day, 5, GETDATE()), 10
 UNION SELECT 3, 'WP02', 
    DATEADD(day, 7, GETDATE()), DATEADD(day, 10, GETDATE()), 11
 UNION SELECT 4, 'WP10', 
    GETDATE(), DATEADD(day, 10, GETDATE()), 11

-- << set up source data as table variable << ----------------------------------

SELECT *
FROM @TasksStartEnd

Data transformation

For the bar chart we will need two series: The one that shows the tasks and an invisible series that aligns the correct start for each task. So we will need to generate two columns for these series

• OffsetDays - to align the tasks
• CycleTimeDays - for the correct length of each task

So the SQL-statement for our Dataset will look like this.

-- >> set up source data as table variable >> ----------------------------------

DECLARE @TasksStartEnd TABLE (
 TaskNr int,
 Resource nvarchar(100),
 TaskStart date,
 TaskEnd date,
 WorkHours money
 )

INSERT @TasksStartEnd 
 SELECT 
    TaskNr = 1, 
    Resource = 'WP01',
    TaskStart = GETDATE(),
    TaskEnd = DATEADD(day, 2, GETDATE()),
    WorkHours = CONVERT(money, 5)
 UNION SELECT 2, 'WP03', 
    DATEADD(day, 2, GETDATE()), DATEADD(day, 5, GETDATE()), 10
 UNION SELECT 3, 'WP02', 
    DATEADD(day, 7, GETDATE()), DATEADD(day, 10, GETDATE()), 11
 UNION SELECT 4, 'WP10', 
    GETDATE(), DATEADD(day, 10, GETDATE()), 11

-- << set up source data as table variable << ----------------------------------

-- >> add two calculated columns >> --------------------------------------------

SELECT t.*,
  -- difference between "zero" date of database (1900-01-01) and Report Server 
  -- (1899-12-30)
  OffsetDays = DATEDIFF(day, 0, TaskStart) + 2,
  CycleTimeDays = DATEDIFF(day, TaskStart, TaskEnd) 
FROM @TasksStartEnd t
ORDER BY TaskNr DESC

-- << add two calculated columns << --------------------------------------------

One thing I find a bit odd is that the date-value for zero in SQL Server is 1900-01-01 while in SSRS it is 1899-12-30. That is why we add another two days to the offset.

Report-Design

Add Dataset

The Data source does not matter because we will generate our own table variable in the dataset. So I just set up a data source pointing to my local master database.

For the Dataset select the embedded type and just copy the sql-statement above. In the query editor window (after clicking Edit as Text) it will look like this:

Query Designer window of the Dataset

Add chart

Choose the stacked bar chart.

Stacked Bar chart

Customize the chart

Now we are going to add the following items

1. add series - OffsetDays
2. add series - CycleTime

The Aggregation type Sum() is applied automatically. Now we need to customize the time axis (the horizontal axis). Right-click -> Horizontal Axis Properties...

Set the Interval type to days and open the expression editor for Minimum. In order to have the correct time-scope for the graph we set the following expression:

=DateAdd(DateInterval.Day, -1, Min(Fields!TaskStart.Value))

So we are forcing the graph to start the scale one day before the first task starts. 

Now select the OffsetDays series and set the Color property to No Color.

That is vital because we don't want to see the bar that helps us control the beginnings of our taks.

Let's take a look at what we accomplished.

Gantt chart - First result

Well, that already looks like a Gantt chart. Of course to get the same result as shown in the beginning of the post we need some polish:

• Right-click Chart title -> Delete
• Right-click Axis titles -> un-check Show
• Right-click Legend -> Delete legend
• Go to the Category Properties of the Details-Group and set the following expression for the label
  

  =CStr(Fields!TaskNr.Value) + " - " + Fields!Resource.Value

For the horizontal chart axis set the following properties

• Interlaced - true
• InterlacedColor - WhiteSmoke
• LabelFormat - dd.MM.yy (or anything you like)
• HideEndLabels - true
• Interval - 1
• Minimum - 

  =DateAdd(DateInterval.Day, 1, Max(Fields!TaskEnd.Value))

Right-click the CycleTimeDays series -> check Show Data Labels

• Set the Data Labels property UseValueAsLabel to False
• Set the following expression for the Label property of the Data Label
  

  = FormatNumber(Fields!CycleTimeDays.Value, 0) + " days " 
  + vbCrLf 
  + "(" + FormatNumber(Fields!WorkHours.Value, 1) + " hours)"

For the CycleTimeDays series set the following properties

• BorderColor - White
• BorderStyle - Solid

Now you should be able to do the chart approach to Gantt charts in SSRS 2012. Next time we will take a look at the table approach.