How to Utilize Color Palette Function in PBRS for Stunning Visualization

by | Mar 11, 2020 | ConfigMgr, How-To, SQL Server Reporting Services, Tips | 3 comments

Last updated on December 31st, 2023 at 11:27 am

I’m updating my blog post to create a color palette for Ask Garth logo. So, I thought that I would show you the Power BI Report Server (PBRS/PBIRS) or SQL Server Reporting Services (SSRS) function that I use. This function will allow me to apply a defined color palette to reports and allow me to synchronize colors between Charts and tables/matrix.

If you have ever written any reports for SSRS or Power BI (either desktop or report server). You know that when you create a chart and table a random color is automatically assigned. This is fine if you don’t want any control over what colors are used. Or don’t care what the end result looks like. So how can you sync the colors between the two objects?

In all of Ask Garth reports, however, I never (rarely) use the automatically assigned colors. Instead, I set the colors myself at least use branded colors. In this blog post, I show you one of the color palette codes we use at Ask Garth. Then I explain what each part of the code means.

PBRS vs SSRS

This is as hard one to explain but PBRS reports created with VS will actually be SSRS reports. And they can be used on either SSRS or PBRS. In PBRS they will show up within the paginated Section within the folder. So even though I use PBRS thru this blog, everything applies to SSRS too.

WHY DEFINE A COLOR PALETTE CODE FOR SSRS?

There are many reasons you would want to define the colors used in a SSRS report. These include: making the information easier to read (e.g. matching the colors between a chart and a table), following style guides or adhering to Web Content Accessibility Guidelines (WCAG). We use our own custom color palettes to help customers understand data more quickly and easily.

I have written a number of blog posts on why color is important. But here is a good one to start with, How Color Is Used in Power BI and SSRS Dashboards.

“THE CODE”

Here’s an example of a color palette code and function that we use at Ask Garth:

Private colorPalette As String() = {"#F25430","#404959","#D5C72B","#9C4D1F",
"#494C8C","#D0320d","#3C5956","#F23078","#3067F2","#F27630","#549D83","#F2309F",
"#D068B3","#F29830","#129AB8","#BF624C","#18C9F2","#D52B69","#A6F230","#FF9981",
"#D5692B","#D52B8B","#D5862B","#2B5BD5","#7db781","#B0D9D5","#F25430","#73D8B3",
"#91D52B","#9D6154","#2BD597","#DC7058","#B84025","#9C1F66","#9C621F","#814F44",
"#7988AD","#F2933D","#505A73","#E02BCB","#A41f00","#4e948d","#1309D9","#15E66F",
"#3E1F9B","#FF7858","#2DE0BC","#D58335","#ED77CC","#129AB8","#09632A","#A0264D"}

Private count As Integer = 0
Private mapping As New System.Collections.Hashtable()

Public Function GetColor(ByVal groupingValue As String) As String
	If mapping.ContainsKey(groupingValue) Then
		Return mapping(groupingValue)
	End If
	Dim c As String = colorPalette(count Mod colorPalette.Length)
	count = count + 1
	mapping.Add(groupingValue, c)
	Return c
End Function

“THE CODE” EXPLAINED

In order to create your own color palette code, you need to understand what is needed. I know when you first look at the code above. It is a bit of an eyechart, so let me break it down for you.

colorPalette is defined as an array of strings. Each string is a color, so each one forms part of my color palette.

Do no mix and match color name and color codes. In my opinion, this is a bad move because colour (UK spelling intended) names change based on what country you live in. You can read more about that in, When is Grey Not a Valid Color?. Which is also why I highly recommend that you use Hex Color codes exclusively. This is to ensure that you always get the correct color.

Next, there is a variable called, “count As Integer.” What the name is self-explanatory.

After that, “mapping” is defined as a “shortcut” to the System.Collections.Hashtable API function.

Now for the “meat and potatoes” of the code. The Ask Garth function, GetColor, at a high-level, accepts a string and then checks to see if there is already a defined color for that string. When one already exists, the color code is returned. Else the next color code on the list is returned.

In the code above, the first color returned is #F25430 and then #404959, etc. When the end of the color palette is reached (52 colors to be exact in this example). The function starts over at the beginning with #F25430.

The function is using simple math to define the color code.

HOW TO CREATE YOUR OWN COLOR PALETTE CODE

Now that I explained the basics of a color palette code, you can get to work!

Need help creating a color palette? Please read, How to Create a Color Palette for Your ConfigMgr Reports Once you decide what colors you want to use. You can simply edit each string in the code above by swapping out the HEX color code with your own choice.

Obviously, you can adapt the function for any number of predetermined instances. I plan to publish a blog post in the not-too-distant future that shows you how to modify the function.

Lastly, you need to add your color palette code to a report.

ADDING THE GetColor TO A REPORT

Report Properties Menu

From the menu, Extension then select Report and then click on Report Properties…

GetColor function listed Code node for the RDL. Select the Code node and then paste the code with your color palette into the Custom code area. Click on the OK button.

With that last step completed, you are now ready to use your color palette! In next week’s “Unleashing the Power of GetColor Advanced Techniques for PBRS Chart Customization”, you’ll see how to assign this color palette code to a chart. The following week, Tips for Perfectly Matching Colors in Your RDL Chart and Table.

How to Utilize Color Palette Function in PBRS for Stunning Visualization Video

Follow along with me as I show you the steps that I took to add the GetColor function to RDL.

If you have any questions about How to Utilize Color Palette Function in PBRS for Stunning Visualization Video? Please also subscribe to my YouTube channel and newsletter to stay on top of the latest trips and tricks. Additionally, if you have any questions, please feel free to touch base @Garthmj.