Monday, 23 November 2009

Multicoloured column charts in SSRS 2008

Perhaps I’m being as dumb as a bag of spanners but I’ve really struggled with getting multi-coloured column charts in Reporting Services 2008 without putting the category data on the series axis, which then creates all sorts of problems with sizing and grid lines. It seems that Dundas type charts don’t do this easily, although the Nevron ones do (if you know where to look). However I’m not forking out for third-party charting controls just to get nice colours.

This problem first came to light when doing the QlikView vs SSRS development (QlikView charts are pretty by default). Since then I’ve looked in vain for a multicolored (sic) property similar to charts in Excel.

To illustrate my point, I’ve included a screenshot from Excel where I’ve put together a simple column chart with a category and value axis:

By default this renders all columns in a single colour. However if we right-click any of the columns and ‘Format Series Data’ then we can vary the colours by data point:

Anyway, after much gnashing of teeth and wailing I chanced upon this article which got me thinking about a simple VB.NET function that could be used in the fill property of the columns. Thereafter the solution was quite simple. Basically you need to add the following function to your report code (remembering to add a reference to the System.Drawing assembly):

Public Shared Function GenerateRandomColor(ByVal seed As Integer) As String 

Dim randNum As New Random(seed)
Return System.Drawing.ColorTranslator.ToHtml( _ System.Drawing.Color.FromArgb( _ randNum.Next(0, 255), _ randNum.Next(0, 255), _ randNum.Next(0, 255)))

End Function

Then right click the columns in design view to bring up the Series Properties dialog and go to the Fill section:

Enter the following expression for the Color formula (obviously remembering to replace the fieldname with the field being used on the category axis):

=Code.GenerateRandomColor(RunningValue(Fields!MyFieldName.Value,Count,Nothing))
 

That’s about it really. A bit convoluted just for random colours but it works. Despite the fact that I will kick myself if this is already a supported feature (probably a checkbox marked ‘multicolored’) I would be really grateful if anyone could enlighten me.

No comments:

Post a Comment