Dynamic Chart Titles with SQL Server 2005 Reporting Services

In browsing some forum postings recently I ended up here: http://social.msdn.microsoft.com/Forums/en-US/sqlreportingservices/thread/bcb6b640-a4bd-4040-8954-679b89f61caa.

I noticed the comment that there was no support for using parameters in chart titles in SSRS 2005, only in SSRS 2008. I’ve always been lucky enough to have access to the full Dundas Charts product when working with SSRS 2005 on past projects, but am now working on a project where we only have access to the standard SSRS2005 chart control so was curious if this (suggested) limitation would prove a problem or not.

So I created a quick test report and added a chart control. Bringing up the properties window showed that the chart title had an option to deal with basic formatting, but indeed no expression builder.

Undeterred, I added a report parameter and I tried simply typing in a chart title which contained parameters:

=Parameters!ParameterName.Value + " the rest of my chart title"

A switch to preview mode showed it worked. The chart title changed dynamically at run time with the parameter value.

Given that win I decided to push my luck and see if I could add some more complex logic. So I added a second report parameter and typed the following expression in the chart title textbox:

=IIf(Len(Parameters!ParameterName.Value) >1, Parameters!ParameterName.Value + " the rest of my chart title", Parameters!SecondParamterName.Value + " the rest of my chart title")

Back in to preview mode and again success!

So it would seem that there is support for the use of parameters in SSRS2005 chart titles after all.

For more complex expressions I tried creating a textbox on the main report palette as a scratchpad so I could use the expression builder and save the need to type the entire expression. From here it was a simple matter of a copy and paste across to the chart title textbox. The only shortcoming I found was that the title textbox is only single line so I needed to ensure that my expression had no line breaks in it. Failure to do this would result in only the first line of the expression being pasted successfully.

Those people lucky enough to be working with SSRS 2008 have an expression builder inside the chart title properties, but for the rest of us this approach provides a reasonably painless alternative.