How to create a Gantt-like Chart in SQL Server Reporting Services

Intro and Background

I was recently asked for some help with a Dynamics CRM Online engagement.  The customer wanted a Gantt-like chart that showed the span of active opportunities.

Dynamics CRM Online really isn’t important to my post, except in one major detail.

Dynamics CRM Online doesn’t allow access to the underlying data.  There is no ability to query the data store using SQL.

Instead, Dynamics CRM allows for queries to be written using FetchXML.  SQL Server Business Intelligence Development Studio (BIDS) is able to consume the results of a query and present it as a DataSet.

Why is this important.  You can’t do any trickery using SQL.  You pretty much take what FetchXML gives you and work with it from there.

Instead of dealing with FetchXML, in order to have a repeatable demo/tutorial, we’re going to create a test dataset in SQL.

Why is this hard?

The only real hard part of this task is how to create a scale and how to create a progress bar that spans a start date and end date.  SQL Server Reporting Services does not allow a report programmer to modify a “width” property at runtime.  This makes things interesting.

But SQL Server Reporting Services, Business Intelligence Development Studio (in SQL Server 2008 R2 and previous) and SQL Server Data Tools (in SQL Server 2012) all provide a Range Bar Chart.  That’s what we’re going to use.

Setup

Let’s get things set up.  We’re going to need a Data Source, a Dataset, a Report, a Table and a Range Bar Chart.  Let’s get started.

We’re going to keep things intentionally basic.  We’re not going to use a Shared Data Source, nor a Shared Dataset.

That means everything is going to live within a report.  So create one.

01 Create Report

Because this is a demo using SQL Server as a data source, we need a connection to an instance of SQL Server.  Within the report, create a new Data Source.

02 Create Data Source

Now we need data.  Before we create a Dataset, we’re going to write a query that created the data we’re going to work with.

This query is going to be:

SELECT CAST ( 'Industrial'        as [varchar](128) ) as [market segment],
CAST ( 'Pipefittings'      as [varchar](128) ) as [opportunity name],
CAST ( 'Joe User'          as [varchar](128) ) as [created by],
CAST ( '2013-10-01'        as [date]         ) as [start date],
CAST ( '2014-02-28'        as [date]         ) as [complete date],
CAST ( 'Universe'          as [varchar](128) ) as [sales stage],
CAST ( '1000000'           as [money]        ) as [estimated total] UNION ALL
SELECT CAST ( 'Industrial'        as [varchar](128) ) as [market segment],
CAST ( 'Flanges'           as [varchar](128) ) as [opportunity name],
CAST ( 'Betty Lou'         as [varchar](128) ) as [created by],
CAST ( '2014-01-01'        as [date]         ) as [start date],
CAST ( '2014-06-30'        as [date]         ) as [complete date],
CAST ( 'Early Pipeline'    as [varchar](128) ) as [sales stage],
CAST ( '1200000'           as [money]        ) as [estimated total] UNION ALL
SELECT CAST ( 'Commercial'        as [varchar](128) ) as [market segment],
CAST ( 'Display Cases'     as [varchar](128) ) as [opportunity name],
CAST ( 'John Doe'          as [varchar](128) ) as [created by],
CAST ( '2014-02-01'        as [date]         ) as [start date],
CAST ( '2014-10-31'        as [date]         ) as [complete date],
CAST ( '30%'               as [varchar](128) ) as [sales stage],
CAST ( '800000'            as [money]        ) as [estimated total] UNION ALL
SELECT CAST ( 'Commercial'        as [varchar](128) ) as [market segment],
CAST ( 'Overhead Lighting' as [varchar](128) ) as [opportunity name],
CAST ( 'Richard Smith'     as [varchar](128) ) as [created by],
CAST ( '2014-06-15'        as [date]         ) as [start date],
CAST ( '2014-10-15'        as [date]         ) as [complete date],
CAST ( '50%'               as [varchar](128) ) as [sales stage],
CAST ( '50000'             as [money]        ) as [estimated total]

This gives us a result set like this:

market segment opportunity name created by start date complete date sales stage estimated total
Industrial Pipefittings Joe User 2013-10-01 2014-02-28 Universe 1000000
Industrial Flanges Betty Lou 2014-01-01 2014-06-30 Early Pipeline 1200000
Commercial Display Cases John Doe 2014-02-01 2014-10-30 30% 800000
Commercial Overhead Lighting Richard Smith 2014-06-15 2014-10-15 50% 50000

We’ll create a Dataset from this query:

03 Create Dataset

The last step of our prep will be to create a table and begin the basic layout.

We’re going to create a Table within the report that has a column for each of the columns in the dataset, plus a column for a graph.

The layout will look like this:

Column Description Width
1 Market Segment 4 inches
2 Opportunity 2.5 inches
3 Created By 2.5 inches
4 Start Date 1 inch
5 Complete Date 1 inch
6 Sales Stage 2 inches
7 Estimated Total 1 inch
8 Chart Area 7 inches

We just have to bind the Table to the dataset “OpportunityData”

04 Bind Dataset to Table

Then drag the columns of the dataset to the columns of the table.  For now, leave the right-most column empty.

05 Dataset Columns

After a little minor format tweaking (out of scope of this article), we have a solid starting point.

Building the Gantt-like Chart

Why do I keep saying Gantt-like instead of just Gantt.  Well, the definition of Gantt Chart is that it illustrates a project schedule.  But this isn’t going to be used to track a project schedule.  It doesn’t show dependencies or precedence networks.

It just shows start, end and the period of time between them.  Which is exactly what we need.

The component I chose to build this tutorial is the Range Bar Chart.  This is available within BIDS and SSDT (SQL Server Data Tools, ships within SQL Server 2012) under Toolbox / Chart / Range

07 Bar Chart Location 1 08 Bar Chart Location 2

Just drop the chart onto a blank area of the Report.  We’ll fit it into Table later.

09 Bar Chart - Preformat

We’re going to cut this to the bare bones.  We’re going to get rid of:

  • The Chart Title
  • The Legend
  • The Vertical Axis Title
  • The Vertical Axis
  • The Horizontal Axis Title

When we’re done, the Chart should look like this:

10 Bar Chart - Postformat

Let’s start setting up the chart.

The first thing we need to do is set the Minimums, Maximums, Intervals and Interval Types.

The customer was asking for an 18 month span of time to be represented, starting with the current day and ending 18 months out.  Instead, we settled on the first day of the current month through the last day of the 18th month.  This will make the report consistent from day-to-day (at least within the context of the same month).

On the Horizontal Axis, we are going to set the following properties:

Minimum =DateSerial(Year(Now), Month(Now), 1)
Maximum =DateSerial(Year(Now), Month(Now) + 18, 0)
Interval 1
Interval Type Months

We’re also going to set the format the of items along the Horizontal Axis to MM-yyyy (two digit month followed by 4 digit years).

Naturally, this is found under “Number”.  Go figure.

11 Bar Chart - Format Axis

Just a hint, the labels will be more readable if you rotate them 90 degrees.  This will present them vertically.

12 Bar Chart - Horizontal Axis Vertical Labels

Last, how to we set the start and length of the bar?  We set the Series Top and Bottom Values

13 Bar Chart - Set Series Properties

Let’s get a preview of the report and see how we’re doing.

14 Bar Chart - First Preview

This is accurate, but it doesn’t provide the Opportunity data.  Let’s cut the chart and paste it into the details row of the Table, in the right-most column.  Now we’re going to run a preview.

OOPS!

We receive the error “The chart has a detail member with inner members.  Detail members can only contain static members.

What this means is that this chart cannot live in a detail row.

We’re going to create a Group that contains only a single opportunity.

After selecting the Table, at the bottom left of the screen, we find “Row Groups”.  Right-click on “(Details)” and select “Add Group – Parent Group”

For “Group By”, select [opportunity name] and select Add Group Footer

15 Table - Add Group

Now all we have to do is move the data from the Details section to the footer.  Just set the Details section to Hidden = True.  A quick click on Preview shows us:

 

image

Pretty slick, but do we really need a timeline on every row?

Now, we are going to need a timeline.  So let’s take a copy of the Chart and put it right next to the header “Estimated Total”

We need to modify the series properties, setting Top = DateSerial(Year(Now), Month(Now) + 18, 0) and setting Bottom = DateSerial(Year(Now), Month(Now), 1)

Now we can hide the Axis Labels on the Chart in the footer.  We can also change the Axis Line Style to None.

Click on Preview and let’s see what we have.

16 Report Preview 2

Nice.  Except that the timelines in the header and the footers don’t line up.

The cell where the Footer chart lives is 7in wide by .5 inches high.  We’re going to have to offset the chart to the right until the timelines line up.

But you can’t move a Chart within a table.  The Chart automatically fills the available space.

So we’re going to have to use a Rectangle and place the Chart within that Rectangle.

Create a 7 inch by .5 inch rectangle.

Place the chart within the rectangle.

Set the size of the chart to 6.875 inches by .5 inches.

Set the Left property to .125.

Now place the Rectangle with the Chart inside back into the Table.

Now the timelines will line up.

17 Report Preview Final
Advertisements

  1. #1 by Muhammad Zahid on June 6, 2014 - 11:17 am

    Hi,

    Do you have any idea on how to achieve solve the below issue:

    https://community.dynamics.com/ax/f/33/t/128774.aspx

  2. #2 by Muhammad Mabrouk on June 25, 2014 - 4:39 am

    Very Vice Example. It needs some enhancements; but very good as a start for Gantt chart.

  3. #3 by Ricardo Mello on February 16, 2017 - 12:15 pm

    Thanks!

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

%d bloggers like this: