SSRS- charts colour coding

Question

Hi,

I have SSRS solution for SQL 2005 and 2008.

I am showing output in the form of chart- column chart with each column representing different database.

Is there a way to display each column in different color?

Regards

Manjot

Answer 1

You can use a formula to set the colour of each column, but that would work best if you knew what the individual series values ('databases'?) were going to be.

Right-click on your chart and bring up its properties. Now switch to the Data tab and select the first item in the Values list. Click the Edit... button to show the properties for the values (the columns) in your chart. Over on the Appearance tab there's a Series Style... button which takes you to another dialog.

On this new Style Properties dialog, switch to the Fill tab. That's where you set the colour for each of your columns. This can be a formula, so you might make it something like:

=Switch(
    Fields!Database.Value = "master", "Blue",
    Fields!Database.Value = "msdb", "Red",
    "Green")

If you don't know in advance which 'databases' are going to be represented on the chart, this method won't work very well. In that case you might be able to come up with a formula which hashes the database name and comes up with a colour to match. That sounds like an interesting challenge, so add to your question if you need help doing something like that.

Edit

I just got a hash-based-colour-scheme working. It's a pretty nasty piece of code, but it did manage to get me a unique colour for every (string valued) column. Perhaps someone can come up with a better algorithm and post it here. Here's mine:

="#" & left(Hex(Fields!Database.GetHashCode()), 6)

So that's getting the HashCode for the string (a numeric value) and converting it to hex, then taking the leftmost six characters and prepending it with a "#" sign. That gives us a string that looks like a colour value (eg #AB12F0).