Use Paginated Reports to Create a Gantt Chart

A paginated report has variable height that can be scrolled vertically on the screen or split across multiple pages when printed or saved to different output formats. Power BI has become the go-to business tool for modern reporting, but sometimes we need information displayed in the more traditional paginated format. A colleague asked if a Gantt chart could be created, and I thought this was an excellent example of where Paginated Reports & SSRS were an ideal choice for the purpose.

A Gantt chart is a running list of activities with the duration for each displayed as a horizontal bar depicting the beginning and ending day along a horizontal scale. The challenge is that this is not a standard chart type in either Power BI or SSRS/Paginated Reports. Furthermore, project planners may prefer to see activities as rows in the format of a printed page, as you can see in this example:

Let me just get this part out of the way before we proceed. I’ve been using SQL Server Reporting Services (now called “Paginated Reports” in the Power BI service) for most of my 20+ year career in BI and data analytics. It is a very flexible tool that can, at times, be tedious and time-consuming for a report developer to use beyond its basic features. I’ve used it, and seen it used in very creative ways, but some things take patience to work through the trial-and-error design process. Once you know what you are doing, it can be a wonderfully powerful and flexible design tool.

Source Data

The source for this report is a list of project tasks, each having a start date and an end date. For demonstration purposes, I used the Enter Data feature to populate a portable dataset, but you can use a database or any other supported data source. Since the data in my sample is stored as text, I do need to perform some extra data type conversion in some of the expressions. In some cases, this won’t be necessary when using strongly-type data sources like SQL Server.

A Gauge in a Table

Most advanced Paginated reports are built by using a data region, like a table, matrix or rectangle; to repeat other report items in data groups. To achieve the Gantt chart effect, I use a table data region to repeat a linear gauge for each row in the dataset. The table is sorted by the Order column. You could also use the DateFrom column for sorting (again, my data is stored as text which doesn’t naturally sort in date order).

I’ve added two linear gauges to the table: one in the details row of the table with the numeric scale hidden, and one in the header with the range (or bar) removed, and the scale displayed. This image shows the report in design view after the following steps have been completed. You can look though the sample RDL file I’ve provided, which includes a completed report based on the steps outlined below.

Make sure you have the Properties pane displayed in report Builder by checking the Properties box in the View ribbon.

To set the “window” of dates shown in the chart, I’ve created two date-type parameters named ChartRangeStart and ChartRangeEnd, and assigned default values that include the records in the source data, 3 months apart.

The gauge visual doesn’t naturally work with dates, so we need a couple of calculated fields: StartNumber and EndNumber. These are created in the properties for the dataset using the following two expressions, respectively to define these two calculated fields (no line returns, the text wraps):

=DATEDIFF("d", Parameters!ChartRangeStart.Value,
IIF( Fields!DateFrom.Value="", Parameters!ChartRangeStart.Value, Fields!DateFrom.Value )
)

=DATEDIFF("d", Parameters!ChartRangeStart.Value,
IIF( Fields!DateTo.Value="", Parameters!ChartRangeStart.Value, Fields!DateTo.Value )
)

For reference, this is the Dataset Properties dialog after adding these two new fields:

Choose Gauge from the Insert ribbon. Select a Bullet Graph from the Select Gauge Type dialog and then drop it onto a blank space on the report canvas.

When working with an intricate visual report item like a gauge, I make it a point to place it outside the data region, make it as big as possible to setup all the properties. Then, I cut and paste the gauge into the table cell.

Mysteries of the Gauge Visual

This is where things get tricky. The Gauge, Databar, Sparkline and Indicator visualizations were all developed by Dundas Software. They had their own charting product and Microsoft purchased the code base to integrate with Reporting Services back in 2010. There is a trade-off when using these components; they erred on the side of giving us lots and lots of wonderful options to build complex charting visuals, rather than making them uber simple to use. You can spend lifetimes digging through these object collections and properties. Believe me – I’ve spent much of mine doing just that! Anyway, speaking from experience, check your OCD and don’t stay up too late at night trying to figure out every nuance because this can become a Pandora’s Box of knobs and levers that control all the intricate features of these visualizations.

To unravel the Gauge objects, start clicking-around to select objects in the gauge visual designer. You will see that objects are organized into a hierarchy of collections within the Gauge Panel. The items in bold are those used in this report:

• LinearGauges
  • Scales
    • Ranges
    • Pointers
    • CustomLabels
• RadialGauges
• Indicators
• Labels

Objects in the linear gauge and radial gauge have corresponding names, like LinearGaugePanel1 and LinearRange1. You can modify or remove these objects by selecting them in the designer, or you can add or remove then in the properties pane using the ellipsis button for the collection of child objects.

For example, the Gauge Panel has a collection of Scales, and a Scale has a collection of Ranges and Pointers. Until you get the feel of things, you might want to make a second copy of the gauge. You can always use Ctrl-Z to undo a delete or property change if you get into trouble.

Setup the Gauge

Delete all the Pointers and all but the first Range (it’s named LinearRange1). You can click to select each object in the designer. Confirm the selection by checking the object name in the top of the properties pane before you delete or modify anything.

The following image shows the properties of the LinearScale1. Modified and non-default properties are shown in bold:

This is the entire MaximumValue property expression:

=DATEDIFF("d", Parameters!ChartRangeStart.Value, Parameters!ChartRangeEnd.Value)

To choose the LinearRange1 object, either click to select the object in the designer or use the ellipsis (…) button in the properties panel to edit the Ranges collection, then update the properties shown here in bold:

For properties that represent a collection of objects, you’ll see an ellipsis button (a button with three dots) on the right side of the property value cell when that property is selected. Click the button to open a dialog window to add, remove and edit the objects in the collection.

This is the entire ToolTip expression (text wraps so don’t use line returns):

=Fields!Task.Value & " (" & CSTR(DATEDIFF("d", Fields!DateFrom.Value, Fields!DateTo.Value)) & " days) " & vbCrLf & FORMAT(Fields!DateFrom.Value, "Short Date") & " - " & FORMAT(Fields!DateTo.Value, "Short Date")

Using the Insert ribbon, drop-down the Table button and choose Insert Table. Drop the table in the body of the report.

Expand the dataset and then drag and drop the Task field to the “Data” row in the first column of the table.

Select the gauge and then use Ctrl-X to cut it to the clipboard.

Select the second cell in the “Data” row of the table, and then use Ctrl-V to paste the gauge. Resize the second column and row height to adjust the gauge size and position.

Select and copy the gauge to the clipboard, using Ctrl-C.

Select the second cell in the “Header” row. Use Ctrl-V to paste a copy of the gauge into this cell.

In the new copy of the gauge, you will delete the Range and show the Scale. To do this, select the gray range bar and make sure that LinearRange1 is displayed at the top of the properties panel. Once you confirm that the Range object is selected, press the Delete key to remove it.

Select the LinearScale1 object in the new copy of the gauge.

Change the Hidden property to False.

Expand the LabelStyle group and change the UseFontPercent property to False. You can optionally expand the Font group and change the Font property of needed. This is one of the many properties you can tweak later if you want to adjust things to your liking.

To change the sort order of the table, choose the table and use the SortOrder expression. The default table name is Tablix1.

To select the entire table data region, click any cell in the table to show the gray row and column handles, and then click the gray box in the top-left corner, between the row and column handles.

The rest of the report design is typical. I’ve added textboxes to show the title and parameters selections. I’ve also added the option to hide and show additional columns using a parameter. These should be apparent in the sample RDL file I’ve provided. With variations on the design, you can enhance this report to add more details, and perhaps deal with weekends and non-workdays, etc. If you make such enhancements, please share them for benefit of the community.

Download Gantt Chart sample project here