SSRS 2012 - Dynamic sizing of charts

With SSRS 2008 Microsoft introduced a nice feature to he chart item - the ability to dynamically resize it at run-time. All you need is an expression that defines how the size gets calculated.

To see how it works and what the advantages are we will modify our Gantt chart from Gantt charts in SSRS 2012 - the chart approach.

To easily show the benefits we will modify the SQL statement of the Dataset first. We will use a recursive CTE in order to multiply the tasks according to the report parameter Multiplier. We will also generate new Task numbers with the ROW_NUMBER() function in order to have them unique.

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

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

INSERT @TasksStartEnd 
 SELECT 
    TaskNr = 1, 
    Ressource = '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 >> --------------------------------------------

-- Recursive CTE to multiply the tasks
;WITH
  Rec AS (
    SELECT Multiplier = 1
    UNION ALL
    SELECT Multiplier = r.Multiplier + 1
    FROM Rec r
    WHERE 
      -- Report parameter "multiplier"
      r.Multiplier < @Multiplier 
    )
SELECT 
  -- new unique TaskNrs with the help of ROW_NUMBER()
  TaskNr = ROW_NUMBER() OVER (ORDER BY t.TaskNr),
  t.Ressource, t.TaskStart, t.TaskEnd, t.WorkHours,
  -- 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
CROSS JOIN Rec r
ORDER BY TaskNr DESC

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

The IDE will add the parameter automatically. We only need to change the data type to integer.
So let's see what we get for different values of Multiplier.

Our chart with a Multiplier of 1

 With a Multiplier of 1 it's still the same look.

Our chart with a Multiplier of 5

Now with a Multiplier of 3 it starts to look cluttered.

In order to always have the same user experience no matter how many tasks we want to display we need to dynamically change the graph's height at run-time.

Because one task is one row in our dataset we will make the expression for DynamicHeight property of the chart item be based on the CountRows()-function built into SSRS.

= CStr(2 + 2 * CountRows("DataSet1")) + "cm"

It's important to note that the expression needs to evaluate to a valid value for the Height property. So we need a string which gives us a measure in inches or centimeters (If you belong to team metric system like me ;)).
Your expression should also account for a chart's dimension to consist of a fixed part (e.g. the axis, labels, borders) and a dynamic part (e.g. the real content). The expression above has numbers just taken from the top of my head - it could be more accurate if calculated through.
But the difference should be obvious by taking a look at the picture below.

Our dynamically resized chart with a Multiplier of 3

As we can see the general look stays the same no matter how many tasks we add. Of course the same can be done with the Width of a chart.

All in all I have to say that this is a feature I missed a lot when I started working with SSRS 2005.